人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

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は勉強し始めたところでどうすればいいのかわかりません。
どなたかご教授ください。



●質問者: kabisuke
●カテゴリ:コンピュータ
✍キーワード:SQL SQLite 勉強 教授 適当
○ 状態 :終了
└ 回答数 : 4/4件

▽最新の回答へ

1 ● t_shiono
●23ポイント

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

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という部分は抜けてます。

何かの参考になれば。

◎質問者からの返答

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

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

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

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

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

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

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


2 ● degucho
●23ポイント

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
◎質問者からの返答

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

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

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

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


3 ● chuken_kenkou
●22ポイント

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>

4 ● chuken_kenkou
●22ポイント

最初の回答の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>
関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ