指定した購入者ごとの最後の購入時の商品名、購入額、購入日
こちらを取得するSQL文を作成します。
購入者ごとにグループを分けてそれぞれの最終購入日のレコードを取得します。
グループごとに連番をつけるROW_NUMBER関数とRANK関数を使った方法を紹介したいと思います。
目次
1. 検証環境
2. 対象テーブル
2-1. usersテーブル
2-2. itemsテーブル
2-3. ordersテーブル
3. ROW_NUMBER関数
4. RANK関数
5. おまけ
6. 参考記事
1. 検証環境
macOS Monterey 12.3.1
PostgreSQL 14.4
2. 対象テーブル
今回例として使用するのは、顧客を管理するusersテーブル、商品を管理するitemsテーブル、購入履歴を管理するordersテーブルの3つです。
（サンプルとして簡単に用意したものです。設計的な不備はお許しください。）
2-1. usersテーブル
2-2. itemsテーブル
2-3. ordersテーブル
3. ROW_NUMBER関数
次のような書き方をします。
SELECT
user_name AS 購入者
, item_name AS 商品名
, total_price AS 購入額
, date AS 購入日
FROM
(
SELECT
u.name AS user_name
, i.name AS item_name
, i.price * o.quanity AS total_price
, o.date
, ROW_NUMBER() OVER (
PARTITION BY
u.name
ORDER BY
o.date DESC
) AS rank
FROM
orders AS o
INNER JOIN
users AS u ON u.id = o.user_id
INNER JOIN
items AS i ON i.id = o.item_id
) AS t
WHERE
user_name IN ('佐藤', '田中') /* 指定された購入者 */
AND rank = 1
;
結果
OVER句のPARTITION BYでグループ化するカラムを、ORDER BYで表示順を指定します。
ROW_NUMBER関数で取得した連番（ここでいうrank）をwhere句で指定するためには、サブクエリでFROMを指定する必要があります。
ちなみに条件を指定せずに全てのデータを取得するとこのような結果になっています。
この中から指定された購入者のrank = 1のレコードを取得しているということになります。
4. RANK関数
RANK関数も使い方は同じで次のように書きます。
SELECT
user_name AS 購入者
, item_name AS 商品名
, total_price AS 購入額
, date AS 購入日
FROM
(
SELECT
u.name AS user_name
, i.name AS item_name
, i.price * o.quanity AS total_price
, o.date
, RANK() OVER (
PARTITION BY
u.name
ORDER BY
o.date DESC
) AS rank
FROM
orders AS o
INNER JOIN
users AS u ON u.id = o.user_id
INNER JOIN
items AS i ON i.id = o.item_id
) AS t
WHERE
user_name IN ('佐藤', '田中') /* 指定された購入者 */
AND rank = 1
;
ROW_NUMBER関数との違いはグループ内の表示順（この例でいう購入日）が同一のものは同じ番号が付けられるという点です。
なので同順位のものがある場合は複数のレコードが取得されます。
必要に応じて使い分ける必要があります。
5. おまけ
これ以降は特に参考になる内容ではないです。
このSQL文が出来上がるまでの経緯を私の失敗例と共に残しておきます。
そもそもは、
「指定した購入者の最後の購入時の商品名、購入額、購入日」
を取得するSQL文の作成依頼がありました。
「簡単にできそうだな…」
と思って作成したSQL文がこちらです。
SELECT
u.name AS 購入者
, i.name AS 商品名
, i.price * o.quanity AS 購入額
, o.date AS 購入日
FROM
orders AS o
INNER JOIN
users AS u ON u.id = o.user_id
INNER JOIN
items AS i ON i.id = o.item_id
WHERE
u.name = '佐藤' /* 指定された購入者 */
ORDER BY
o.date DESC
LIMIT 1
;
これで報告した際に、「購入者を複数指定したい場合は？」となったので調べて今回の関数を使用することになりました。
ざっと調べて書いてみたのがこちらです。
SELECT
u.name AS 購入者
, i.name AS 商品名
, i.price * o.quanity AS 購入額
, o.date AS 購入日
, ROW_NUMBER() OVER (
PARTITION BY
u.name
ORDER BY
o.date DESC
) AS rank
FROM
orders AS o
INNER JOIN
users AS u ON u.id = o.user_id
INNER JOIN
items AS i ON i.id = o.item_id
WHERE
u.name IN ('佐藤', '田中') /* 指定された購入者 */
AND rank = 1
;
これだと”rank”なんてないよ！とエラーが出ます。
なのでwhere句で指定するためにはサブクエリの中で関数を使う必要があるという訳です。
6. 参考記事
【SQL】グループごとに最大の値を持つレコードを取得する方法3選
【SQL】PARTITION BYの基礎から列ごとの計算方法まとめ