今川館

都内勤務の地味OLです

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関数を使って連番を振っていくと簡単にランキングを求めることができる。