时间戳

首页 > 解决方案 > 基于另一列日期的 SQL 返回日期

sql - 基于另一列日期的 SQL 返回日期

问题描述

我有一个降雨预报表，可以预测两个城市的预计下雨日期。该表如下所示：

CREATE TABLE raincheck(
city varchar(255),
expected_rain_date date,
actual_rain_date date);
INSERT INTO raincheck 
(city,expected_rain_date,actual_rain_date) VALUES 
('NYC','2001-02-02',null),
('NYC','2001-02-11',null),
('NYC','2001-03-20','2001-03-21'),
('NYC','2001-03-25',null),
('NYC','2001-04-10',null),
('NYC','2001-04-12','2001-04-12'),
('LDN','2001-02-03',null),
('LDN','2001-02-07','2001-02-07'),
('LDN','2001-03-11',null),
('LDN','2001-03-12',null),
('LDN','2001-03-17',null),
('LDN','2001-03-20','2001-03-20');
SELECT * FROM raincheck;

由于实际下雨并不总是准确的，我需要一个表格，其中包含第一个预期下雨日期和最新实际下雨日期之间的持续时间。结果如下所示：

CREATE TABLE rainresult(
city varchar(255),
expected_rain_date date,
actual_rain_date date);
INSERT INTO rainresult
(city,expected_rain_date,actual_rain_date) VALUE
('NYC','2001-02-02','2001-03-21'),
('NYC','2001-03-25','2001-04-12'),
('LDN','2001-02-03','2001-02-07'),
('LDN','2001-03-11','2001-03-20');
SELECT * FROM rainresult;

我认为查询可能与NULL有关，例如 SELECT * FROM rainresult WHERE actual_rain_date IS NOT NULL; 但我无法过滤expected_rain_date

标签： sql

解决方案

您希望根据每行上或之后的实际下雨日期计数来分配组。然后聚合：

select city, min(expected_rain_date), max(actual_rain_date)
from (select rc.*,
             sum(case when actual_rain_date is not null then 1 else 0 end) over (partition by city order by expected_rain_date desc) as grp
      from raincheck rc
     ) rc
group by city, grp;

这是一个 db<>fiddle。它碰巧使用 Postgres，但这都是标准 SQL。

推荐阅读