Forberedelse til SQL-interview · Lektion

Glidende gennemsnit over et forskydeligt vindue

Beregn rullende gennemsnit over N perioder med BETWEEN PRECEDING AND CURRENT ROW

Lektion 3 af 413 trin

Glidende gennemsnit over et forskydeligt vindue er en gratis Forberedelse til SQL-interview-lektion på CoddyKit. Dette er lektion 3 af 4. Du kan læse hele lektionen gratis nedenfor — og derefter øve dig praktisk i browseren med en indbygget kodeeditor og en AI-vejleder, der er tilgængelig døgnet rundt. Den er en del af læringsforløbet i Forberedelse til SQL-interview, og dine fremskridt synkroniseres på tværs af nettet og CoddyKit-appen. Forberedelse til SQL-interview-kurset indeholder 4 lektioner i alt.

Spørgsmålet om glidende gennemsnit

Analytikere bliver konstant stillet dette spørgsmål: "Beregn et 7-dages glidende gennemsnit af omsætningen." Et glidende gennemsnit udjævner støjende daglige data ved at beregne gennemsnittet af hvert punkt sammen med dets nærmeste tidligere og efterfølgende punkter.

Et svar på højt niveau til en jobsamtale er en AVG i et vindue med en eksplicit glidende ramme. Den afgørende færdighed er at vælge rammegrænserne, så vinduet bevæger sig korrekt gennem sorteringen.

Det grundlæggende mønster

Et glidende gennemsnit er AVG(value) OVER (ORDER BY ... ROWS BETWEEN n PRECEDING AND CURRENT ROW). Rammen flytter sig: For hver række dækker den den aktuelle række og de foregående n rækker.

For et 7-dages vindue over daglige rækker skal du gå 6 rækker tilbage og medregne den aktuelle række, så du får 7 rækker i alt. En fejl med én række er den mest almindelige fejl, som interviewere opdager.

SELECT
  sale_date,
  amount,
  AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_sales;

Sådan tæller du vinduets størrelse korrekt

Vinduets størrelse er lig med preceding + 1 (det ekstra 1 er den aktuelle række). Derfor:

3-rækkers vindue: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
7-rækkers vindue: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
30-rækkers vindue: ROWS BETWEEN 29 PRECEDING AND CURRENT ROW

Sig denne beregning højt i en jobsamtale, så panelet kan se, at du arbejder bevidst og ikke gætter.

Centrerede og bagudrettede vinduer

Eksemplet ovenfor er et bagudrettet gennemsnit: Det ser kun bagud, så det er kausalt og sikkert at bruge i prognosedashboards.

Et centreret gennemsnit ser i begge retninger, f.eks. ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING for et centreret vindue på 7 rækker. Centrerede vinduer udjævner mere symmetrisk, men kan ikke beregnes for de nyeste rækker i realtid. Nævn, hvilken type anvendelsen kræver.

SELECT
  sale_date,
  AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
  ) AS centered_avg_7
FROM daily_sales;

Effekten ved vinduets kant

Helt i begyndelsen af dataene findes hele vinduet endnu ikke. For række 1 i et 7-dages bagudrettet gennemsnit er kun 1 række tilgængelig, så AVG beregner kun gennemsnittet af denne ene værdi.

Det betyder, at de tidlige rækker viser et "opstarts-"gennemsnit beregnet over færre rækker. Interviewere spørger ofte, hvordan det skal håndteres. Der er to muligheder: acceptere det delvise vindue eller skjule de tidlige rækker ved at kræve et fuldt antal.

Sådan skjuler du delvise vinduer

Hvis virksomheden ønsker en NULL-værdi, indtil et helt vindue er tilgængeligt, skal du bruge COUNT(*) over den samme ramme og sætte korte vinduer til NULL med et CASE-udtryk.

Det er en gennemarbejdet detalje, som viser, at du har tænkt over korrektheden ved grænserne – noget mange kandidater springer over.

SELECT
  sale_date,
  CASE WHEN COUNT(*) OVER w = 7
       THEN AVG(amount) OVER w
  END AS moving_avg_7d
FROM daily_sales
WINDOW w AS (
  ORDER BY sale_date
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
);

Genbrug af en ramme med WINDOW

Den forrige forespørgsel brugte en navngiven WINDOW-klausul. Når den samme ramme optræder flere gange, holder det forespørgslen DRY og læsbar at definere den én gang som WINDOW w AS (...) og henvise til OVER w.

Postgres, MySQL 8 og SQL Server understøtter navngivne vinduer. Hvis du bruger et i en jobsamtale, signalerer det fortrolighed, der rækker ud over at kopiere og indsætte vinduesfunktioner.

SELECT
  sale_date,
  AVG(amount) OVER w AS avg_7d,
  SUM(amount) OVER w AS sum_7d
FROM daily_sales
WINDOW w AS (
  ORDER BY sale_date
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
);

Faldgruben ved kalenderdage og rækker

En subtil fælde er, at hvis tabellen mangler dage, dækker ROWS 6 PRECEDING de seneste 7 registrerede rækker, hvilket kan strække sig over mere end 7 kalenderdage.

Hvis du vil have et ægte kalenderbaseret 7-dages gennemsnit, der tager højde for huller, skal du bruge RANGE med en intervalforskydning eller først join'e med en komplet datoserie, så hver dag har en række. Det er netop den forskel mellem ROWS og RANGE, som blev fremhævet i den forrige lektion.

SELECT
  sale_date,
  AVG(amount) OVER (
    ORDER BY sale_date
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  ) AS calendar_avg_7d
FROM daily_sales;

Glidende gennemsnit pr. gruppe

Ligesom løbende totaler skal glidende gennemsnit normalt nulstilles pr. entitet. Tilføj PARTITION BY, så hvert produkt eller hver butik får sit eget glidende vindue, der ikke flyder sammen med andre grupper.

Rammen og sorteringen fungerer uafhængigt inden for hver partition, så de første rækker i hver gruppe korrekt starter deres egen opvarmningsperiode.

SELECT
  product_id,
  sale_date,
  AVG(amount) OVER (
    PARTITION BY product_id
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS product_ma_7d
FROM daily_sales;

Udjævning i praksis

Hvorfor gøre det? Den daglige omsætning er ujævn, mens et glidende gennemsnit viser tendensen. Et almindeligt svar fra analytikere er at vise den rå værdi sammen med den udjævnede kurve, så dashboardet viser både signal og støj.

Du kan også sammenligne et kort og et langt glidende gennemsnit (f.eks. 7 dage mod 30 dage) for at opdage momentum – SQL-ækvivalenten til et kryds mellem glidende gennemsnit.

SELECT
  sale_date,
  amount,
  AVG(amount) OVER (ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)  AS ma_7,
  AVG(amount) OVER (ORDER BY sale_date
    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS ma_30
FROM daily_sales;

Tjekliste til jobsamtalen

Hvis du vil svare overbevisende på et spørgsmål om glidende gennemsnit, skal du komme ind på:

AVG OVER (ORDER BY ... ROWS BETWEEN n-1 PRECEDING AND CURRENT ROW).
Vinduets størrelse = antal foregående rækker + 1.
Valget mellem bagudrettet og centreret.
Opvarmning med delvise vinduer, og hvordan det skjules.
ROWS kontra RANGE, når der mangler dage.
PARTITION BY til at nulstille pr. entitet.

Hurtigt tjek

Vælg rammen til et 7-dages bagudrettet glidende gennemsnit over daglige rækker.

Besvar den i den interaktive lektion

Opsummering: Glidende gennemsnit

Et glidende gennemsnit er AVG(value) OVER (ORDER BY ... ROWS BETWEEN n-1 PRECEDING AND CURRENT ROW), hvor vinduets størrelse er antallet af foregående rækker plus én. Vælg et bagudrettet eller centreret vindue, der passer til anvendelsen, håndter opvarmningsrækkerne med et COUNT-tjek, og skift til et RANGE-interval, når huller i kalenderen er vigtige.

Nulstil pr. entitet med PARTITION BY, og genbrug rammer med et navngivet WINDOW. Derefter omdanner vi kumulative summer til procentdele med løbende andel af totalen.

Gratis at komme i gang

Lær SQL med en AI-underviser — gratis

Skriv og kør rigtig kode i din browser, få øjeblikkelig hjælp fra en AI-underviser døgnet rundt, og fortsæt, hvor du slap, på web eller i appen.

Kurser: 30
Lektioner: 120

Start gratis Se hele kurset

Ofte stillede spørgsmål

Er lektionen “Glidende gennemsnit over et forskydeligt vindue” gratis?

Ja — hele teksten til “Glidende gennemsnit over et forskydeligt vindue” kan læses gratis her på nettet. Hvis du vil øve dig interaktivt med en indbygget kodeeditor og en AI-vejleder døgnet rundt og få adgang til resten af Forberedelse til SQL-interview-kurset, skal du opgradere til CoddyKit PRO. Forberedelse til SQL-interview-kurset indeholder 4 lektioner i alt.

Hvad lærer jeg i “Glidende gennemsnit over et forskydeligt vindue”?

Beregn rullende gennemsnit over N perioder med BETWEEN PRECEDING AND CURRENT ROW Du øver dig i Forberedelse til SQL-interview med praktisk kode, som du kører direkte i browseren, og en AI-vejleder døgnet rundt besvarer dine spørgsmål, mens du arbejder dig gennem lektionen.

Skal jeg have erfaring for at begynde på Forberedelse til SQL-interview?

Der kræves ingen tidligere erfaring. Forberedelse til SQL-interview på CoddyKit er tilrettelagt for både begyndere og øvede, så du kan starte her eller fra begyndelsen og lære i dit eget tempo. Dette er lektion 3 af 4.

Hvor lang tid tager lektionen “Glidende gennemsnit over et forskydeligt vindue”?

De fleste CoddyKit-lektioner tager cirka 5–10 minutter. Hver lektion er kort og interaktiv, så du gør løbende fremskridt og kan fortsætte, hvor du slap – på både web og app.

Kan jeg skrive og køre kode i denne Forberedelse til SQL-interview-lektion?

Ja. Alle Forberedelse til SQL-interview-lektioner har en indbygget kodeeditor, så du kan skrive og køre rigtig kode direkte i din browser og få øjeblikkelig feedback fra AI – uden lokal opsætning.

Alle lektioner i dette kursus

Kumulative summer med vinduesrammer
ROWS kontra RANGE som ramme
Glidende gennemsnit over et forskydeligt vindue
Kumulativ fordeling og andel af totalen

← Tilbage til Forberedelse til SQL-interview