Forberedelse til SQL-interview · Lektion

Afkortning og gruppering af datoer

Gruppering efter uge, måned og kvartal med DATE_TRUNC og tilsvarende funktioner.

Lektion 2 af 413 trin

Afkortning og gruppering af datoer er en gratis Forberedelse til SQL-interview-lektion på CoddyKit. Dette er lektion 2 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.

Hvorfor datointervaller bliver spurgt om

"Vis omsætning pr. uge" eller "aktive brugere pr. måned" er standardspørgsmål til analytikerinterviews. Den færdighed, der testes, er at samle præcise tidsstempler i et grovere interval, så rækkerne grupperes sammen.

Den fejl, juniorer begår, er kun at udtrække månedsnummeret, hvilket blander den samme måned på tværs af forskellige år. Det professionelle svar er trunkering: kortlæg hvert tidsstempel til begyndelsen af dets periode.

Uge-, måneds-, kvartals- og årsintervaller
DATE_TRUNC og tilsvarende funktioner i andre SQL-dialekter
Korrekt gruppering, så diagrammerne stemmer

DATE_TRUNC: Det centrale værktøj

I PostgreSQL nulstiller DATE_TRUNC(unit, ts) alt, der er finere end den angivne enhed. Hvis du trunkerer til 'month', bliver ethvert tidsstempel i marts til 2024-03-01 00:00:00.

Returværdien er stadig et tidsstempel, så den sorteres kronologisk og grupperes perfekt. Det er den mest nyttige enkeltstående datofunktion til rapportering.

SELECT DATE_TRUNC('month', TIMESTAMP '2024-03-17 14:30:00');
-- 2024-03-01 00:00:00

Gruppering af omsætning pr. måned

Det klassiske gennemarbejdede eksempel. Trunker tidsstemplet til måneden, og gruppér derefter og beregn summen. Fordi intervallet indeholder året, forbliver januar 2023 og januar 2024 adskilt.

Hvis du sorterer efter den trunkerede værdi, får du en ren tidsserie, der er klar til et diagram.

SELECT
  DATE_TRUNC('month', order_ts) AS month,
  SUM(amount)                   AS revenue
FROM orders
GROUP BY 1
ORDER BY 1;

EXTRACT sammenlignet med DATE_TRUNC

Interviewere spørger direkte ind til denne forskel. Begge funktioner udtrækker oplysninger om perioden, men de besvarer forskellige spørgsmål.

EXTRACT(MONTH FROM ts) returnerer tallet 3 for enhver marts måned på tværs af alle år, hvilket er nyttigt til sæsonmønstre.
DATE_TRUNC('month', ts) returnerer begyndelsen på den specifikke måned, så årene holdes adskilt, hvilket er nyttigt til tidsserier.

Hvis du grupperer efter EXTRACT(MONTH ...) til et diagram over den månedlige udvikling, blander du ubemærket årene sammen.

-- Seasonality: which month is busiest on average?
SELECT EXTRACT(MONTH FROM order_ts) AS month_num, COUNT(*)
FROM orders GROUP BY 1 ORDER BY 1;

-- Time series: month-by-month trend (years kept separate)
SELECT DATE_TRUNC('month', order_ts) AS month, COUNT(*)
FROM orders GROUP BY 1 ORDER BY 1;

Ugeintervaller og spørgsmålet om mandag

Ugentlig gruppering skjuler en detalje, som interviewere gerne spørger ind til: Hvornår begynder ugen? PostgreSQLs DATE_TRUNC('week', ts) afrunder altid ned til mandag (ISO-uger).

Hvis virksomheden ønsker uger, der begynder søndag, skal du forskyde datoen. Et almindeligt trick er at flytte datoen én dag tilbage, truncere den og derefter flytte den én dag frem igen.

-- ISO week (Monday start)
SELECT DATE_TRUNC('week', order_ts) AS iso_week FROM orders;

-- Sunday-start week
SELECT DATE_TRUNC('week', order_ts + INTERVAL '1 day') - INTERVAL '1 day'
  AS sunday_week
FROM orders;

Kvartalsintervaller

Kvartalsrapportering er almindelig i roller med tilknytning til finans. DATE_TRUNC('quarter', ts) kortlægger ethvert tidsstempel til den første dag i dets kvartal: 1. januar, 1. april, 1. juli eller 1. oktober.

Hvis du i stedet vil angive kvartalet som et tal, kan du kombinere EXTRACT(QUARTER ...) med året.

SELECT
  DATE_TRUNC('quarter', order_ts)                  AS quarter_start,
  EXTRACT(YEAR FROM order_ts) || '-Q'
    || EXTRACT(QUARTER FROM order_ts)              AS quarter_label,
  SUM(amount)                                      AS revenue
FROM orders
GROUP BY 1, 2
ORDER BY 1;

MySQL har ingen DATE_TRUNC

Et populært spørgsmål på tværs af SQL-dialekter er: "MySQL har ingen DATE_TRUNC — hvordan grupperer du efter måned?" Det portable svar er at formatere datoen ned til den ønskede granularitet.

DATE_FORMAT(ts, '%Y-%m-01') giver månedens begyndelse som tekst eller dato.
DATE_FORMAT(ts, '%Y-%m') giver en sorterbar tekstnøgle som 2024-03.

Til uger tilbyder MySQL YEARWEEK() med et tilstandsargument, der styrer, hvornår ugen begynder.

-- MySQL month bucket
SELECT DATE_FORMAT(order_ts, '%Y-%m-01') AS month, SUM(amount)
FROM orders
GROUP BY 1
ORDER BY 1;

Intervaller i SQL Server

SQL Server manglede historisk en direkte trunkeringsfunktion, så kandidater brugte DATEFROMPARTS eller DATEADD/DATEDIFF-idiomet. Moderne versioner (2022+) har tilføjet DATETRUNC.

Det klassiske idiom — "tæl enheder siden epoken, og læg dem derefter til igen" — virker i alle versioner og er værd at kende.

-- Portable SQL Server month truncation
SELECT DATEADD(month, DATEDIFF(month, 0, order_ts), 0) AS month_start
FROM orders;

-- SQL Server 2022+
SELECT DATETRUNC(month, order_ts) AS month_start FROM orders;

Udfyldning af huller i en tidsserie

Trunkering alene fjerner perioder uden rækker: En måned uden ordrer vises simpelthen ikke. Interviewere tester, om du lægger mærke til dette.

Løsningen er at generere en komplet række af perioder og udføre en LEFT JOIN af dataene på den. I Postgres bygger generate_series denne række.

SELECT
  cal.month,
  COALESCE(SUM(o.amount), 0) AS revenue
FROM generate_series(DATE '2024-01-01', DATE '2024-12-01',
                      INTERVAL '1 month') AS cal(month)
LEFT JOIN orders o
  ON DATE_TRUNC('month', o.order_ts) = cal.month
GROUP BY cal.month
ORDER BY cal.month;

Dybdegående eksempel: Aktive brugere pr. uge

Kombinér gruppering i intervaller med optælling af unikke værdier. "Ugentligt aktive brugere" betyder unikke brugere pr. ugeinterval, hvilket er et reelt spørgsmål inden for produktanalyse.

Trunker hændelsens tidsstempel til ugen, og brug derefter COUNT(DISTINCT user_id). Hvis du nævner, at du ville forbinde en ugerække for at vise uger uden aktivitet, giver det ekstra point.

SELECT
  DATE_TRUNC('week', event_ts) AS week,
  COUNT(DISTINCT user_id)      AS wau
FROM events
GROUP BY 1
ORDER BY 1;

Gruppering efter en indekseret kolonne

Her er en vigtig performancebemærkning: Hvis du omslutter datokolonnen med DATE_TRUNC i en WHERE-sætning, kan det forhindre forespørgselsplanlæggeren i at bruge et indeks på kolonnen.

Det er fint i GROUP BY, men ved filtrering bør du i stedet sammenligne den rå kolonne med beregnede grænser. Vi gennemgik dette halvlukkede mønster tidligere; det gælder også her.

-- Avoid in WHERE: DATE_TRUNC('month', order_ts) = '2024-03-01'
-- Prefer:
SELECT * FROM orders
WHERE order_ts >= DATE '2024-03-01'
  AND order_ts <  DATE '2024-04-01';

Hurtigt tjek

Vælg det rigtige værktøj til et diagram over den månedlige udvikling, hvor årene holdes adskilt.

Besvar den i den interaktive lektion

Opsummering: Trunkering og gruppering af datoer

Det skal du huske:

DATE_TRUNC(unit, ts) kortlægger tidsstempler til begyndelsen af en periode og holder årene adskilt, så det er det rigtige værktøj til tidsserier.
EXTRACT returnerer et ubearbejdet tal, som er godt til sæsonmønstre, men blander årene sammen.
Uger i Postgres begynder mandag; forskyd datoen, hvis du har brug for søndag.
MySQL bruger DATE_FORMAT; ældre SQL Server bruger DATEADD(DATEDIFF(...))-idiomet; version 2022+ har DATETRUNC.
Brug en genereret datostamme + LEFT JOIN til at vise tomme perioder, og undgå DATE_TRUNC i WHERE for at bevare brugen af indekser.

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 “Afkortning og gruppering af datoer” gratis?

Ja — hele teksten til “Afkortning og gruppering af datoer” 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 “Afkortning og gruppering af datoer”?

Gruppering efter uge, måned og kvartal med DATE_TRUNC og tilsvarende funktioner. 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 2 af 4.

Hvor lang tid tager lektionen “Afkortning og gruppering af datoer”?

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

← Tilbage til Forberedelse til SQL-interview