プログラマには「プログラムは思った通りに動かない。書いた通りに動く。」「バグではない仕様です」なんて面白い格言がいろいろありますが、データベースの世界にも「WHERE句で条件分岐させるのは素人のやること。プロはSELECT句で分岐させる」という格言があるそうです。
初めて聞いたのですが、ひとえに私の経験が浅いからでしょう(爆)
SQL実践入門を読んでいて面白かったSQL問題などをちょこちょこと書いていこうと思います。(データなどはちょっといじっていますが)
テーブル名 postalcode
| pcode | district_name |
|---|---|
| 4130001 | サンプル県サンプル市1 |
| 4130002 | サンプル県サンプル市2 |
| 4130103 | サンプル県サンプル市3 |
| 4103213 | サンプル県サンプル市4 |
| 4380824 | サンプル県サンプル市5 |
上記のようなリストがあった時、検索語句(4130033)の合致度が高いもの3つを選べ。
合致度とは4130033と合致している桁数が多いものですね。
例えばこの場合、4130001と4130002は41300まで合致していますので、合致度が高くなります。
この選び方ですが、whereを使わずにcase式でサクッと行けます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT pcode, district_name, CASE WHEN pcode = '41300003' THEN 0 WHEN pcode LIKE '4130000%' THEN 1 WHEN pcode LIKE '413000%' THEN 2 WHEN pcode LIKE '41300%' THEN 3 WHEN pcode LIKE '4130%' THEN 4 WHEN pcode LIKE '413%' THEN 5 WHEN pcode LIKE '41%' THEN 6 ELSE NULL END rank FROM postalcode ORDER BY rank ASC LIMIT 3; |
もう一問。今度はウィンドウ関数を使うと比較的楽に解ける問題です。
テーブル名 receipts
| cust_id | seq | price |
|---|---|---|
| A | 1 | 500 |
| A | 2 | 1000 |
| A | 3 | 700 |
| B | 5 | 100 |
| B | 6 | 50000 |
| B | 7 | 300 |
| B | 9 | 200 |
| B | 12 | 1000 |
| C | 10 | 600 |
| C | 20 | 100 |
| C | 45 | 200 |
| C | 70 | 50 |
| D | 3 | 2000 |
上記のテーブルでcust_idのseqが最大値のものと最小値の差を求めよ。
※例 Aの場合はseq=1が最小値でseq=3が最大値なので差は200となります。
この場合まず、cust_idごとの最大値と最小値を求めなければなりませんが、サブクエリを作るなどやると式が複雑になります。PostgreSQLなどであればPARTTIONを使い、
|
1 2 3 4 5 6 |
SELECT cust_id, price, RANK() OVER (PARTITION BY cust_id ORDER BY seq asc) min_seq_rank, RANK() OVER (PARTITION BY cust_id ORDER BY seq desc) max_seq_rank FROM receipts |
これで上のテーブルにランクをのせたテーブルにすることが出来ました。
この後はmax_seq_rank =1 のpriceとmin_seq_rank=1の引き算を行います。
これまたcaseを使うと下記のように比較的楽になります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT T1.cust_id, MAX( CASE WHEN T1.max_seq_rank = 1 THEN T1.price ELSE 0 END ) - MAX( CASE WHEN T1.min_seq_rank = 1 THEN T1.price ELSE 0 END ) FROM ( SELECT cust_id, price, RANK() OVER (PARTITION BY cust_id ORDER BY seq asc) min_seq_rank, RANK() OVER (PARTITION BY cust_id ORDER BY seq desc) max_seq_rank FROM receipts ) T1 GROUP BY cust_id; |
コツとしては一気にやらずに部分から組み立てていったほうがよいでしょう。また引き算の部分はまずおのおのの数値を出した後計算したほうがよいです。
私もいきなりやろうとすると挫折しましたので・・・・
