Nur eine Zeile pro Schlüssel behalten

Teil des Abschnitts Über die Grundlagen hinaus der SQL-Journey von Coddy — Lektion 26 von 27.

Oft hat man eine Tabelle, in der ein Schlüssel (ein Kunde, ein Sensor, ein Produkt) mehrfach vorkommt und man nur den neuesten oder denjenigen mit der höchsten Punktzahl behalten möchte.

Verwenden Sie ROW_NUMBER() auf die gleiche Weise wie für Top-N, und behalten Sie dann rn = 1 bei.

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

Das PARTITION BY besagt „ein Ranking pro diesen Schlüssel“; das ORDER BY besagt „neueste zuerst“. Zeile 1 in jeder Partition ist die Zeile, die Sie behalten möchten.

Aufgabe

Mittel

Verfügbare Tabellen und Spalten:
readings: sensor_id, recorded_at, value

Für jeden Sensor, der mindestens 3 Messwerte hat, gib den einzelnen Messwert mit dem höchsten Wert zurück. Wenn zwei Messwerte den gleichen value aufweisen, behalte den neueren.

Gib sensor_id, recorded_at und value aus, sortiert nach sensor_id.

Spickzettel

Verwenden Sie ROW_NUMBER() mit PARTITION BY, um nur die neueste (oder beste) Zeile pro Schlüssel zu behalten, und filtern Sie dann nach 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 — setzt das Ranking für jeden eindeutigen Schlüssel zurück
ORDER BY ... DESC — setzt die gewünschte Zeile an die erste Stelle (neueste, höchste usw.)
WHERE rn = 1 — behält nur diese oberste Zeile pro Partition

Probier es selbst

WITH ranked AS (
    -- ROW_NUMBER() OVER (PARTITION BY sensor_id ORDER BY value DESC, recorded_at DESC)
    -- COUNT(*) OVER (PARTITION BY sensor_id) um Sensoren mit >= 3 Messwerten zu filtern
)
SELECT sensor_id, recorded_at, value
FROM ranked
WHERE rn = 1 AND total_readings >= 3
ORDER BY sensor_id