0Pricing
SQL Interview Prep · Lekcja

Obcinanie i grupowanie dat

Grupowanie według tygodnia, miesiąca i kwartału za pomocą DATE_TRUNC i odpowiedników.

Obcinanie i grupowanie dat to bezpłatna lekcja SQL Interview Prep na CoddyKit. To lekcja 2 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej SQL Interview Prep, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs SQL Interview Prep zawiera 4 lekcji w sumie.

Dlaczego pojawiają się pytania o grupowanie dat

„Przychody według tygodni” lub „aktywni użytkownicy według miesięcy” to codzienność podczas rozmów kwalifikacyjnych dla analityków. Sprawdzana umiejętność polega na sprowadzeniu precyzyjnych znaczników czasu do mniej szczegółowego przedziału, aby wiersze można było grupować.

Błąd popełniany przez osoby początkujące polega na wyodrębnieniu samego numeru miesiąca, co łączy ten sam miesiąc z różnych lat. Profesjonalna odpowiedź to obcięcie: przypisanie każdego znacznika czasu do początku jego okresu.

  • Przedziały tygodniowe, miesięczne, kwartalne i roczne
  • DATE_TRUNC i odpowiedniki w innych dialektach
  • Poprawne grupowanie, aby dane na wykresach były prawidłowo ułożone

DATE_TRUNC: podstawowe narzędzie

W PostgreSQL funkcja DATE_TRUNC(unit, ts) zeruje wszystko, co jest bardziej szczegółowe niż podana jednostka. Obcięcie do 'month' zamienia dowolny znacznik czasu z marca na 2024-03-01 00:00:00.

Wynik nadal jest znacznikiem czasu, więc jest sortowany chronologicznie i doskonale nadaje się do grupowania. To najważniejsza funkcja dat używana w raportowaniu.

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

Grupowanie przychodów według miesięcy

To klasyczny przykład. Najpierw należy obciąć znacznik czasu do miesiąca, a następnie pogrupować dane i obliczyć sumę. Ponieważ przedział zawiera rok, styczeń 2023 i styczeń 2024 pozostają rozdzielone.

Sortowanie według obciętej wartości daje przejrzysty szereg czasowy gotowy do przedstawienia na wykresie.

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

EXTRACT a DATE_TRUNC

Osoby prowadzące rozmowy kwalifikacyjne często bezpośrednio sprawdzają tę różnicę. Obie funkcje pobierają informacje o okresie, ale odpowiadają na inne pytania.

  • EXTRACT(MONTH FROM ts) zwraca liczbę 3 dla każdego marca, niezależnie od roku, więc nadaje się do analizy sezonowości.
  • DATE_TRUNC('month', ts) zwraca początek konkretnego miesiąca, zachowując rozróżnienie lat, więc nadaje się do szeregów czasowych.

Jeśli w przypadku miesięcznego wykresu trendu pogrupują Państwo dane według EXTRACT(MONTH ...), lata zostaną po cichu połączone.

-- 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;

Przedziały tygodniowe i pytanie o poniedziałek

Grupowanie tygodniowe kryje subtelność, którą osoby prowadzące rozmowy kwalifikacyjne lubią sprawdzać: kiedy zaczyna się tydzień? Funkcja PostgreSQL DATE_TRUNC('week', ts) zawsze przesuwa znacznik czasu na poniedziałek (tygodnie ISO).

Jeśli firma potrzebuje tygodni zaczynających się w niedzielę, należy zastosować przesunięcie. Popularny sposób polega na przesunięciu daty o jeden dzień wstecz, obcięciu jej, a następnie przesunięciu o jeden dzień do przodu.

-- 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;

Przedziały kwartalne

Raportowanie kwartalne jest częste na stanowiskach związanych z finansami. Funkcja DATE_TRUNC('quarter', ts) przypisuje dowolny znacznik czasu do pierwszego dnia jego kwartału: 1 stycznia, 1 kwietnia, 1 lipca lub 1 października.

Aby zamiast tego oznaczyć kwartał numerem, należy połączyć EXTRACT(QUARTER ...) z rokiem.

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 nie ma DATE_TRUNC

To popularne pytanie dotyczące różnic między dialektami: „MySQL nie ma DATE_TRUNC — jak pogrupować dane według miesięcy?”. Przenośna odpowiedź polega na sformatowaniu daty do wymaganej szczegółowości.

  • DATE_FORMAT(ts, '%Y-%m-01') zwraca początek miesiąca jako tekst lub datę.
  • DATE_FORMAT(ts, '%Y-%m') zwraca sortowalny klucz tekstowy, taki jak 2024-03.

W przypadku tygodni MySQL udostępnia funkcję YEARWEEK() z argumentem trybu określającym początek tygodnia.

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

Grupowanie przedziałów w SQL Server

SQL Server przez długi czas nie udostępniał bezpośredniej funkcji obcinania, dlatego używano funkcji DATEFROMPARTS lub idiomu DATEADD/DATEDIFF. Nowsze wersje (2022+) mają funkcję DATETRUNC.

Klasyczny idiom „policz jednostki od epoki, a następnie dodaj je z powrotem” działa w każdej wersji i warto go znać.

-- 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;

Uzupełnianie luk w szeregu czasowym

Samo obcięcie pomija okresy, w których nie ma żadnych wierszy: miesiąc bez zamówień po prostu się nie pojawi. Osoby prowadzące rozmowy kwalifikacyjne sprawdzają, czy zwrócą Państwo na to uwagę.

Rozwiązaniem jest wygenerowanie pełnej osi okresów i wykonanie na niej operacji LEFT JOIN z danymi. W Postgresie oś okresów można zbudować za pomocą funkcji generate_series.

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;

Głębszy przykład: aktywni użytkownicy w każdym tygodniu

Połączmy grupowanie w przedziały z liczeniem wartości unikatowych. „Aktywni użytkownicy tygodniowo” oznaczają liczbę unikatowych użytkowników w każdym tygodniowym przedziale — to rzeczywiste pytanie z analityki produktu.

Należy obciąć znacznik czasu zdarzenia do tygodnia, a następnie użyć COUNT(DISTINCT user_id). Warto wspomnieć, że do pokazania tygodni bez aktywności można połączyć dane z osią tygodni za pomocą JOIN-a, co jest dodatkowym atutem odpowiedzi.

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

Grupowanie według kolumny z indeksem

Warto wspomnieć o jednym ograniczeniu wydajnościowym: opakowanie kolumny z datą w funkcję DATE_TRUNC wewnątrz klauzuli WHERE może uniemożliwić optymalizatorowi użycie indeksu na tej kolumnie.

W klauzuli GROUP BY jest to w porządku, ale podczas filtrowania należy porównywać surową kolumnę z obliczonymi granicami. Omówiony wcześniej wzorzec przedziału półotwartego ma zastosowanie również tutaj.

-- 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';

Szybkie sprawdzenie

Proszę wybrać właściwe narzędzie do utworzenia miesięcznego wykresu trendu, na którym lata pozostają rozdzielone.

Podsumowanie: obcinanie i grupowanie dat

Najważniejsze informacje:

  • DATE_TRUNC(unit, ts) przypisuje znaczniki czasu do początku okresu i zachowuje rozróżnienie lat, dlatego jest właściwym narzędziem do szeregów czasowych.
  • EXTRACT zwraca samą liczbę, co sprawdza się przy analizie sezonowości, ale łączy lata.
  • W Postgresie tygodnie zaczynają się w poniedziałek; jeśli potrzebują Państwo niedzieli, należy zastosować przesunięcie.
  • MySQL używa DATE_FORMAT; starsze wersje SQL Server korzystają z idiomu DATEADD(DATEDIFF(...)), a wersje 2022+ mają funkcję DATETRUNC.
  • Aby pokazać puste okresy, należy użyć wygenerowanej osi dat + LEFT JOIN, a funkcji DATE_TRUNC nie umieszczać w WHERE, aby zachować możliwość użycia indeksu.

Często zadawane pytania

Czy lekcja „Obcinanie i grupowanie dat” jest bezpłatna?

Tak — pełny tekst „Obcinanie i grupowanie dat” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu SQL Interview Prep, przejdź na CoddyKit PRO. Kurs SQL Interview Prep zawiera 4 lekcji w sumie.

Co nauczysz się w „Obcinanie i grupowanie dat”?

Grupowanie według tygodnia, miesiąca i kwartału za pomocą DATE_TRUNC i odpowiedników. Ćwiczysz SQL Interview Prep z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.

Czy potrzebuję doświadczenia, aby zacząć SQL Interview Prep?

Nie wymagamy żadnego doświadczenia. SQL Interview Prep w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 2 z 4.

Ile czasu zajmuje lekcja „Obcinanie i grupowanie dat”?

Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.

Czy mogę pisać i uruchamiać kod w tej lekcji SQL Interview Prep?

Tak. Każda lekcja SQL Interview Prep zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.

Wszystkie lekcje w tym kursie

  1. Działania na datach i interwały
  2. Obcinanie i grupowanie dat
  3. Analizowanie i formatowanie ciągów znaków
  4. Strefy czasowe i znaczniki czasu
← Powrót do SQL Interview Prep