キーごとに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で並べ替えてください。

自分で試してみよう

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