キーごとに1行を保持

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

キー（顧客、センサー、商品）が複数回登場するテーブルがあり、その中で最新のもの、またはスコアが最も高いものだけを残したいことがよくあります。

トップ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件返してください。valueが同じ読み取り値が複数ある場合は、より新しいものを選んでください。

sensor_id、recorded_at、valueを出力し、sensor_idの順に並べてください。

自分で試してみよう

WITH ranked AS (
    -- TODO: 各測定値を、そのセンサー内での順位 (rn) とともに選択する
    -- および、そのセンサーの測定値の数 (total_readings)
)
SELECT sensor_id, recorded_at, value
FROM ranked
WHERE rn = 1 AND total_readings >= 3
ORDER BY sensor_id

腕試し

このレッスンには短いクイズがあります。レッスンを始めて解答し、進捗を記録しましょう。

応用のすべてのレッスン

1条件分岐

CASE WHEN ORDER BYでのCASE NULLに対するCOALESCE NULLIFとIIF 復習 - 出席

4集合演算

UNION と UNION ALL INTERSECT EXCEPT 復習 - メンバーシップ

2文字列関数

LENGTH、UPPER、LOWER SUBSTR INSTR REPLACE と TRIM || による連結復習 - 請求書

5再帰CTE

数列階層をたどる復習 - 組織図

3条件付き集計

CASEを使ったCOUNT CASEを使ったSUM 行を列にピボット復習 - トラフィックソース

6分析レシピ

グループごとのTop-N 合計に占める割合累計キーごとに1行を保持復習 - 上位顧客

自分で練習してみよう: SQLプレイグラウンド