キーごとに1行を保持する

CoddyのSQLジャーニー「応用」セクションの一部 — レッスン 26/27。

キー（顧客、センサー、製品など）が複数回出現するテーブルを扱うことがよくあります。そして、その中から最新のもの、あるいは最もスコアが高いものだけを保持したい場合があります。

top-N の時と同じように ROW_NUMBER() を使用し、rn = 1 を保持します。

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id ORDER BY purchased_at DESC
           ) AS rn
    FROM purchases
)
SELECT * FROM ranked WHERE rn = 1

PARTITION BY は「このキーごとに1つのランキング」を意味し、ORDER BY は「新しい順」を意味します。各パーティションの1行目が、保持したい行です。

チャレンジ

中級

利用可能なテーブルとカラム:
readings: sensor_id, recorded_at, value

少なくとも3つの測定値を持つ各センサーについて、その中で最も高い値の測定値を1つ返してください。2つの測定値の value が同じ場合は、より新しい方を保持してください。

sensor_id、recorded_at、value を出力し、sensor_id で並べ替えてください。

チートシート

ROW_NUMBER() を PARTITION BY と共に使用して、キーごとに最新（または最適）の行のみを保持し、その後 rn = 1 でフィルタリングします。

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id ORDER BY purchased_at DESC
           ) AS rn
    FROM purchases
)
SELECT * FROM ranked WHERE rn = 1

PARTITION BY — 各ユニークキーに対してランキングをリセットします
ORDER BY ... DESC — 目的の行を最初に配置します（最新、最高など）
WHERE rn = 1 — パーティションごとにその最上位の行のみを保持します

自分で試してみよう

WITH ranked AS (
    -- ROW_NUMBER() OVER (PARTITION BY sensor_id ORDER BY value DESC, recorded_at DESC)
    -- COUNT(*) OVER (PARTITION BY sensor_id) を使用して、読み取り値が 3 つ以上のセンサーをフィルタリングする
)
SELECT sensor_id, recorded_at, value
FROM ranked
WHERE rn = 1 AND total_readings >= 3
ORDER BY sensor_id