SQL case式やウィンドウ関数の威力

投稿日：2016年10月20日

プログラマには「プログラムは思った通りに動かない。書いた通りに動く。」「バグではない仕様です」なんて面白い格言がいろいろありますが、データベースの世界にも「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式でサクッと行けます。

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;

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を使い、

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

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を使うと下記のように比較的楽になります。

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;

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;

コツとしては一気にやらずに部分から組み立てていったほうがよいでしょう。また引き算の部分はまずおのおのの数値を出した後計算したほうがよいです。

私もいきなりやろうとすると挫折しましたので・・・・