postgresql 如何在SQL中将周围的值选择到列的NULL中?

4uqofj5v  于 2023-05-06  发布在  PostgreSQL
关注(0)|答案(1)|浏览(147)

我有一个包含两列的表:日期和数值。我想有这个表“稀释”的方式,它将有一个新的条目之间的每一天的开始和结束日期(基本上是天的列表),并有上述数值设置不仅为它的相应日期,而且周围的新创建的日期以及。我不知道SQL中处理这样的问题的正确方法是什么(最好是PostgreSQL)。
对于一个input:

  • 我有两个约会:开始日期和结束日期
  • 有两列的表格:日期和值

日期:

start: 2023-04-01
 end:   2023-04-10

该表:

date       | value
--------------------
 2023-04-03 | 1000
 2023-04-06 | 500
 2023-04-08 | 2500

步骤1:创建一个结果表,其中包含开始日期和结束日期之间的所有日期的条目。

date
------------
 2023-04-01
 2023-04-02
 2023-04-03
 2023-04-04
 2023-04-05
 2023-04-06
 2023-04-07
 2023-04-08
 2023-04-09
 2023-04-10

步骤2:添加一个列,该列的值已存在于输入表中。

date       | value
--------------------
 2023-04-01 | [NULL]
 2023-04-02 | [NULL]
 2023-04-03 | 1000
 2023-04-04 | [NULL]
 2023-04-05 | [NULL]
 2023-04-06 | 500
 2023-04-07 | [NULL]
 2023-04-08 | 2500
 2023-04-09 | [NULL]
 2023-04-10 | [NULL]

第三步(我正在努力解决的问题):用周围的值填充NULL值。规则是:

  • 如果该值为空,则查找PREVIOUS最接近的非NULL值,并设置它的值
  • 如果该值位于开头,或者所有先前的值都为NULL,则查找NEXT最接近的非NULL值,并设置它的值
date       | value 
--------------------
 2023-04-01 | 1000
 2023-04-02 | 1000
 2023-04-03 | 1000
 2023-04-04 | 1000
 2023-04-05 | 1000
 2023-04-06 | 500
 2023-04-07 | 500
 2023-04-08 | 2500
 2023-04-09 | 2500
 2023-04-10 | 2500
dwbf0jvd

dwbf0jvd1#

下面演示了生成指定结果的方法:

WITH test_values(
  test_date,
  test_value
) AS (
  VALUES ('2023-03-31'::date, 1),
         ('2023-04-03'::date, 1000),
         ('2023-04-06'::date, 500),
         ('2023-04-08'::date, 2500),
         ('2023-04-11'::date, 9999)
),
test_range(start_date, end_date) AS (
    VALUES ('2023-04-01'::date, '2023-04-10'::date)
)
SELECT
  GENERATE_SERIES(CASE WHEN LAG(test_values.test_date) OVER (ORDER BY test_values.test_date) IS NULL THEN test_range.start_date ELSE test_values.test_date END,
                  LEAD(test_values.test_date - INTERVAL '1' DAY, 1, test_range.end_date) OVER (ORDER BY test_values.test_date),
                  INTERVAL '1' DAY) AS test_date,
  test_values.test_value
  FROM
    test_range JOIN test_values ON test_values.test_date BETWEEN test_range.start_date AND test_range.end_date
 ORDER BY test_date;

我将列DATEVALUE重命名为test_datetest_value,以避免使用关键字作为列标识符。我还添加了日期紧接在测试范围内的日期之前和之后的行,以暴露查询中的逐个缺陷。
对于test_values中的每一行,查询都为该行的test_date或之后的日期生成行,直到集合中下一个test_date的前一天。第一行是一个特例:如果按日期没有前一行,则start_date将替换为test_date

相关问题