sql - 如何从 PostgreSQL 中的另一个表生成日期范围 + 计算更早的日期？

Question

我有下表:

链接:

created_at           active 
2017-08-12 15:46:01  false
2017-08-13 15:46:01  true
2017-08-14 15:46:01  true
2017-08-15 15:46:01  false

当给定一个日期范围时，我必须提取时间序列，它告诉我在等于或小于当前(滚动)日期的日期创建了多少事件链接。

输出(日期范围为 2017-08-12 - 2017-08-17):

day          count
2017-08-12   0 (there are 0 active links created on 2017-08-12 and earlier)
2017-08-13   1 (there is 1 active link created on 2017-08-13 and earlier)
2017-08-14   2 (there are 2 active links created on 2017-08-14 and earlier)
2017-08-15   2 ...
2017-08-16   2
2017-08-17   2

我提出了以下用于生成日期的查询:

SELECT date_trunc('day', dd):: date
FROM generate_series
    ( '2017-08-12'::timestamp 
    , '2017-08-17'::timestamp
    , '1 day'::interval) dd

但滚动计数让我感到困惑，我不确定如何继续。这可以用窗口函数解决吗？

Answer 1

这应该是最快的:

SELECT day::date
     , sum(ct) OVER (ORDER BY day) AS count
FROM   generate_series (timestamp '2017-08-12'
                      , timestamp '2017-08-17'
                      , interval  '1 day') day
LEFT   JOIN  (
   SELECT date_trunc('day', created_at) AS day, count(*) AS ct
   FROM   tbl
   WHERE  active -- fastest
   GROUP  BY 1
   ) t USING (day)
ORDER  BY 1;

dbfiddle here

count() 仅计算非空行，因此您可以使用 count(active OR NULL)。但最快的计数方法是使用 WHERE 子句排除不相关的行。由于无论如何我们都使用 generate_series() 添加所有天数，因此这是最佳选择。

比较:

• For absolute performance, is SUM faster or COUNT?

由于 generate_series() 返回 timestamp(不是 date)，我使用 date_trunc() 来获取匹配的时间戳(非常快)。