Forberedelse til SQL-intervju · leksjon

Trikset med forskjellen mellom radnumre

Trekk ROW_NUMBER fra en sekvens for å gruppere fortløpende verdier i islands.

Leksjon 2 av 413 trinn

Trikset med forskjellen mellom radnumre er en gratis leksjon i Forberedelse til SQL-intervju på CoddyKit. Dette er leksjon 2 av 4. Du kan lese hele leksjonen gratis nedenfor – og deretter øve praktisk i nettleseren med en innebygd kodeeditor og en AI-veileder som er tilgjengelig døgnet rundt. Den er en del av læringsløpet i Forberedelse til SQL-intervju, og fremdriften din synkroniseres mellom nettet og CoddyKit-appen. Kurset i Forberedelse til SQL-intervju inneholder totalt 4 leksjoner.

Den mest elegante island-nøkkelen

Trikset med differansen mellom radnumre er teknikken intervjuere helst vil se for islands med sammenhengende heltall eller datoer. Det produserer gruppenøkkelen med én subtraksjon, uten LAG og uten løpende sum.

Hele ideen er å trekke en ROW_NUMBER fra selve verdien. For enhver sekvens med sammenhengende verdier øker både verdien og radnummeret nøyaktig med 1 for hvert trinn, så differansen er konstant gjennom hele sekvensen. Denne konstanten er island-nøkkelen.

Hvorfor differansen forblir konstant

Se på to tilstøtende rader i en sammenhengende sekvens. Når du går fra den ene til den neste, øker verdien med 1, og radnummeret øker med 1. Trekker du dem fra hverandre, opphever +1-ene hverandre, slik at value - row_number ikke endres.

Men idet det oppstår et gap, hopper verdien med mer enn 1, mens radnummeret fortsatt bare øker med 1. Differansen forskyves til en ny konstant. Det skiftet skiller nøyaktig én island fra den neste.

Se det på dataene våre

Husk innloggingsdagene 1, 2, 3, 7, 8, 10. La oss sette radnummeret og differansen ved siden av hverandre:

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

Differansene (0,0,0,3,3,4) deler radene perfekt inn i de tre islands. Samme differanse betyr samme island.

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;

Slå sammen til islands

Med differansen som gruppenøkkel er den avsluttende spørringen den vanlige sammenslåingen. Pakk differansen inn i en CTE og bruk GROUP BY på den:

Dette gir de samme tre islands som tidligere, men SQL-en er kortere og tydeligere enn versjonen med LAG og løpende sum. For sekvenser med heltall eller jevne trinn er dette svaret du bør velge først.

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;

Fallgruven: Verdiene må øke med én

Det enkle differansetrikset forutsetter at sekvensen øker med nøyaktig 1 for hvert trinn. Det stemmer for tette heltallssekvenser og sammenhengende kalenderdager, men fungerer ikke hvis verdiene øker med et annet fast trinn eller hvis det finnes duplikater.

Selv partallene 2,4,6,8 vil se ut som hull når du trekker radnummeret fra verdien.
Duplikatverdier forskyver samsvaret fordi radnummeret fortsetter å øke mens verdien ikke gjør det.

Det som skiller et innlært triks fra reell forståelse, er å kjenne denne begrensningen og vite hvordan den kan håndteres.

Korrigering av sekvenser med faste trinn

Hvis verdiene øker med en kjent konstant k i stedet for 1, normaliserer du dem først: del verdien på k (eller bruk value / k for heltall), slik at hvert trinn blir 1 igjen, og trekk deretter fra radnummeret.

For partall som øker med 2, kan du for eksempel bruke day_no / 2 - ROW_NUMBER(). Den normaliserte verdien øker nå med 1 for hvert påfølgende element, slik at egenskapen med konstant differanse gjenopprettes.

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

Bruk av dette på datoer

Datoer er den vanligste praktiske varianten. Kalenderdatoer kan ikke trekkes direkte fra et radnummer, så konverter datoen til et dagtall først. I Postgres trekker du fra en fast forankringsdato for å få et heltall som angir antall dager, og bruker deretter samme triks.

Fordi påfølgende kalenderdager skiller seg med 1, blir differansen mellom dagtallet og radnummeret igjen konstant innenfor en sammenhengende sekvens.

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;

Beregning av datoforskjeller på tvers av SQL-dialekter

Steget fra dato til heltall varierer mellom databasemotorer, og intervjuere setter pris på at du kjenner flere dialekter:

Postgres: trekk fra en datoliteral: login_date - DATE '2000-01-01' gir et heltall.
MySQL: bruk DATEDIFF(login_date, '2000-01-01').
SQL Server: bruk DATEDIFF(day, '2000-01-01', login_date).

På noen motorer finnes det en enda smidigere løsning: trekk ROW_NUMBER dager direkte fra datoen ved hjelp av intervallaritmetikk, og bruk deretter GROUP BY på den beregnede forankringsdatoen.

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

Legge til partisjonering per gruppe

For sekvenser per bruker partisjonerer du radnummeret etter gruppek kolonnen. Det er avgjørende at gruppek nøkkelen også inkluderer partisjonskolonnen, fordi to forskjellige brukere tilfeldigvis kan få samme differanseverdi.

Bruk derfor GROUP BY på både user_id og den beregnede differansen. Hvis du glemmer user_id i den endelige GROUP BY, får du en subtil feil som intervjuere gjerne bruker for å teste deg.

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;

Trikset eller LAG: Hva bør du bruke

Du har nå to solide teknikker i verktøykassen. Velg bevisst:

Forskjell mellom radnummer og verdi: kortest og ryddigst for sekvenser med jevnt trinn (tette heltallssekvenser og sammenhengende datoer). Førstevalg når naboskap betyr «skiller seg med en konstant».
LAG pluss løpende sum: mer fleksibelt når naboskap ikke innebærer et fast numerisk trinn, for eksempel «samme status som forrige rad» eller uregelmessige spesialregler.

Fortell hva du velger og hvorfor i intervjuet; begrunnelsen imponerer mer enn syntaksen.

Håndtere duplikater på en robust måte

Hvis en verdi kan gjentas og du fortsatt vil ha én sammenhengende sekvens per påfølgende kjøring, fjerner du først duplikater med DISTINCT eller et grupperingssteg, slik at radnummeret samsvarer én til én med verdiene. Alternativt kan du bruke DENSE_RANK i stedet for ROW_NUMBER, slik at like verdier får samme rangering.

Spør alltid intervjueren om duplikater kan forekomme. Den riktige håndteringen avhenger av om duplikater skal forlenge eller ignoreres i en kjøring.

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;

Kort kontroll

Forsikre deg om at du forstår hvorfor trikset fungerer.

Besvar den i den interaktive leksjonen

Oppsummering: Differansetrikset

Du behersker nå den ryddigste nøkkelen for sammenhengende sekvenser:

Nøkkelformel: value - ROW_NUMBER() OVER (ORDER BY value) er konstant for hver sammenhengende kjøring.
Bruk GROUP BY på differansen for å finne start, slutt og lengde.
For sekvenser med faste trinn må du først normalisere (dele på trinnet).
For datoer konverterer du til et heltall som angir antall dager, ved hjelp av dialektens funksjon for datoforskjell.
Per gruppe: bruk PARTITION BY på radnummeret, og ta med gruppek kolonnen i den endelige GROUP BY.
Beskytt deg mot duplikater med DISTINCT eller DENSE_RANK.

Nå flytter vi fokuset fra sammenhengende sekvenser til de tomme mellomrommene: å finne hull.

Gratis å komme i gang

Lær deg SQL med en AI-veileder – gratis

Skriv og kjør ekte kode i nettleseren, få umiddelbar hjelp fra en AI-veileder som er tilgjengelig døgnet rundt, og fortsett der du slapp – på nettet eller i appen.

Kurs: 30
Leksjoner: 120

Start læringen gratis Se hele kurset

Ofte stilte spørsmål

Er leksjonen «Trikset med forskjellen mellom radnumre» gratis?

Ja – hele teksten i «Trikset med forskjellen mellom radnumre» er gratis å lese her på nettet. For å øve interaktivt med en innebygd kodeeditor og en AI-veileder som er tilgjengelig døgnet rundt, og for å låse opp resten av Forberedelse til SQL-intervju-kurset, kan du oppgradere til CoddyKit PRO. Kurset i Forberedelse til SQL-intervju inneholder totalt 4 leksjoner.

Hva lærer jeg i «Trikset med forskjellen mellom radnumre»?

Trekk ROW_NUMBER fra en sekvens for å gruppere fortløpende verdier i islands. Du øver på Forberedelse til SQL-intervju med praktisk kode som du kjører direkte i nettleseren, mens en AI-veileder som er tilgjengelig døgnet rundt, svarer på spørsmålene dine mens du jobber deg gjennom leksjonen.

Trenger jeg erfaring for å begynne med Forberedelse til SQL-intervju?

Ingen tidligere erfaring er nødvendig. Forberedelse til SQL-intervju på CoddyKit er lagt opp for både nybegynnere og viderekomne, så De kan begynne her eller helt fra start og lære i Deres eget tempo. Dette er leksjon 2 av 4.

Hvor lang tid tar leksjonen «Trikset med forskjellen mellom radnumre»?

De fleste CoddyKit-leksjoner tar omtrent 5–10 minutter. Hver leksjon er kort og interaktiv, slik at De gjør jevne fremskritt og kan fortsette akkurat der De slapp – både på nettet og i appen.

Kan jeg skrive og kjøre kode i denne Forberedelse til SQL-intervju-leksjonen?

Ja. Alle Forberedelse til SQL-intervju-leksjoner har en innebygd kodeeditor, slik at De kan skrive og kjøre ekte kode direkte i nettleseren og få umiddelbar tilbakemelding fra AI – uten lokal konfigurering.

Alle leksjonene i dette kurset

← Tilbake til Forberedelse til SQL-intervju