postgresql Postgres如何在日历周内转发和回填值

x3naxklr 于 2023-06-29 发布在 PostgreSQL

关注(0)|答案(2)|浏览(105)

我有下表：

CREATE TABLE IF NOT EXISTS test (
                                    date DATE,
                                    value INT
);

INSERT INTO test VALUES
                     ( '2022-01-11', NULL),  -- Tues
                     ( '2022-01-10', 5),  -- Mon
                     ( '2022-01-09', NULL),  -- Sun
                     ( '2022-01-08', 6),  -- Sat
                     ( '2022-01-07', NULL), -- Fri
                     ( '2022-01-06', NULL), -- Thur
                     ( '2022-01-05', 5), -- Wed
                     ( '2022-01-04', NULL), -- Tues
                     ( '2022-01-03', NULL), --Mon
                     ( '2022-01-02', NULL), -- Sun
                     ( '2022-01-01', NULL); -- Sat

对于日历周（星期一到星期日），什么是转发和回填值列的查询
对于日历周，应将空值向前填充到下一个非空值，并将空值回填到上一个非空值。如果日历周的值全部为空，则该周应为空
结果应该如下所示：
| 价值观| VALUES |
| - -----| ------------ |
| 5 -周二，从周一开始填补| 5 -- Tues, filled from Monday |
| 5 -星期一| 5 -- Mon |
| 6 -从星期六开始充满阳光| 6 -- Sun Filled from saturday |
| 6 -周六| 6 -- Sat |
| 5 --从星期三开始填充星期五| 5 -- Fri Filled from Wed |
| 5 --星期四从星期三开始填写| 5 -- Thur Filled from Wed |
| 5 --赌| 5 -- Wed |
| 5 -星期二从星期三开始| 5 -- Tues Filled from Wed |
| 5 -星期一从星期三开始填写| 5 --Mon Filled from Wed |
| 空--周日| Null -- Sun |
| Null -- Sat周中没有值，因此为null| Null -- Sat No values in week, therefor null |

postgresql

来源：https://stackoverflow.com/questions/76533937/postgres-how-to-forward-and-backfill-values-in-a-calendar-week

2条答案

按热度按时间

qlzsbp2j1#

这对于last_value和ignore nulls选项来说是一个很好的选择，但不幸的是Postgres不支持后者。
下面是一种在Postgres中使用窗口函数的方法：

select date, value,
    max(value) over(partition by date_trunc('week', date), grp) new_value
from (
    select t.*,
        count(value) over(partition by date_trunc('week', date) order by date) grp
    from test t
) t
order by date desc

方法是使用窗口count构建同一周的连续行组，这些行包含一个非空值，可能后跟空值，然后引入最近的前一个非空值。您可以单独运行子查询以查看组是如何生成的。
如果您还希望查询在同一周内没有前一个值可用时查找 next 值，我们可以扩展逻辑并为每行定义两个组，然后尝试两者：

select date, value,
    coalesce(
        max(value) over(partition by date_trunc('week', date), grp1),
        max(value) over(partition by date_trunc('week', date), grp2)
    ) new_value
from (
    select t.*,
        count(value) over(partition by date_trunc('week', date) order by date     ) grp1,
        count(value) over(partition by date_trunc('week', date) order by date desc) grp2
    from test t
) t
order by date desc

Demo on DB Fiddle

赞(0）回复(0）举报 2023-06-29

lymgl2op2#

此查询查找表中每个日期在同一周内（如果在同一周内没有非空值，则在下一周内）的下一个或上一个非空值，并使用它来填充任何空值。
查询首先按周划分数据，然后在每个组中构造一个窗口框架以查找下一个或上一个非空值，从而构建组。

SELECT date,
       value,
       COALESCE(value,
-- prior non-NULL value
                (ARRAY_AGG(value)
                 FILTER (WHERE value IS NOT NULL)
                     OVER (PARTITION BY DATE_TRUNC('week', date)
                     ORDER BY date DESC
                     ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING))[1],
-- next non-NULL value
                (ARRAY_AGG(value) FILTER (WHERE value IS NOT NULL) OVER
                    (PARTITION BY DATE_TRUNC('week', date)
                    ORDER BY date ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING))[1]
           ) AS substitute_level
FROM test
ORDER BY date DESC;

db fiddle上的演示

赞(0）回复(0）举报 2023-06-29

我来回答

postgresql Postgres如何在日历周内转发和回填值

2条答案

相关问题

热门标签

最新问答