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

Часть раздела Продвинутый уровень путешествия по 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.

Попробуйте сами

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