Una de las grandes ventajas de dbt es que existen múltiples paquetes con soluciones a problemas comunes, mismos que puedes aprovechar en tus propios proyectos. En el paquete de dbt-utils existen distintas macros que te pueden ayudar con el día día en la resolución de problemas comunes en SQL. Una de las macros es date_spine, misma que genera una columna con una lista de fechas consecutivas en un período determinado. Por ejemplo, si quisieras generar la serie de fechas de todo 2020, la macro quedaría así:
{{ dbt_utils.date_spine(
datepart="day",
start_date="cast('2020-01-01' as date)",
end_date="cast('2021-01-01' as date)"
)
}}De acuerdo a la documentación, el argumento que pones como “end_date” no está incluido en la lista de fechas. De hecho, “end_date” es el día posterior. Usando esa macro, obtienes esta lista:
Funcionamiento de la macro datespine
La lógica para esta macro está disponible aquí: https://github.com/dbt-labs/dbt-utils/blob/main/macros/sql/date_spine.sql. Traducido para este ejemplo en particular queda así:
with
rawdata as (
with
p as (
select
0 as generated_number
union all
select
1
),
unioned as (
select
p0.generated_number * power(2, 0)
+
p1.generated_number * power(2, 1)
+
p2.generated_number * power(2, 2)
+
p3.generated_number * power(2, 3)
+
p4.generated_number * power(2, 4)
+
p5.generated_number * power(2, 5)
+
p6.generated_number * power(2, 6)
+
p7.generated_number * power(2, 7)
+
p8.generated_number * power(2, 8)
+ 1
as generated_number
from p as p0
cross join
p as p1
cross join
p as p2
cross join
p as p3
cross join
p as p4
cross join
p as p5
cross join
p as p6
cross join
p as p7
cross join
p as p8
)
select *
from unioned
where
generated_number <= 365
order by generated_number
),
all_periods as (
select
(
datetime_add(
cast(cast('2020-01-01' as date) as datetime),
interval row_number() over (order by 1) - 1 day
)
) as date_day
from rawdata
),
filtered as (
select *
from all_periods
where
date_day <= cast('2021-01-01' as date)
)
select *
from filtered
Y funciona de la siguiente manera.
Crear punto de partida “p”
La primera parte del query genera un punto de partida, que en el query está designada como “p”. Está lógica, de hecho está generada por otra macro “generate_series” dentro del mismo paquete de dbt (ver referencia).
with
rawdata as (
with
p as (
select
0 as generated_number
union all
select
1
)
SELECT * FROM p
)
select *
from rawdata
¿Por qué un 0 y un 1? Para crear una plantilla que nos permita contar en binario. El siguiente paso es cruzar esta plantilla consigo misma para generar un “tablero” que nos permita hacer los conteos en binario:
with
rawdata as (
with
p as (
select
0 as generated_number
union all
select
1
),
unioned as (
select *
from p as p0
cross join
p as p1
cross join
p as p2
cross join
p as p3
cross join
p as p4
cross join
p as p5
cross join
p as p6
cross join
p as p7
cross join
p as p8
)
select *
from unioned
)
select *
from rawdataEl tablero se ve así en una hoja de cálculo: https://docs.google.com/spreadsheets/d/1W5-GW5MOhGJraaYJ4X74-5P0LHAJxeuP-_1P6FlzuSs/edit#gid=176921942&range=A1.Visto de manera simplificada, si solo se cruzara el query consigo mismo una vez, quedaría así
with
rawdata as (
with
p as (
select
0 as generated_number
union all
select
1
),
unioned as (
select *
from p as p0
cross join
p as p1
)
select *
from unioned
)
select *
from rawdata
Con este cruce podríamos contar hasta desde 0 hasta 3.
Cruzando el query 8 veces (como ocurre en el query que genera la plantilla), podemos llegar hasta el 511:
511 = 20 + 21 + 22 + 23 + 24 + 25 + 26 + 27 + 28
Esto es visible en el siguiente paso del query:
with
rawdata as (
with
p as (
select
0 as generated_number
union all
select
1
),
unioned as (
select
p0.generated_number * power(2, 0)
+
p1.generated_number * power(2, 1)
+
p2.generated_number * power(2, 2)
+
p3.generated_number * power(2, 3)
+
p4.generated_number * power(2, 4)
+
p5.generated_number * power(2, 5)
+
p6.generated_number * power(2, 6)
+
p7.generated_number * power(2, 7)
+
p8.generated_number * power(2, 8)
+ 1
as generated_number
from p as p0
cross join
p as p1
cross join
p as p2
cross join
p as p3
cross join
p as p4
cross join
p as p5
cross join
p as p6
cross join
p as p7
cross join
p as p8
)
select *
from unioned
where
generated_number <= 365
order by generated_number
)
select *
from rawdataEsta operación es la misma que en esta hoja de cálculo: https://docs.google.com/spreadsheets/d/1W5-GW5MOhGJraaYJ4X74-5P0LHAJxeuP-_1P6FlzuSs/edit#gid=1546948972&range=T4. Y aplicando el filtro (“where”) y ordenando, se obtiene una lista del 1 al 365. Cuando el rango es extendido, la macro para generar la serie extiende esta lista para admitir fechas más allá del mismo año.
Con esta lista como base (rawdata), se calculan las fechas (partiendo de la fecha inicial), y se limita por la fecha final:
...
all_periods as (
select
(
datetime_add(
cast(cast('2019-01-01' as date) as datetime),
interval row_number() over (order by 1) - 1 day
)
) as date_day
from rawdata
),
filtered as (
select *
from all_periods
where
date_day <= cast('2021-01-01' as date)
)
select *
from filteredY así obtienes el producto final: