generate_series produce un conjunto de filas desde un inicio hasta un fin con un paso. Es la forma mas directa de obtener un eje continuo: una serie de enteros de 1 a 100, un calendario dia a dia o una rejilla de timestamps a la que luego pegar agregados y ver tambien los periodos vacios.
Rangos de enteros
En su forma mas simple la funcion toma un inicio, un fin y un paso opcional. Ambos limites son inclusivos.
SELECT n FROM generate_series(1, 5) AS n;
SELECT n FROM generate_series(0, 100, 10) AS n;
Propiedades utiles:
- El paso puede ser negativo:
generate_series(10, 1, -1) cuenta hacia atras.
- Un paso fraccionario funciona con
numeric: generate_series(0, 1, 0.25).
- Es una funcion de tabla, asi que puedes ponerla en
FROM y nombrar su columna con AS t(col).
Un uso clasico es una rejilla numerada, por ejemplo numeros de fila para un informe o una lista fija de IDs contra la que hacer LEFT JOIN.
Calendarios de fechas y timestamps
La misma sintaxis sirve para fechas si pasas el paso como un interval. Esta es la receta canonica del calendario.
SELECT d::date AS day
FROM generate_series(
'2024-01-01'::date,
'2024-01-15'::date,
'1 day'
) AS d;
El paso es cualquier intervalo: '1 hour', '15 minutes', '1 month'. Una rejilla por horas de un dia:
SELECT ts
FROM generate_series(
'2024-01-01 00:00'::timestamp,
'2024-01-01 23:00'::timestamp,
'1 hour'
) AS ts;
Recuerda: los limites son inclusivos en ambos extremos. Por eso del 1 al 15 de enero salen 15 filas, no 14. Si quieres un eje semiabierto [inicio, fin), resta un paso al limite superior.
Rellenar huecos con ceros
El caso estrella. Un GROUP BY simple sobre orders solo devuelve los dias que de verdad tuvieron pedidos. Los dias sin ventas desaparecen sin mas y el grafico se rompe. La cura es generar un calendario y hacer un LEFT JOIN contra el.
SELECT
cal.day::date AS day,
COALESCE(SUM(o.amount), 0) AS revenue
FROM generate_series(
'2024-01-01'::date,
'2024-01-31'::date,
'1 day'
) AS cal(day)
LEFT JOIN orders o
ON o.created_at >= cal.day
AND o.created_at < cal.day + interval '1 day'
AND o.status = 'paid'
GROUP BY cal.day
ORDER BY cal.day;
Ahora obtienes 31 filas honestas, y los dias sin pedidos informan 0 en lugar de esfumarse. Dos cosas importan: el calendario va a la izquierda del LEFT JOIN, y COALESCE convierte el NULL de SUM en un cero.
Intervalos de paso y series por fila
generate_series tambien brilla dentro de un join LATERAL: para cada fila de la tabla puedes expandir su propia serie. Supon que quieres dividir el onboarding de cada usuario en 3 puntos de control tras el registro.
SELECT
u.id,
u.created_at + (g.n * interval '7 days') AS checkpoint
FROM users u
CROSS JOIN LATERAL generate_series(1, 3) AS g(n);
Cada usuario produce ahora tres filas: a los 7, 14 y 21 dias. El mismo truco construye franjas horarias, calendarios de cuotas o escalones de precio.
Trampa: si el paso en generate_series(a, b, step) apunta al lado equivocado (por ejemplo b < a con un paso positivo), la funcion devuelve cero filas, no un error. La consulta entrega en silencio un resultado vacio, asi que revisa el signo del paso y el orden de los limites.
Una alternativa para MySQL
MySQL no tiene generate_series. MariaDB tampoco, pese al parecido familiar: en lugar de la funcion incluye el motor de almacenamiento Sequence, que sirve una serie mediante tablas virtuales con nombres como seq_1_to_100 o seq_0_to_100_step_10. En MySQL normal, la forma portable de construir una serie de enteros es un CTE recursivo.
WITH RECURSIVE seq AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM seq WHERE n < 31
)
SELECT n FROM seq;
Para un calendario, suma dias dentro de la recursion:
WITH RECURSIVE cal AS (
SELECT DATE '2024-01-01' AS day
UNION ALL
SELECT day + INTERVAL 1 DAY FROM cal WHERE day < DATE '2024-01-31'
)
SELECT day FROM cal;
Por defecto la recursion esta limitada a 1000 iteraciones (cte_max_recursion_depth), asi que los rangos largos necesitan subirlo. ClickHouse es mas simple: SELECT arrayJoin(range(1, 6)) o la funcion numbers(100). La idea es la misma en todas partes: materializar el eje de antemano para que los periodos vacios nunca se pierdan.
generate_seriesproduce un conjunto de filas desde un inicio hasta un fin con un paso. Es la forma mas directa de obtener un eje continuo: una serie de enteros de 1 a 100, un calendario dia a dia o una rejilla de timestamps a la que luego pegar agregados y ver tambien los periodos vacios.Rangos de enteros
En su forma mas simple la funcion toma un inicio, un fin y un paso opcional. Ambos limites son inclusivos.
SELECT n FROM generate_series(1, 5) AS n; -- 1, 2, 3, 4, 5 SELECT n FROM generate_series(0, 100, 10) AS n; -- 0, 10, 20, ... 100Propiedades utiles:
generate_series(10, 1, -1)cuenta hacia atras.numeric:generate_series(0, 1, 0.25).FROMy nombrar su columna conAS t(col).Un uso clasico es una rejilla numerada, por ejemplo numeros de fila para un informe o una lista fija de IDs contra la que hacer
LEFT JOIN.Calendarios de fechas y timestamps
La misma sintaxis sirve para fechas si pasas el paso como un
interval. Esta es la receta canonica del calendario.SELECT d::date AS day FROM generate_series( '2024-01-01'::date, '2024-01-15'::date, '1 day' ) AS d; -- 2024-01-01 ... 2024-01-15 (15 filas, ambos limites incluidos)El paso es cualquier intervalo:
'1 hour','15 minutes','1 month'. Una rejilla por horas de un dia:SELECT ts FROM generate_series( '2024-01-01 00:00'::timestamp, '2024-01-01 23:00'::timestamp, '1 hour' ) AS ts;Recuerda: los limites son inclusivos en ambos extremos. Por eso del 1 al 15 de enero salen 15 filas, no 14. Si quieres un eje semiabierto
[inicio, fin), resta un paso al limite superior.Rellenar huecos con ceros
El caso estrella. Un
GROUP BYsimple sobreorderssolo devuelve los dias que de verdad tuvieron pedidos. Los dias sin ventas desaparecen sin mas y el grafico se rompe. La cura es generar un calendario y hacer unLEFT JOINcontra el.SELECT cal.day::date AS day, COALESCE(SUM(o.amount), 0) AS revenue FROM generate_series( '2024-01-01'::date, '2024-01-31'::date, '1 day' ) AS cal(day) LEFT JOIN orders o ON o.created_at >= cal.day AND o.created_at < cal.day + interval '1 day' AND o.status = 'paid' GROUP BY cal.day ORDER BY cal.day;Ahora obtienes 31 filas honestas, y los dias sin pedidos informan
0en lugar de esfumarse. Dos cosas importan: el calendario va a la izquierda delLEFT JOIN, yCOALESCEconvierte elNULLdeSUMen un cero.Intervalos de paso y series por fila
generate_seriestambien brilla dentro de un joinLATERAL: para cada fila de la tabla puedes expandir su propia serie. Supon que quieres dividir el onboarding de cada usuario en 3 puntos de control tras el registro.SELECT u.id, u.created_at + (g.n * interval '7 days') AS checkpoint FROM users u CROSS JOIN LATERAL generate_series(1, 3) AS g(n);Cada usuario produce ahora tres filas: a los 7, 14 y 21 dias. El mismo truco construye franjas horarias, calendarios de cuotas o escalones de precio.
Trampa: si el paso en
generate_series(a, b, step)apunta al lado equivocado (por ejemplob < acon un paso positivo), la funcion devuelve cero filas, no un error. La consulta entrega en silencio un resultado vacio, asi que revisa el signo del paso y el orden de los limites.Una alternativa para MySQL
MySQL no tiene
generate_series. MariaDB tampoco, pese al parecido familiar: en lugar de la funcion incluye el motor de almacenamiento Sequence, que sirve una serie mediante tablas virtuales con nombres comoseq_1_to_100oseq_0_to_100_step_10. En MySQL normal, la forma portable de construir una serie de enteros es un CTE recursivo.WITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 31 ) SELECT n FROM seq;Para un calendario, suma dias dentro de la recursion:
WITH RECURSIVE cal AS ( SELECT DATE '2024-01-01' AS day UNION ALL SELECT day + INTERVAL 1 DAY FROM cal WHERE day < DATE '2024-01-31' ) SELECT day FROM cal;Por defecto la recursion esta limitada a 1000 iteraciones (
cte_max_recursion_depth), asi que los rangos largos necesitan subirlo. ClickHouse es mas simple:SELECT arrayJoin(range(1, 6))o la funcionnumbers(100). La idea es la misma en todas partes: materializar el eje de antemano para que los periodos vacios nunca se pierdan.