时间戳

首页 > 解决方案 > 根据 Postgres 中另一列的条件生成整数系列

sql - 根据 Postgres 中另一列的条件生成整数系列

问题描述

我有以下数据表

create table test.my_table
(
  date                    date,
  daily_cumulative_precip real
);


INSERT INTO test.my_table (date, daily_cumulative_precip) VALUES ('2016-07-11', 0.508);
INSERT INTO test.my_table (date, daily_cumulative_precip) VALUES ('2016-07-12', 0);
INSERT INTO test.my_table (date, daily_cumulative_precip) VALUES ('2016-07-13', 0);
INSERT INTO test.my_table (date, daily_cumulative_precip) VALUES ('2016-07-14', 2.032);
INSERT INTO test.my_table (date, daily_cumulative_precip) VALUES ('2016-07-15', 0);
INSERT INTO test.my_table (date, daily_cumulative_precip) VALUES ('2016-07-16', 0);
INSERT INTO test.my_table (date, daily_cumulative_precip) VALUES ('2016-07-17', 21.842);
INSERT INTO test.my_table (date, daily_cumulative_precip) VALUES ('2016-07-18', 0);
INSERT INTO test.my_table (date, daily_cumulative_precip) VALUES ('2016-07-19', 0);
INSERT INTO test.my_table (date, daily_cumulative_precip) VALUES ('2016-07-20', 0);

我想在名为“delta”的新列中创建和分配值，基于daily_cumulative_precip. 我想知道什么时候delta = 0，daily_cumulative_precip > 0那天和前一天的时间 ，那天和前1天的时间，以及那天和前2天的时间。对于这个特定的数据表，应该是delta = 1daily_cumulative_precip = 0delta = 2daily_cumulative_precip = 0delta = 3daily_cumulative_precip = 0delta

0, 1, 2, 0, 1, 2, 0, 1, 2, 3

我有以下内容，但没有产生预期的结果

SELECT *,
      CASE
        WHEN daily_cumulative_precip > 0 THEN 0
        --ELSE date - first_value(date) OVER (ORDER BY date)
          ELSE date - lag(date) OVER (ORDER BY date)
          END AS delta
FROM "test".my_table
ORDER BY date;

我非常感谢您的帮助。

标签： sqlpostgresqlwindow

解决方案

对于您的特定数据，以下工作：

select t.*,
       (date - max(date) filter (where daily_cumulative_precip > 0) over (order by date))
from my_table t
order by date;

这将获取值大于 0 的最近日期。

这假设第一天的值大于 0。如果情况并非总是如此，则：

select t.*,
       (date -
        coalesce(max(date) filter (where daily_cumulative_precip > 0) over (order by date),
                 min(date) over (order by date)
                )
       ) as seqnum
from my_table t
order by date;

这是一个 db<>fiddle。

推荐阅读