generate_series produz um conjunto de linhas de um inicio ate um fim com um passo. E a forma mais direta de obter um eixo continuo: uma sequencia de inteiros de 1 a 100, um calendario dia a dia ou uma grade de timestamps a qual depois colar agregados e enxergar tambem os periodos vazios.
Faixas de inteiros
Na forma mais simples a funcao recebe um inicio, um fim e um passo opcional. Ambos os limites sao inclusivos.
SELECT n FROM generate_series(1, 5) AS n;
SELECT n FROM generate_series(0, 100, 10) AS n;
Propriedades uteis:
- O passo pode ser negativo:
generate_series(10, 1, -1) conta para tras.
- Um passo fracionario funciona com
numeric: generate_series(0, 1, 0.25).
- E uma funcao de tabela, entao voce pode coloca-la no
FROM e nomear sua coluna com AS t(col).
Um uso classico e uma grade numerada, por exemplo numeros de linha para um relatorio ou uma lista fixa de IDs contra a qual fazer LEFT JOIN.
Calendarios de datas e timestamps
A mesma sintaxe serve para datas quando voce passa o passo como um interval. Esta e a receita canonica do calendario.
SELECT d::date AS day
FROM generate_series(
'2024-01-01'::date,
'2024-01-15'::date,
'1 day'
) AS d;
O passo e qualquer intervalo: '1 hour', '15 minutes', '1 month'. Uma grade por horas de um dia:
SELECT ts
FROM generate_series(
'2024-01-01 00:00'::timestamp,
'2024-01-01 23:00'::timestamp,
'1 hour'
) AS ts;
Lembre-se: os limites sao inclusivos nas duas pontas. Por isso de 1 a 15 de janeiro saem 15 linhas, nao 14. Se voce quer um eixo semiaberto [inicio, fim), subtraia um passo do limite superior.
O caso de uso principal. Um GROUP BY simples sobre orders so retorna os dias que de fato tiveram pedidos. Dias sem vendas simplesmente desaparecem e o grafico quebra. A cura e gerar um calendario e fazer um LEFT JOIN contra ele.
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;
Agora voce obtem 31 linhas honestas, e os dias sem pedidos relatam 0 em vez de sumir. Duas coisas importam: o calendario fica a esquerda do LEFT JOIN, e o COALESCE transforma o NULL do SUM em zero.
Intervalos de passo e series por linha
generate_series tambem brilha dentro de um join LATERAL: para cada linha da tabela voce pode expandir a propria serie. Digamos que voce queira dividir o onboarding de cada usuario em 3 pontos de controle apos o cadastro.
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 produz agora tres linhas: aos 7, 14 e 21 dias. O mesmo truque constroi faixas horarias, calendarios de parcelas ou degraus de preco.
Pegadinha: se o passo em generate_series(a, b, step) aponta para o lado errado (por exemplo b < a com passo positivo), a funcao retorna zero linhas, nao um erro. A consulta entrega em silencio um resultado vazio, entao confira o sinal do passo e a ordem dos limites.
Uma alternativa para MySQL
O MySQL nao tem generate_series. O MariaDB tambem nao, apesar do parentesco: em vez da funcao ele traz o mecanismo de armazenamento Sequence, que serve uma serie por meio de tabelas virtuais com nomes como seq_1_to_100 ou seq_0_to_100_step_10. No MySQL comum, a forma portavel de construir uma sequencia de inteiros e um 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 um calendario, some dias dentro da recursao:
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 padrao a recursao tem limite de 1000 iteracoes (cte_max_recursion_depth), entao faixas longas precisam aumenta-lo. O ClickHouse e mais simples: SELECT arrayJoin(range(1, 6)) ou a funcao numbers(100). A ideia e a mesma em todo lugar: materializar o eixo de antemao para que periodos vazios nunca se percam.
generate_seriesproduz um conjunto de linhas de um inicio ate um fim com um passo. E a forma mais direta de obter um eixo continuo: uma sequencia de inteiros de 1 a 100, um calendario dia a dia ou uma grade de timestamps a qual depois colar agregados e enxergar tambem os periodos vazios.Faixas de inteiros
Na forma mais simples a funcao recebe um inicio, um fim e um passo opcional. Ambos os limites sao 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, ... 100Propriedades uteis:
generate_series(10, 1, -1)conta para tras.numeric:generate_series(0, 1, 0.25).FROMe nomear sua coluna comAS t(col).Um uso classico e uma grade numerada, por exemplo numeros de linha para um relatorio ou uma lista fixa de IDs contra a qual fazer
LEFT JOIN.Calendarios de datas e timestamps
A mesma sintaxe serve para datas quando voce passa o passo como um
interval. Esta e a receita canonica do 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 linhas, ambos os limites incluidos)O passo e qualquer intervalo:
'1 hour','15 minutes','1 month'. Uma grade por horas de um dia:SELECT ts FROM generate_series( '2024-01-01 00:00'::timestamp, '2024-01-01 23:00'::timestamp, '1 hour' ) AS ts;Lembre-se: os limites sao inclusivos nas duas pontas. Por isso de 1 a 15 de janeiro saem 15 linhas, nao 14. Se voce quer um eixo semiaberto
[inicio, fim), subtraia um passo do limite superior.Preenchendo lacunas com zeros
O caso de uso principal. Um
GROUP BYsimples sobreordersso retorna os dias que de fato tiveram pedidos. Dias sem vendas simplesmente desaparecem e o grafico quebra. A cura e gerar um calendario e fazer umLEFT JOINcontra ele.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;Agora voce obtem 31 linhas honestas, e os dias sem pedidos relatam
0em vez de sumir. Duas coisas importam: o calendario fica a esquerda doLEFT JOIN, e oCOALESCEtransforma oNULLdoSUMem zero.Intervalos de passo e series por linha
generate_seriestambem brilha dentro de um joinLATERAL: para cada linha da tabela voce pode expandir a propria serie. Digamos que voce queira dividir o onboarding de cada usuario em 3 pontos de controle apos o cadastro.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 produz agora tres linhas: aos 7, 14 e 21 dias. O mesmo truque constroi faixas horarias, calendarios de parcelas ou degraus de preco.
Pegadinha: se o passo em
generate_series(a, b, step)aponta para o lado errado (por exemplob < acom passo positivo), a funcao retorna zero linhas, nao um erro. A consulta entrega em silencio um resultado vazio, entao confira o sinal do passo e a ordem dos limites.Uma alternativa para MySQL
O MySQL nao tem
generate_series. O MariaDB tambem nao, apesar do parentesco: em vez da funcao ele traz o mecanismo de armazenamento Sequence, que serve uma serie por meio de tabelas virtuais com nomes comoseq_1_to_100ouseq_0_to_100_step_10. No MySQL comum, a forma portavel de construir uma sequencia de inteiros e um 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 um calendario, some dias dentro da recursao:
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 padrao a recursao tem limite de 1000 iteracoes (
cte_max_recursion_depth), entao faixas longas precisam aumenta-lo. O ClickHouse e mais simples:SELECT arrayJoin(range(1, 6))ou a funcaonumbers(100). A ideia e a mesma em todo lugar: materializar o eixo de antemao para que periodos vazios nunca se percam.