Одна строка на ключ

Часть раздела Продвинутый уровень путешествия по SQL на Coddy — урок 26 из 27.

Часто у вас будет таблица, в которой ключ (клиент, датчик, продукт) встречается несколько раз, и вы хотите оставить только последний или тот, у которого самый высокий балл.

Используйте 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 показания, верните его единственное показание с самым высоким значением. Если два показания имеют одинаковое значение 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