主页 / dba / 问题 / 307695
Accepted
sparkle
Asked: 2022-02-19 07:23:50 +0800 CST

使用生成时间序列回填数据

  • 772

我有一张这样的桌子：

日期 团队名字 价值
2022-01-01 一个 1.0
2022-01-15 一个 0.5
2022-01-31 一个 0.2

但这仅包含 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;
postgresql greatest-n-per-group

1 个回答

  1. Best Answer

    假设您想要每天一行和组名：

    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<>在这里摆弄

    ① 如果可用，请使用提供不同组名的“组”表。快点。否则，如果表很大，请考虑模拟索引跳过扫描。看：

    ② 对最新值的搜索不限于给定的时间范围，除非您在LATERAL子查询中明确说明。关于LATERAL：

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

    如果incomplete_table很大，则索引将(group_name, date)有助于性能（很多）。甚至可能是一个“覆盖”索引，添加 column value。看：

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

    • 3

相关问题