Início / dba / Perguntas / 307695
Accepted
sparkle
Asked: 2022-02-19 07:23:50 +0800 CST

Preenchimento de dados usando gerar séries temporais

  • 772

Tenho uma tabela assim:

Encontro Nome do grupo valor
01-01-2022 UMA 1,0
15-01-2022 UMA 0,5
31-01-2022 UMA 0,2

mas isso contém apenas 3 dias. Preciso de uma tabela com datas de calendário completo, uma linha para cada dia do ano em que o valor seja o último disponível.
Por exemplo, todos os registros de 1º de janeiro de 2022 a 14 de janeiro têm valor 1,0 (com escopo para group_name'A').

Já tentei usar LAST_VALUE()mas não está funcionando.

WITH dates AS (
    
    SELECT 
        date::date
    FROM generate_series ( '2022-01-01'::timestamp, '2022-01-31'::timestamp, '1 day'::interval) date

), incomplete_table AS (
     SELECT * FROM (VALUES 
        ('2022-01-01'::date, 'a', 1),
        ('2022-01-15'::date, 'a', 0.5), 
        ('2022-01-31'::date, 'a', 0.2), 
        ('2022-01-02'::date, 'b', 0.1),
        ('2022-01-10'::date, 'b', 0.15),
        ('2022-01-20'::date, 'b', 0.15)
    ) AS t (date,group_name, value)
)
SELECT
    dates.date,
    group_name, 
    value,
    LAST_VALUE(value) OVER (ORDER BY dates.date DESC) as last_value_window
FROM dates
LEFT JOIN incomplete_table ON incomplete_table.date = dates.date
ORDER BY dates.date DESC;
postgresql greatest-n-per-group

1 respostas

  1. Best Answer

    Supondo que você queira uma linha por dia e nome do grupo:

    WITH incomplete_table(date, group_name, value) AS (
   VALUES 
     ('2022-01-01'::date, 'a', 1)
   , ('2022-01-15'::date, 'a', 0.5)
   , ('2022-01-31'::date, 'a', 0.2)
   , ('2022-01-02'::date, 'b', 0.1)
   , ('2022-01-10'::date, 'b', 0.15)
   , ('2022-01-20'::date, 'b', 0.15)
   )
SELECT d.date, g.group_name, i.value
FROM  (
   SELECT date::date
   FROM   generate_series (timestamp '2022-01-01'
                         , timestamp '2022-01-31'
                         , interval '1 day') date
   ) d
CROSS  JOIN (SELECT DISTINCT group_name FROM incomplete_table) g  -- ①
LEFT   JOIN LATERAL (
   SELECT i.group_name, i.value
   FROM   incomplete_table i
   WHERE  i.group_name = g.group_name
   AND    i.date <= d.date
-- AND    i.date >= timestamp '2022-01-01'  -- ? ②
   ORDER  BY i.date DESC
   LIMIT  1
   ) i ON true
ORDER  BY g.group_name, d.date DESC;

    db<>fique aqui

    ① Se disponível, use uma tabela "grupos" fornecendo nomes de grupos distintos. Mais rápido. Caso contrário, se a tabela for grande, considere uma varredura de salto de índice emulado. Ver:

    ② A pesquisa pelo valor mais recente não está limitada ao intervalo de tempo fornecido, a menos que você especifique isso na LATERALsubconsulta. Sobre LATERAL:

    Você ainda chega value IS NULLonde nenhum valor anterior é encontrado.

    Se incomplete_tablefor grande, um índice em (group_name, date)ajudará o desempenho (muito). Possivelmente até mesmo um índice de "cobertura", adicionando column value. Ver:

    Caso muito semelhante com mais explicações:

    • 3

relate perguntas