Jeden wiersz na klucz

Część sekcji Poza podstawami ścieżki SQL w Coddy. Lekcja 26 z 27.

Często będziesz mieć tabelę, w której klucz (klient, czujnik, produkt) pojawia się wielokrotnie, a chcesz zachować tylko najnowszy wpis lub ten z najwyższym wynikiem.

Użyj ROW_NUMBER() tak samo jak w przypadku top-N, a następnie zachowaj 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 oznacza „jeden ranking dla tego klucza”; ORDER BY oznacza „od najnowszego”. Wiersz 1 w każdej partycji to wiersz, który chcesz zachować.

Wyzwanie

Średni

Dostępne tabele i kolumny:
readings: sensor_id, recorded_at, value

Dla każdego czujnika, który ma co najmniej 3 odczyty, zwróć jego pojedynczy odczyt o najwyższej wartości. Jeśli dwa odczyty mają taką samą wartość w value, wybierz nowszy.

Zwróć sensor_id, recorded_at, value, posortowane według sensor_id.

Spróbuj swoich sił

WITH ranked AS (
    -- ROW_NUMBER() OVER (PARTITION BY sensor_id ORDER BY value DESC, recorded_at DESC)
    -- COUNT(*) OVER (PARTITION BY sensor_id) do filtrowania czujników z co najmniej 3 odczytami
)
SELECT sensor_id, recorded_at, value
FROM ranked
WHERE rn = 1 AND total_readings >= 3
ORDER BY sensor_id