SQLServerでランキングを求めるとき -- OVERの使い方(OLAP関数)
「最後に買ったもの」を調べる
SQLで「誰が・何を・いつ買った」という買い物データから「その人が最後に買ったもの」を調べようとすると案外難しい。
データイメージ
PERSON GOODS PURCHASE_DATE +-------+-------+-------------- Tom Orange 2015-2-19 Tom Apple 2015-2-17 Nancy Apple 2015-2-19 Nancy Potato 2015-2-17
期待する結果
PERSON GOODS PURCHASE_DATE +-------+-------+-------------- Tom Orange 2015-2-19 Nancy Apple 2015-2-19
MAXのA列に該当するレコードのB列といったデータを取り出してくださいと言われて少々答えに窮したのでここにメモしておく。
こういうデータを取り出すときは相関サブクエリかOLAP関数を使うとできる。
おすすめはOLAP関数を使うやり方で、相関サブクエリを使うとどうしてもSQL文が読みにくくなる。
相関サブクエリで頑張る方法
まず、相関サブクエリでやる方法
クエリ1
WITH PURCHASE (PERSON, GOODS, PURCHASE_DATE) AS ( SELECT 'Tom' PERSON, 'Orange' GOODS, '2015-2-19' PURCHASE_DATE UNION ALL SELECT 'Tom' PERSON, 'Apple' GOODS, '2015-2-17' PURCHASE_DATE UNION ALL SELECT 'Nancy' PERSON, 'Apple' GOODS, '2015-2-19' PURCHASE_DATE UNION ALL SELECT 'Nancy' PERSON, 'Potato' GOODS, '2015-2-17' PURCHASE_DATE ) SELECT X.* FROM PURCHASE X WHERE ( SELECT COUNT(*) FROM PURCHASE Y WHERE X.PERSON = Y.PERSON AND X.PURCHASE_DATE < Y.PURCHASE_DATE ) = 0 ;
結果
PERSON GOODS PURCHASE_DATE Tom Orange 2015-2-19 Nancy Apple 2015-2-19
自分より購入日が後のデータを持たない行だけ取り出す。
一応これで期待する結果を取り出せるのだが、自己相関サブクエリはやはりSQL文が理解しにくい。
1位タイを許さないケースに対応しようとすると更に難しい
クエリ1のデータは1位タイ(=最終購入日が同じレコードが存在しない)けれども、1位タイが実際に発生するデータでなおかつ1位タイを許さず商品名称で昇順ソートして先頭に来る1件だけを結果に取り出すとなると、とても難解なSQL文を書かなければならなくなる(以下)。
クエリ2-1 1位タイが生じてしまうケース
WITH PURCHASE (PERSON, GOODS, PURCHASE_DATE) AS ( SELECT 'Tom' PERSON, 'Orange' GOODS, '2015-2-19' PURCHASE_DATE UNION ALL SELECT 'Tom' PERSON, 'Apple' GOODS, '2015-2-17' PURCHASE_DATE UNION ALL SELECT 'Nancy' PERSON, 'Hamburger' GOODS, '2015-2-19' PURCHASE_DATE UNION ALL SELECT 'Nancy' PERSON, 'Apple' GOODS, '2015-2-19' PURCHASE_DATE UNION ALL SELECT 'Nancy' PERSON, 'Potato' GOODS, '2015-2-17' PURCHASE_DATE ) SELECT X.* FROM PURCHASE X WHERE ( SELECT COUNT(*) FROM PURCHASE Y WHERE X.PERSON = Y.PERSON AND X.PURCHASE_DATE < Y.PURCHASE_DATE ) = 0 ;
結果
PERSON GOODS PURCHASE_DATE Tom Orange 2015-2-19 Nancy Hamburger 2015-2-19 Nancy Apple 2015-2-19
クエリ2-2 1位タイを許さないケース
WITH PURCHASE (PERSON, GOODS, PURCHASE_DATE) AS ( SELECT 'Tom' PERSON, 'Orange' GOODS, '2015-2-19' PURCHASE_DATE UNION ALL SELECT 'Tom' PERSON, 'Apple' GOODS, '2015-2-17' PURCHASE_DATE UNION ALL SELECT 'Nancy' PERSON, 'Hamburger' GOODS, '2015-2-19' PURCHASE_DATE UNION ALL SELECT 'Nancy' PERSON, 'Apple' GOODS, '2015-2-19' PURCHASE_DATE UNION ALL SELECT 'Nancy' PERSON, 'Potato' GOODS, '2015-2-17' PURCHASE_DATE ) SELECT ZZ.* FROM PURCHASE ZZ WHERE EXISTS ( SELECT 1 FROM ( SELECT Z.PERSON, MIN(Z.GOODS) GOODS FROM ( SELECT X.*, ( SELECT COUNT(*) FROM PURCHASE Y WHERE X.PERSON = Y.PERSON AND X.PURCHASE_DATE < Y.PURCHASE_DATE ) LATER FROM PURCHASE X ) Z WHERE Z.LATER = 0 GROUP BY Z.PERSON ) ZA WHERE ZZ.PERSON = ZA.PERSON AND ZZ.GOODS = ZA.GOODS ) ;
結果
PERSON GOODS PURCHASE_DATE Tom Orange 2015-2-19 Nancy Apple 2015-2-19
これをどうやって他人に分かりやすく説明しろと言うのか。
一回PERSONとGOODSの組で一意の集合を作ってEXISTSで調べると望む結果を取り出せるが、読みにくいことこの上ない。
OLAP関数を使うと簡潔に書ける -- SQLServerだったら集約関数 + OVER句
同じ結果を導き出すのにOVER句を使うとものすごく簡単に書ける。
クエリ3
WITH PURCHASE (PERSON, GOODS, PURCHASE_DATE) AS ( SELECT 'Tom' PERSON, 'Orange' GOODS, '2015-2-19' PURCHASE_DATE UNION ALL SELECT 'Tom' PERSON, 'Apple' GOODS, '2015-2-17' PURCHASE_DATE UNION ALL SELECT 'Nancy' PERSON, 'Hamburger' GOODS, '2015-2-19' PURCHASE_DATE UNION ALL SELECT 'Nancy' PERSON, 'Apple' GOODS, '2015-2-19' PURCHASE_DATE UNION ALL SELECT 'Nancy' PERSON, 'Potato' GOODS, '2015-2-17' PURCHASE_DATE ) SELECT PERSON, GOODS, PURCHASE_DATE FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY PERSON ORDER BY PURCHASE_DATE DESC, GOODS) RANKING, * FROM PURCHASE ) X WHERE X.RANKING = 1 ;
結果
PERSON GOODS PURCHASE_DATE Nancy Apple 2015-2-19 Tom Orange 2015-2-19
PERTITION BYにPERSONを指定して購入者ごとにデータを区切った中で、購入日が最新かつ商品名称が辞書順で昇順にORDER BYするようOVER句で指示した上でROW_NUMBER関数を使って連番を振っていくと簡単にランキングを求めることができる。