使用生成時間序列回填數據

我有一張這樣的桌子：

但這僅包含 3 天。我需要一個包含完整日曆日期的表格，一年中的每一天都有一行，其中值是最後一個可用的。

例如，從 2022 年 1 月 1 日到 1 月 14 日的所有記錄都具有值 1.0（範圍為group_name“A”）。

我試過使用LAST_VALUE()，但它不工作。

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;

假設您想要每天一行和組名：

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<>在這裡擺弄

① 如果可用，請使用提供不同組名的“組”表。快點。否則，如果表很大，請考慮模擬索引跳過掃描。看：

• SELECT DISTINCT 在我的 PostgreSQL 表上比預期的要慢

② 對最新值的搜尋不限於給定的時間範圍，除非您在LATERAL子查詢中明確說明。關於LATERAL：

• LATERAL JOIN 和 PostgreSQL 中的子查詢有什麼區別？

您仍然可以value IS NULL找到未找到早期值的位置。

如果incomplete_table很大，則索引將(group_name, date)有助於性能（很多）。甚至可能是一個“覆蓋”索引，添加 column value。看：

• 大表中的慢速索引掃描

非常相似的案例，有更多解釋：

• 當時間序列中的值為空時返回上一個執行總計

引用自：https://dba.stackexchange.com/questions/307695