Brakujące dni na wykresie? – chodź, pokażę Ci rozwiązanie.

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:

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
   • wymiar – czas transkacji z agreacją dzienną
   • miara – suma wartości sprzedaży
3. Ustawiamy filtr na 30 ostatnich dni.
4. Z radością oznaczamy zadanie w asanie czy tam innym mondayu jako zrobione.

Nasz wykres może wyglądać np. tak:

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. Jej dokładny opis znajdziesz w dokumentacji. 

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 data początkowej do daty końcowej. Opcjonalnie możesz też podać odstęp(krok) pomiędzy kolejnymi elementami.

Przykłady

Podstawowe zastosowanie

SELECT GENERATE_DATE_ARRAY('2024-01-01', '2024-01-10')

Rezultat:

Przykład wygenerowania tablicy z odstępem 2 dni:

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

Rezultat:

GENERATE DATE ARRAY zwraca tablicę. Żeby rozpakować daty do wierszy możemy wykorzystać funkcję UNNEST. W ten sposób:

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

Rezultat:

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 wygenerujemy listę wszystkich dni, a następnie poprzez LEFT OUTER JOIN dołączymy do nich dane o transakcjach. W ten sposób zapewniamy, że wszystkie dni z wybranego przez nas okresu, będą obecne w danych. Finalne rozwiązanie wygląda tak:

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 `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 o nazwie 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.

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.

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.