素人によるSQLパズルゲーム :: ひまつぶし雑記帖

素人によるSQLパズルゲーム

2015/1/31 [12:49:15] (土)

ここにあるのは素人芸なのできちんと調べるなら
「図でイメージするOracle DatabaseのSQL全集」
https://www.oracle.com/technetwork/jp/articles/otnj-sql-image1-308625-ja.html
をオススメ。

以下は、このORACLEのページを元ネタにして雑記帖のカテゴリをいじくった時のメモ。
（具体的にデータベースの構造を晒すのはセキュリティ的に問題があるような気がするので端折った）

「カテゴリを元に雑記帖の記事をリストアップしたい」

・雑記単位で一意のIDがある
・雑記に紐付くカテゴリがゼロ以上複数個ある

雑記ID	本文など雑記内容詳細

という雑記帖詳細テーブル

カテゴリID

カテゴリ内容詳細

というカテゴリ詳細テーブル

このふたつを繋ぐために

雑記ID

カテゴリID

という雑記とカテゴリを紐付けるテーブルがある。各々のIDから詳細テーブルがわかるので内容を引っ張りだせる。

「電子書籍」というカテゴリがついた雑記を引っ張りだすには、雑記IDで雑記カテゴリを繋ぐ。inner join、内部結合というやつ。
select 雑記ID from 雑記帖詳細 inner join 雑記カテゴリ ON 雑記ID=雑記カテゴリの雑記ID AND 雑記カテゴリのカテゴリID=電子書籍ID
でシンプルにあっさり。

複数のカテゴリから雑記を引っ張りだすのが少し面倒くさい。

その1 和集合

A「電子書籍」か、B「kindle」のどちらかのカテゴリのついた雑記を引っ張りだすには
select 雑記ID from 雑記帖詳細 inner join 雑記カテゴリ ON 雑記ID=雑記カテゴリの雑記ID AND 雑記カテゴリのカテゴリID in (電子書籍ID,kindleID)
これでカテゴリに「電子書籍」か「kindle」がついた雑記を引っ張りだせるけど、両方ともついた雑記が2回出てくる。

雑記1	電子書籍
雑記2	kindle
雑記3	電子書籍
雑記3	kindle

雑記3のケース。
IDが重複していると、同じ雑記を表示することになってしまうので、重複IDをひとつにするために distinct を使う。
select distinct(雑記ID) from 雑記帖詳細 inner join 雑記カテゴリ ON 雑記ID=雑記カテゴリの雑記ID AND 雑記カテゴリのカテゴリID in (電子書籍ID,kindleID)

その2 積集合

A「電子書籍」とB「kindle」、両方のカテゴリがついた雑記を引っ張りだすには intersect を使ってふたつの検索結果であるテーブルの積集合をとることになるらしい。
select 雑記ID from 雑記帖詳細 inner join 雑記カテゴリ ON 雑記ID=雑記カテゴリの雑記ID AND 雑記カテゴリのカテゴリID=電子書籍ID intersect select 雑記ID from 雑記帖詳細 inner join 雑記カテゴリ ON 雑記ID=雑記カテゴリの雑記ID AND 雑記カテゴリのカテゴリID=kindleID
1行目のselect文で電子書籍IDのついた雑記を引っ張りだし、3行目のselect文でkindleIDのついた雑記を引っ張りだす。
intersect が、ふたつから共通する雑記IDだけを引っ張りだしている。

この引っ張りだした結果を select で括って条件でフィルタしたり順番に並べたりする。
select * from ( select 雑記ID from 雑記帖詳細 inner join 雑記カテゴリ ON 雑記ID=雑記カテゴリの雑記ID AND 雑記カテゴリのカテゴリID=電子書籍ID intersect select 雑記ID from 雑記帖詳細 inner join 雑記カテゴリ ON 雑記ID=雑記カテゴリの雑記ID AND 雑記カテゴリのカテゴリID=kindleID ) where文 order by 並び順 limit 個数 offset どこから

積集合じゃないけど、両方のカテゴリがついた雑記を引っ張りだすもうひとつのやり方。
ひとつの雑記に「電子書籍」「電子書籍」などとカテゴリは重複しない。ということを前提にすると。
select 雑記ID from 雑記帖詳細 inner join 雑記カテゴリ ON 雑記ID=雑記カテゴリの雑記ID AND 雑記カテゴリのカテゴリID in (電子書籍ID,kindleID) group by 雑記ID having count(雑記ID) = 2
というのもありかも知れない。
・カテゴリに「電子書籍」か「kindle」がついた雑記を引っ張りだす
・雑記IDごとにグループ化＝集計する
・集計結果が「2」であればカテゴリがふたつともある
最初はこっちで実装してみた。

intersectを使ってtableをふたつ結合するのは処理が重い＝遅いんじゃないかと思ったんだけど、計測してみたら全然差がなかった…たかだか（現状）2700程度の数だし。
それなら、カテゴリの重複はない、などと前提条件がつくよりは素直にテーブルを結合させた方が安心安全、かな。

https://t2aki.doncha.net/index.pl?cat=5%2C43
↑カテゴリをコンマで繋いで複数カテゴリのリストアップ。
これは「日野裕太郎」と「doncha.net」の組み合わせ。

日野裕太郎は紙本（商業・同人）があるし、ウチ以外からの電子書籍もあるので細かいコンロールをしたかったんだよなあ。

いろんな場面でカテゴリの組合せが使えるのがスゲーありがたい（自画自賛芸）

perl にしろ SQL にしろ、ネットの情報・紙の解説本が豊富なので、わたしのような素人なら素人なりにレベルに応じて使えるので本当に助かります。