如何从开始日期和结束日期识别和聚合序列

Question

我正试图根据person识别一个连续的日期序列，以及该序列的总和amount。我的records表如下所示：

person   start_date   end_date     amount
1        2015-09-10   2015-09-11   500
1        2015-09-11   2015-09-12   100
1        2015-09-13   2015-09-14   200
1        2015-10-05   2015-10-07   2000
2        2015-10-05   2015-10-05   300
2        2015-10-06   2015-10-06   1000
3        2015-04-23   2015-04-23   900

结果查询应该是这样的：

person   sequence_start_date   sequence_end_date     amount
1        2015-09-10            2015-09-14            800
1        2015-10-05            2015-10-07            2000
2        2015-10-05            2015-10-06            1400
3        2015-04-23            2015-04-23            900

下面，我可以使用LAG和LEAD来识别序列start_date和end_date，但我没有聚合amount的方法。我假设答案将涉及某种ROW_NUMBER()窗口函数，它将按序列进行分区，我只是不知道如何使序列对函数可识别。

SELECT
person
,COALESCE(sequence_start_date, LAG(sequence_start_date, 1) OVER (ORDER BY person, start_date)) AS "sequence_start_date"
,COALESCE(sequence_end_date, LEAD(sequence_end_date, 1) OVER (ORDER BY person, start_date)) AS "sequence_end_date"
FROM
(
SELECT
person
,start_date
,end_date
,CASE WHEN LAG(end_date, 1) OVER (PARTITION BY person ORDER BY start_date) + interval '1 day' = start_date
THEN NULL
ELSE start_date
END AS "sequence_start_date"
,CASE WHEN LEAD(start_date, 1) OVER (PARTITION BY person ORDER BY start_date) - interval '1 day' = end_date
THEN NULL
ELSE end_date
END AS "sequence_end_date"
,amount
FROM records
) sq

Answer 1

即使您更新的(子)查询仍然不太适合您提供的数据，这与序列中第二行和后续行的开始日期应该等于前一行的结束日期还是晚一天不一致。如果需要的话，可以很容易地更新查询以适应两者。

在任何情况下，都不能将COALESCE用作窗口函数。聚合函数可以通过提供OVER子句用作窗口函数，但不能用作普通函数。尽管如此，还是有一些方法可以将窗口函数应用于此任务。以下是一种识别数据中序列的方法(如图所示)：

SELECT
person
,MAX(sequence_start_date)
OVER (
PARTITION BY person
ORDER BY start_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS "sequence_start_date"
,MIN(sequence_end_date)
OVER (
PARTITION BY person
ORDER BY start_date
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
AS "sequence_end_date"
,amount
FROM
(
SELECT
person
,start_date
,end_date
,CASE WHEN LAG(end_date, 1) OVER (PARTITION BY person ORDER BY start_date) + interval '1 day' >= start_date
THEN date '0001-01-01'
ELSE start_date
END AS "sequence_start_date"
,CASE WHEN LEAD(start_date, 1) OVER (PARTITION BY person ORDER BY start_date) - interval '1 day' <= end_date
THEN NULL
ELSE end_date
END AS "sequence_end_date"
,amount
FROM records
order by person, start_date
) sq_part
ORDER BY person, sequence_start_date

它依赖于MAX()和MIN()而不是COALESCE()，并且它应用窗口成帧来为每个分区中的每个分区获得适当的范围。结果：

person  sequence_start_date         sequence_end_date           amount
1       September, 10 2015 00:00:00 September, 12 2015 00:00:00 500
1       September, 10 2015 00:00:00 September, 12 2015 00:00:00 100
1       October, 05 2015 00:00:00   October, 07 2015 00:00:00   2000
2       October, 05 2015 00:00:00   October, 06 2015 00:00:00   300
2       October, 05 2015 00:00:00   October, 06 2015 00:00:00   1000
3       April, 23 2015 00:00:00     April, 23 2015 00:00:00     900

请注意，这不需要结束日期与随后的开始日期完全匹配；与相邻或与重叠的每个人的所有行都将被分配到相同的序列。然而，如果(person，start_date)不能被认为是唯一的，那么您可能还需要按结束日期对分区进行排序。

现在你有了一种识别序列的方法：它们由三重person, sequence_start_date, sequence_end_date表征。(或者实际上，出于识别目的，您只需要个人和其中一个，但请继续阅读。)您可以将上述查询包装为外部聚合查询的内联视图，以产生您想要的结果：

SELECT
person,
sequence_start_date,
sequence_end_date,
SUM(amount) AS "amount"
FROM ( <above query> ) sq
GROUP BY person, sequence_start_date, sequence_end_date

当然，如果要选择这两个日期，则需要将它们作为分组列。

Answer 2

为什么不：

select a1.person, a1.sequence_start_date, a1.sequence_end_date, 
sum(rx.amount) 
as amount
from (EXISTING_QUERY) a1
left join records rx 
on rx.person = a1.person 
and rx.start_date >= a1.start_date
and rx.end_date <= a1.end_date
group by a1.person, a1.sequence_start_date, a1.sequence_end_date