SQL Interview Prep · Lektion

Der Trick mit der Differenz von Zeilennummern

ROW_NUMBER von einer Sequenz subtrahieren, um aufeinanderfolgende Werte zu Inseln zu gruppieren

Der Trick mit der Differenz von Zeilennummern ist eine kostenlose SQL Interview Prep-Lektion auf CoddyKit. Dies ist Lektion 2 von 4. Du kannst die komplette Lektion unten kostenlos lesen – dann übst du sie direkt im Browser mit einem integrierten Code-Editor und einem KI-Tutor rund um die Uhr. Sie ist Teil des SQL Interview Prep-Lernpfads, und dein Fortschritt wird über Web und CoddyKit-App synchronisiert. Der SQL Interview Prep-Kurs umfasst insgesamt 4 Lektionen.

Der eleganteste Gruppenschlüssel für Inseln

Der Trick mit der Differenz aus Wert und Zeilennummer ist die Technik, die Interviewer für Inseln aus aufeinanderfolgenden Ganzzahlen oder Daten am liebsten sehen. Er erzeugt den Gruppenschlüssel mit einer einzigen Subtraktion – ohne LAG und ohne laufende Summe.

Die gesamte Idee: Ziehen Sie eine ROW_NUMBER vom Wert selbst ab. In jeder Folge aufeinanderfolgender Werte erhöhen sich sowohl der Wert als auch die Zeilennummer bei jedem Schritt genau um 1, daher ist ihre Differenz über die gesamte Folge hinweg konstant. Diese Konstante ist Ihr Insel-Schlüssel.

Warum die Differenz konstant bleibt

Betrachten Sie zwei benachbarte Zeilen in einer Folge aufeinanderfolgender Werte. Beim Übergang von der einen zur nächsten erhöht sich der Wert um 1 und die Zeilennummer ebenfalls um 1. Wenn Sie sie voneinander abziehen, heben sich die beiden +1 auf, sodass sich value - row_number nicht ändert.

Sobald jedoch eine Lücke auftritt, springt der Wert um mehr als 1, während die Zeilennummer weiterhin nur um 1 steigt. Die Differenz nimmt eine neue Konstante an. Genau diese Änderung trennt eine Insel von der nächsten.

Anhand unserer Daten nachvollziehen

Erinnern Sie sich an die Anmeldetage 1, 2, 3, 7, 8, 10. Stellen wir die Zeilennummer und die Differenz nebeneinander:

Tag 1, rn 1, diff 0
Tag 2, rn 2, diff 0
Tag 3, rn 3, diff 0
Tag 7, rn 4, diff 3
Tag 8, rn 5, diff 3
Tag 10, rn 6, diff 4

Die Differenzen (0,0,0,3,3,4) teilen die Zeilen perfekt in die drei Inseln auf. Gleiche Differenz bedeutet gleiche Insel.

SELECT
  day_no,
  ROW_NUMBER() OVER (ORDER BY day_no) AS rn,
  day_no - ROW_NUMBER() OVER (ORDER BY day_no) AS grp
FROM logins
ORDER BY day_no;

Zu Inseln zusammenfassen

Mit der Differenz als Gruppenschlüssel folgt die abschließende Abfrage dem üblichen Zusammenfassungsmuster. Kapseln Sie die Differenz in einer CTE und wenden Sie GROUP BY darauf an:

Dies liefert dieselben drei Inseln wie zuvor, aber das SQL ist kürzer und übersichtlicher als die Variante mit LAG und laufender Summe. Für ganzzahlige oder gleichmäßig fortlaufende Sequenzen ist dies die Lösung, nach der Sie zuerst greifen sollten.

WITH keyed AS (
  SELECT
    day_no,
    day_no - ROW_NUMBER() OVER (ORDER BY day_no) AS grp
  FROM logins
)
SELECT
  MIN(day_no) AS start_day,
  MAX(day_no) AS end_day,
  COUNT(*)    AS length
FROM keyed
GROUP BY grp
ORDER BY start_day;

Der Haken: Werte müssen in Einerschritten steigen

Der einfache Differenztrick setzt voraus, dass die Sequenz bei jedem Schritt um genau 1 steigt. Das gilt für lückenlose Ganzzahlen und aufeinanderfolgende Kalendertage, funktioniert aber nicht, wenn Ihre Werte in einer anderen festen Schrittweite steigen oder Duplikate enthalten.

Auch gerade Werte wie 2,4,6,8 erscheinen bei einer Subtraktion von Wert und Zeilennummer als Lücken.
Duplikate bringen die Zuordnung durcheinander, weil die Zeilennummer weiter steigt, der Wert aber gleich bleibt.

Diese Einschränkung zu kennen und zu wissen, wie Sie sie beheben, unterscheidet einen auswendig gelernten Trick von echtem Verständnis.

Sequenzen mit festen Schrittweiten korrigieren

Wenn die Werte statt in Einerschritten um eine bekannte Konstante k steigen, normalisieren Sie sie zunächst: Teilen Sie den Wert durch k (oder verwenden Sie value / k bei Ganzzahlen), sodass jeder Schritt wieder 1 beträgt, und subtrahieren Sie anschließend die Zeilennummer.

Bei geraden Zahlen mit einer Schrittweite von 2 verwenden Sie beispielsweise day_no / 2 - ROW_NUMBER(). Der normalisierte Wert steigt nun bei jedem aufeinanderfolgenden Element um 1 und stellt damit die Eigenschaft der konstanten Differenz wieder her.

SELECT
  val,
  (val / 2) - ROW_NUMBER() OVER (ORDER BY val) AS grp
FROM even_series
ORDER BY val;

Auf Datumswerte anwenden

Datumswerte sind die häufigste praktische Anwendung. Kalenderdaten lassen sich nicht direkt von einer Zeilennummer subtrahieren. Wandeln Sie das Datum daher zunächst in eine Tagesanzahl um. Subtrahieren Sie in Postgres ein festes Ankerdatum vom Datum, um eine ganzzahlige Anzahl von Tagen zu erhalten, und wenden Sie anschließend denselben Trick an.

Da sich aufeinanderfolgende Kalendertage um 1 unterscheiden, ist die Differenz zwischen der Tagesanzahl und der Zeilennummer innerhalb einer Insel wieder konstant.

WITH keyed AS (
  SELECT
    login_date,
    (login_date - DATE '2000-01-01')
      - ROW_NUMBER() OVER (ORDER BY login_date) AS grp
  FROM daily_logins
)
SELECT MIN(login_date) AS start_date,
       MAX(login_date) AS end_date,
       COUNT(*)        AS days_in_run
FROM keyed GROUP BY grp ORDER BY start_date;

Datumsdifferenzen in verschiedenen SQL-Dialekten

Die Umwandlung eines Datums in eine Ganzzahl unterscheidet sich je nach Datenbank-Engine. Interviewer schätzen es, wenn Sie die Unterschiede zwischen SQL-Dialekten kennen:

Postgres: Subtrahieren Sie ein Datums-Literal: login_date - DATE '2000-01-01' ergibt eine Ganzzahl.
MySQL: Verwenden Sie DATEDIFF(login_date, '2000-01-01').
SQL Server: Verwenden Sie DATEDIFF(day, '2000-01-01', login_date).

Auf manchen Engines gibt es einen noch eleganteren Weg: Subtrahieren Sie mithilfe von Intervallarithmetik direkt ROW_NUMBER Tage vom Datum und gruppieren Sie anschließend nach dem resultierenden Ankerdatum.

SELECT
  login_date,
  login_date - (ROW_NUMBER() OVER (ORDER BY login_date)
               * INTERVAL '1 day') AS grp_date
FROM daily_logins;

Partitionen pro Gruppe hinzufügen

Für benutzerspezifische Inseln partitionieren Sie die Zeilennummer nach der Gruppenspalte. Entscheidend ist, dass der Gruppenschlüssel anschließend auch die Partitionsspalte enthalten muss, da zwei verschiedene Benutzer zufällig denselben Differenzwert erzeugen können.

Gruppieren Sie daher sowohl nach user_id als auch nach der berechneten Differenz. Wenn Sie user_id im abschließenden GROUP BY vergessen, entsteht ein subtiler Fehler, den Interviewer gerne entdecken.

WITH keyed AS (
  SELECT user_id, day_no,
    day_no - ROW_NUMBER()
      OVER (PARTITION BY user_id ORDER BY day_no) AS grp
  FROM logins
)
SELECT user_id, MIN(day_no) AS start_day,
       MAX(day_no) AS end_day, COUNT(*) AS len
FROM keyed
GROUP BY user_id, grp
ORDER BY user_id, start_day;

Trick oder LAG: Welches Verfahren verwenden

Ihnen stehen jetzt zwei solide Techniken zur Verfügung. Wählen Sie bewusst:

Differenz aus Zeilennummern: Die kürzeste und sauberste Lösung für Folgen gleichmäßig ansteigender Werte, etwa lückenlose Ganzzahlen oder aufeinanderfolgende Datumswerte. Erste Wahl, wenn Nachbarschaft bedeutet, dass sich die Werte um eine Konstante unterscheiden.
LAG plus laufende Summe: Flexibler, wenn die Nachbarschaft keiner festen numerischen Schrittweite folgt, etwa bei „derselbe Status wie in der vorherigen Zeile“ oder unregelmäßigen, benutzerdefinierten Regeln.

Nennen Sie im Interview Ihre Wahl und begründen Sie sie. Die Begründung beeindruckt mehr als die Syntax.

Duplikate robust behandeln

Wenn sich ein Wert wiederholen kann und Sie trotzdem eine Insel pro zusammenhängender Folge erhalten möchten, entfernen Sie zunächst Duplikate mit DISTINCT oder durch einen Gruppierungsschritt, sodass die Zeilennummer jeden Wert genau einmal abbildet. Alternativ können Sie DENSE_RANK statt ROW_NUMBER verwenden, sodass gleiche Werte denselben Rang erhalten.

Fragen Sie den Interviewer immer, ob Duplikate vorkommen können. Die richtige Absicherung hängt davon ab, ob Duplikate eine Folge verlängern oder innerhalb einer Folge ignoriert werden sollen.

WITH d AS (SELECT DISTINCT day_no FROM logins)
SELECT day_no,
  day_no - ROW_NUMBER() OVER (ORDER BY day_no) AS grp
FROM d;

Kurze Überprüfung

Vergewissern Sie sich, dass Sie verstehen, warum der Trick funktioniert.

Zusammenfassung: Der Differenztrick

Sie beherrschen jetzt den saubersten Insel-Schlüssel:

Schlüsselformel: value - ROW_NUMBER() OVER (ORDER BY value) ist für jede zusammenhängende Folge konstant.
Gruppieren Sie nach der Differenz mit GROUP BY, um Anfang, Ende und Länge zu ermitteln.
Normalisieren Sie Sequenzen mit festen Schrittweiten zunächst, indem Sie durch die Schrittweite teilen.
Wandeln Sie Datumswerte über die Differenzfunktion des jeweiligen Dialekts in eine ganzzahlige Tagesanzahl um.
Pro Gruppe: Partitionieren Sie die Zeilennummer mit PARTITION BY und nehmen Sie die Gruppenspalte in das abschließende GROUP BY auf.
Fangen Sie Duplikate mit DISTINCT oder DENSE_RANK ab.

Als Nächstes wechseln wir den Fokus von den Inseln zu den leeren Zwischenräumen und suchen nach Lücken.

Häufig gestellte Fragen

Ist die Lektion „Der Trick mit der Differenz von Zeilennummern“ kostenlos?

Ja — der vollständige Text von „Der Trick mit der Differenz von Zeilennummern“ ist hier im Web kostenlos zu lesen. Um sie interaktiv zu üben (integrierter Code-Editor und 24/7 KI-Tutor) und den Rest des SQL Interview Prep-Kurses freizuschalten, upgrade auf CoddyKit PRO. Der SQL Interview Prep-Kurs umfasst insgesamt 4 Lektionen.

Was lerne ich in „Der Trick mit der Differenz von Zeilennummern“?

ROW_NUMBER von einer Sequenz subtrahieren, um aufeinanderfolgende Werte zu Inseln zu gruppieren Du übst SQL Interview Prep mit praktischem Code, den du direkt im Browser ausführst, und ein 24/7 KI-Tutor beantwortet deine Fragen während du die Lektion bearbeitest.

Brauche ich Erfahrung, um SQL Interview Prep zu starten?

Keine Vorkenntnisse erforderlich. SQL Interview Prep auf CoddyKit ist für Anfänger bis fortgeschrittene Lernende strukturiert, sodass du hier starten oder von Anfang an beginnen und in deinem eigenen Tempo voranschreiten kannst. Dies ist Lektion 2 von 4.

Wie lange dauert die Lektion „Der Trick mit der Differenz von Zeilennummern“?

Die meisten CoddyKit-Lektionen dauern etwa 5–10 Minuten. Jede ist kompakt und interaktiv, sodass du stetig Fortschritte machst und genau dort weitermachst, wo du aufgehört hast – im Web und in der App.

Kann ich in dieser SQL Interview Prep-Lektion Code schreiben und ausführen?

Ja. Jede SQL Interview Prep-Lektion enthält einen integrierten Code-Editor, sodass du echten Code direkt in deinem Browser schreibst und ausführst und sofort KI-Feedback erhältst — ohne lokale Einrichtung erforderlich.

Alle Lektionen in diesem Kurs

Ein Gaps-and-Islands-Problem erkennen
Der Trick mit der Differenz von Zeilennummern
Lücken in einer Sequenz finden
Inseln mit Datums- und Statusänderungen

← Zurück zu SQL Interview Prep