SQLに関して質問です。とりあえずSQLiteを用いています。

積集合の結果を取得する方法がよくわからないのです。

例えば、
TABLE_A
id
1
2
3
4

TABLE_B
id|table_a_id|code
1 |1 |10
2 |1 |11
3 |2 |10
4 |2 |11
5 |2 |12
6 |3 |10
7 |3 |11
8 |3 |12
9 |3 |13
10|4 |13

のようになっているときに、
id1|id2|count
1 |2 |2
1 |3 |3
1 |4 |0
2 |3 |3
2 |4 |0
3 |4 |1
のように、
TABLE_AとTABLE_Bが1対nの関係になっているときに、TABLE_Aの各要素が持つ
TABLE_Bのcodeの重複度をすべての組み合わせで取得したいのです。
TABLE_Aの件数が1000件ほどあり、適当に組んだら終わりませんでした。。。
SQLは勉強し始めたところでどうすればいいのかわかりません。
どなたかご教授ください。

回答の条件
  • 1人2回まで
  • 登録:2007/10/27 19:28:16
  • 終了:2007/11/03 19:30:03

回答(4件)

id:t_shiono No.1

t_shiono回答回数256ベストアンサー獲得回数222007/10/27 20:55:28

ポイント23pt

難しいと言っておきながらなんですが、望みの結果と少し違いますが、

select B1.a_id as id1, B2.a_id as id2, count(*) as count

from B as B1 join B as B2

where id1 < id2 and B1.code = B2.code

group by id1, id2;

で似た出力は得られます。なお、テーブルAは利用していません。

同一の項目がもの例でいうところの 2 - 4が0という部分は抜けてます。

何かの参考になれば。

id:kabisuke

おお、まさしくこれが私の求めていたものです!

試しに1000件程度のデータでやってみたところ1分半くらいで

クエリが返ってきました。

結構ギリギリですね。(^^;)

あと一桁オーダーが増えるとアウトな感じですね。

結果クエリが50万行もあるので当然といえば当然ですが。。。

なにはともあれ、これでなんとかなりそうです。ありがとうございました。

2007/10/27 22:46:49
id:degucho No.2

degucho回答回数254ベストアンサー獲得回数682007/10/27 22:56:51

ポイント23pt

MSDEですか以下で強引に取れました。

JOINやnull値の扱いなどで環境依存があるかもしれませんが

一応標準規格でやっています


SELECT C.ID1
      ,C.ID2
      ,COUNT(C.B_CODE) AS CNT
  FROM
(
SELECT A.ID1
      ,A.ID2
      ,A.CODE AS A_CODE
      ,B.CODE AS B_CODE
  FROM
(
SELECT L.ID1
      ,L.ID2
      ,R.CODE
  FROM
(
SELECT X.*
FROM
(
SELECT L.ID AS ID1
      ,R.ID AS ID2
  FROM TABLE_A AS L
      ,TABLE_A AS R
) AS X
 WHERE  X.ID1 < X.ID2
) AS L
LEFT OUTER JOIN TABLE_B AS R
  ON L.ID1 = R.TABLE_A_ID
) AS A LEFT OUTER JOIN
(
SELECT L.ID1
      ,L.ID2
      ,R.CODE
  FROM
(
SELECT X.*
  FROM
(
SELECT L.ID AS ID1
      ,R.ID AS ID2
  FROM TABLE_A AS L
      ,TABLE_A AS R
) AS X
 WHERE X.ID1 < X.ID2
) AS L
LEFT OUTER JOIN TABLE_B AS R
  ON L.ID2 = R.TABLE_A_ID
) AS B
ON A.ID1=B.ID1 AND A.ID2=B.ID2 AND A.CODE=B.CODE
) AS C
GROUP BY C.ID1,C.ID2
ORDER BY C.ID1,C.ID2


ちなみにテーブル類

create table TABLE_A
(ID INTEGER
)
GO
INSERT INTO TABLE_A VALUES(1)
GO
INSERT INTO TABLE_A VALUES(2)
GO
INSERT INTO TABLE_A VALUES(3)
GO
INSERT INTO TABLE_A VALUES(4)
GO

create table TABLE_B
(ID INTEGER
,TABLE_A_ID INTEGER
,CODE CHAR(2)
)
GO

INSERT INTO TABLE_B VALUES(1,1,'10')
INSERT INTO TABLE_B VALUES(2,1,'11')
INSERT INTO TABLE_B VALUES(3,2,'10')
INSERT INTO TABLE_B VALUES(4,2,'11')
INSERT INTO TABLE_B VALUES(5,2,'12')
INSERT INTO TABLE_B VALUES(6,3,'10')
INSERT INTO TABLE_B VALUES(7,3,'11')
INSERT INTO TABLE_B VALUES(8,3,'12')
INSERT INTO TABLE_B VALUES(9,3,'13')
INSERT INTO TABLE_B VALUES(10,4,'13')
GO
id:kabisuke

おお、まさしく希望通りです。

・・・が、TABLE_Aが1000件もあるような状況では

さすがに終わりませんでした。

あと一歩ですが、残念です。

2007/10/28 00:07:19
id:chuken_kenkou No.3

chuken_kenkou回答回数722ベストアンサー獲得回数542007/10/28 01:54:49

ポイント22pt

SQLiteの環境がないのですが。。。

1行目の「case when cnt is null then 0 else cnt end as cnt」は、id1=1 & id2=4などのケースで、nullが返ってくるので、nullの場合は0に変換しています。

SQLiteのマニュアルを見たところ、関数が他のRDBMSと異なるところがあるようです。

Query Language Understood by SQLite: expression

もし、構文エラーになるようなら、SQLiteの構文に合わせてください。

性能に関してですが、TABLE_Bに、次のインデクスを定義してみてください。

create index Bix1 on TABLE_B(table_a_id,code)
select x.id1,x.id2,case when cnt is null then 0 else cnt end as cnt
 from
 (select A1.id as id1,A2.id as id2
   from TABLE_A as A1
    inner join TABLE_A as A2
    on A1.id<A2.id) as x
 left join
 (select B1.table_a_id as id1,B2.table_a_id as id2,count(B1.code) as cnt
   from TABLE_B as B1 
    left join TABLE_B as B2
     on B1.table_a_id<B2.table_a_id and B1.code=B2.code
   where B2.table_a_id is not null
   group by B1.table_a_id,B2.table_a_id) as y
 on x.id1=y.id1 and x.id2=y.id2
 order by x.id1,x.id2
</pre>
id:chuken_kenkou No.4

chuken_kenkou回答回数722ベストアンサー獲得回数542007/10/28 02:05:25

ポイント22pt

最初の回答のTABLE_BのLEFT JOINは、INNER JOINでよかったので、訂正版です。

LEFT JOINのままでも、「where B2.table_a_id is not null」の条件を入れてあるので、そのままでも

結果に違いはありません。

select x.id1,x.id2,case when cnt is null then 0 else cnt end as cnt
 from
 (select A1.id as id1,A2.id as id2
   from TABLE_A as A1
    inner join TABLE_A as A2
    on A1.id<A2.id) as x
 left join
 (select B1.table_a_id as id1,B2.table_a_id as id2,count(B1.code) as cnt
   from TABLE_B as B1 
    inner join TABLE_B as B2
     on B1.table_a_id<B2.table_a_id and B1.code=B2.code
   group by B1.table_a_id,B2.table_a_id) as y
 on x.id1=y.id1 and x.id2=y.id2
 order by x.id1,x.id2
</pre>
  • id:taknt
    id1とid2が何を示したもので
    countは、何の件数か示さないと わからない。

    また、どのようなSQL文を書いたのか コメントにでも書いておけば
    質問で足りない分の参考になる。
  • id:t_shiono
    あまり有益ではないかと思うのでコメントで。

    まず、確認ですが、出力例は、

    id1|id2|count
    1 |2 |2
    1 |3 |2 ←ここを修正
    1 |4 |0

    ですよね?
    id=1 => (10, 11)
    id=3 => (10, 11, 12, 13)
    なので、共通項は10と11の二つ

    こういう処理をしたいのですよね?


    これを一発のクエリで書くのはなかなか難しい気がします。
    1000件程度であれば、何かスクリプトで処理をしてあげれば、それほど時間はかからない気がします。

  • id:kabisuke
    コメントありがとうございます。

    >t_shionoさん

    >id=1 => (10, 11)
    >id=3 => (10, 11, 12, 13)
    >なので、共通項は10と11の二つ
    >こういう処理をしたいのですよね?
    そのとおりです。全くもって申し訳ないです。。。
    一発のクエリでは難しいものなのですか・・・
    ならば仕方ないのでINTERSECT句を使ってみますが、
    もう少しだけアドバイスを待ってみます。


    >takntさん

    t_shionoさんの指摘のとおりです。
    質問が上手くなくて申し訳ありません。
    書いたSQLなのですが、
    SELECT A1.id AS id1, A1.id AS id2
    FROM TABLE_A A1, TABLE_A A1, TABLE_B B1, TABLE_B B2
    WHERE A1.id < A2.id AND A1.id = B1.table_a_id AND A2.id = B2.table_a_id;
    みたいな感じにして取得してからさらにGROUP BYを使えばできるのかと
    思ってやってみたのですが、どうも上手くいきませんでした。

  • id:kabisuke
    すみません。
    FROM TABLE_A A1, TABLE_A A1, TABLE_B B1, TABLE_B B2
    ではなく
    FROM TABLE_A A1, TABLE_A A2, TABLE_B B1, TABLE_B B2
    でした。
  • id:chuken_kenkou
    TABLE_Bを自己結合し、id毎にcodeの集合を作り・・・といった作業から、group byしcount関数で件数を
    求めるというSQLを作っていったのですが、回答後に確認したら、結果的にTABLE_Bの自己結合の部分は、
    t_shionoさんとほぼ同じSQLになっていました。カンニングしたようで、すみません。
  • id:chuken_kenkou
    私が提示したSQLですが、SQLite3で実行可能なことを確認済です。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません