Problem z brakującymi dniami

Agregując dane na przykład do poziomu dziennego, możesz zgubić część dni. Kiedy może się tak zdarzyć?

Wyobraź sobie, że masz w swojej hurtowni danych tabelę, w której rejestrowane są wszystkie transakcje. Mogłaby wyglądać np. tak:

Zadanie wydaje się proste

Twoim zadaniem jest przedstawienie na wykresie słupkowym dziennej sprzedaży z ostatnich 30 dni. Zadanie wydaje się proste:

1. Podpinamy tabelę do naszego BI (niech to będzie Looker Studio)
2. Dodajemy wykres
3. wymiar: czas transakcji z agregacją dzienną
4. miara: suma wartości sprzedaży
5. Ustawiamy filtr na 30 ostatnich dni
6. Z radością oznaczamy zadanie w Asanie czy tam innym Monday jako zrobione

Nasz wykres mógłby wyglądać np. tak:

Gdzie jest problem?

A za jakiś czas wraca do nas użytkownik, z pytaniem dlaczego na wykresie sprzedaży z ostatnich 30 dni mamy tylko 23 słupki?

Hmm... W biznesie mógł być jakiś słabszy okres i mogły wystąpić dni bez żadnych sprzedaży. A może pojawiła się jakaś awaria? To wszystko mogło się zdarzyć, ale mało spostrzegawczy użytkownik (kto liczy słupki na wykresie?) może się tego z przygotowanego przez nas wykresu nie dowiedzieć.

Schemat tabeli źródłowej sprawia, że takie „puste" dni w ogóle nie będą pokazane na wykresie.

Na szczęście w BigQuery mamy na to gotowe rozwiązanie...

Funkcja GENERATE_DATE_ARRAY()

Z pomocą przychodzi nam funkcja GENERATE_DATE_ARRAY. To, co musisz o niej wiedzieć, to argumenty jakie przyjmuje:

• data początkowa
• data końcowa
• interwał (krok – opcjonalnie)

Zwraca tablicę (ARRAY), której elementami są wygenerowane daty od daty początkowej do daty końcowej.

Podstawowe zastosowanie

SELECT GENERATE_DATE_ARRAY(STRING">'2024-01-01', STRING">'2024-01-10')

Rezultat to tablica dat:

Przykład z interwałem

Wygenerowanie tablicy z odstępem 2 dni:

SELECT GENERATE_DATE_ARRAY(STRING">'2024-01-01', STRING">'2024-01-10', INTERVAL 2 DAY)

Rozpakowywanie tablicy

GENERATE_DATE_ARRAY zwraca tablicę. Żeby rozpakować daty do wierszy możemy wykorzystać funkcję UNNEST:

SELECT *
FROM UNNEST(GENERATE_DATE_ARRAY(STRING">'2024-01-01', STRING">'2024-01-10')) AS dzien

To zapytanie zwróci 10 wierszy, po jednym dla każdego dnia.

Finalne rozwiązanie

OK, ale co to właściwie nam daje? Żeby pokazać na wykresie również te dni, w których nie było żadnych transakcji:

1. Wygenerujemy listę wszystkich dni
2. Poprzez LEFT OUTER JOIN dołączymy do nich dane o transakcjach
3. Zapewniamy, że wszystkie dni z wybranego okresu będą obecne w danych

WITH
dates AS (
  SELECT DATE
  FROM UNNEST(GENERATE_DATE_ARRAY(
    DATE_SUB(CURRENT_DATE, INTERVAL 29 DAY), 
    CURRENT_DATE
  )) AS DATE
),
revenue_daily AS (
  SELECT
    DATE(TIMESTAMP) AS DATE,
    SUM(revenue_usd) AS revenue_usd
  FROM STRING">`pozar-w-danych.sales_data.transactions`
  WHERE
    DATE(TIMESTAMP) BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 29 DAY) 
    AND CURRENT_DATE
  GROUP BY 1
)
SELECT 
  d.DATE, 
  rd.revenue_usd
FROM dates d
LEFT OUTER JOIN revenue_daily rd USING (DATE)

Co tu się właściwie wydarzyło?

1. W pierwszym CTE dates wygenerowaliśmy listę 30 ostatnich dni za pomocą funkcji GENERATE_DATE_ARRAY
2. W drugim CTE revenue_daily zagregowaliśmy dane o transakcjach do poziomu dni
3. Na koniec użyliśmy LEFT OUTER JOIN zaczynając od naszej wygenerowanej listy dni

Bonus: Dynamiczny okres

Możesz zauważyć, że zamiast „hardkodować" okres użyłem funkcji CURRENT_DATE oraz DATE_SUB. Dzięki temu kod będzie generował aktualne ostatnie 30 dni zawsze, gdy zostanie uruchomiony.

Efekt końcowy

Jeśli takiego zapytania użyjemy jako źródła danych w naszym raporcie, na wykresie pojawią się również te dni, w których nie było transakcji.

Teraz raport jest kompletny i użytkownik od razu widzi, które dni były bez sprzedaży - bez potrzeby liczenia słupków! 🎉

Kluczowe korzyści:

• ✅ Wszystkie dni są widoczne na wykresie
• ✅ Łatwo zauważyć dni bez transakcji
• ✅ Dynamiczny zakres dat
• ✅ Czytelny i zrozumiały raport