키별로 하나의 행만 유지하기

Coddy SQL 여정의 심화 과정 섹션에 포함된 레슨 — 27개 중 26번째.

종종 키(고객, 센서, 제품)가 여러 번 나타나는 테이블이 있으며, 그중 최신 항목이나 점수가 가장 높은 항목만 유지하고 싶은 경우가 있습니다.

ROW_NUMBER()를 top-N에 했던 것과 동일한 방식으로 사용하고, 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는 "최신순"을 의미합니다. 각 파티션의 1번 행이 유지하려는 행입니다.

챌린지

중급

사용 가능한 테이블 및 컬럼:
readings: sensor_id, recorded_at, value

최소 3개 이상의 측정값이 있는 각 센서에 대해, 가장 높은 값(highest-value)을 가진 단일 측정 기록을 반환하세요. 만약 value가 동일한 측정값이 두 개 이상이라면, 더 최근의 기록을 유지하세요.

sensor_id, recorded_at, value를 출력하고, sensor_id를 기준으로 정렬하세요.

치트 시트

PARTITION BY와 함께 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 — 각 고유 키에 대해 순위를 재설정합니다
ORDER BY ... DESC — 원하는 행(최신순, 높은 순 등)을 첫 번째로 배치합니다
WHERE rn = 1 — 파티션당 최상위 행만 유지합니다

직접 해보기

WITH ranked AS (
    -- ROW_NUMBER() OVER (PARTITION BY sensor_id ORDER BY value DESC, recorded_at DESC)
    -- 측정값이 3개 이상인 센서를 필터링하기 위한 COUNT(*) OVER (PARTITION BY sensor_id)
)
SELECT sensor_id, recorded_at, value
FROM ranked
WHERE rn = 1 AND total_readings >= 3
ORDER BY sensor_id