postgresql - 如何使用 PostgreSQL 中的表提供的可变日期范围对行进行计数

Question

我怀疑我需要某种窗口函数来执行此操作。我有以下项目数据作为示例:

count | date
------+-----------
3     | 2017-09-15
9     | 2017-09-18
2     | 2017-09-19
6     | 2017-09-20
3     | 2017-09-21

所以我的数据首先存在差距，我在这里有另一个问题:

select until_date, until_date - (lag(until_date)  over ()) as delta_days from ranges

我生成了以下数据:

until_date | delta_days
-----------+-----------
2017-09-08 |
2017-09-11 | 3
2017-09-13 | 2
2017-09-18 | 5
2017-09-21 | 3
2017-09-22 | 1

所以我希望我的最终查询产生这个结果:

start_date | ending_date | total_items
-----------+-------------+--------------
2017-09-08 | 2017-09-10  | 0
2017-09-11 | 2017-09-12  | 0
2017-09-13 | 2017-09-17  | 3
2017-09-18 | 2017-09-20  | 15
2017-09-21 | 2017-09-22  | 3

这会根据第二张表中的自定义范围告诉我每天第一张表中的项目总数。

在这个特定的例子中，我将总结 total_items BETWEEN start AND end(因为日期会有重叠，我将从结束日期中减去 1 以不计算重复项)

有人知道怎么做吗？

谢谢!

Answer 1

使用 daterange 类型。请注意，您不必计算 delta_days , 只需转换 ranges至 dataranges并使用运算符 <@ - element is contained by.

with counts(count, date) as (
values
    (3, '2017-09-15'::date),
    (9, '2017-09-18'),
    (2, '2017-09-19'),
    (6, '2017-09-20'),
    (3, '2017-09-21')
),
ranges (until_date) as (
values
    ('2017-09-08'::date),
    ('2017-09-11'),
    ('2017-09-13'),
    ('2017-09-18'),
    ('2017-09-21'),
    ('2017-09-22')
)
select daterange, coalesce(sum(count), 0) as total_items
from (
    select daterange(lag(until_date) over (order by until_date), until_date)
    from ranges
    ) s
left join counts on date <@ daterange
where not lower_inf(daterange)
group by 1
order by 1;

        daterange        | total_items 
-------------------------+-------------
 [2017-09-08,2017-09-11) |           0
 [2017-09-11,2017-09-13) |           0
 [2017-09-13,2017-09-18) |           3
 [2017-09-18,2017-09-21) |          17
 [2017-09-21,2017-09-22) |           3
(5 rows)    

注意，在上面的日期范围内，下限是包含的，而上限是不包含的。

如果您想在日期范围内计算每天的项目数:

select 
    daterange, total_items, 
    round(total_items::dec/(upper(daterange)- lower(daterange)), 2) as items_per_day
from (
    select daterange, coalesce(sum(count), 0) as total_items
    from (
        select daterange(lag(until_date) over (order by until_date), until_date)
        from ranges
        ) s
    left join counts on date <@ daterange
    where not lower_inf(daterange)
    group by 1
    ) s
order by 1

        daterange        | total_items | items_per_day 
-------------------------+-------------+---------------
 [2017-09-08,2017-09-11) |           0 |          0.00
 [2017-09-11,2017-09-13) |           0 |          0.00
 [2017-09-13,2017-09-18) |           3 |          0.60
 [2017-09-18,2017-09-21) |          17 |          5.67
 [2017-09-21,2017-09-22) |           3 |          3.00
(5 rows)