sql - 填补 PostgreSQL 查询结果中的空白

Question

我有下表 data_users

  id   |    signed_up_at     | product_id
-------+---------------------------------
 20030 | 2017-09-15 12:51:45 | 2
 20122 | 2017-09-15 12:51:45 | 2
 21461 | 2017-09-15 12:51:45 | 2
 20150 | 2017-09-13 10:10:10 | 2
 19858 | 2017-09-10 23:00:54 | 2
 20126 | 2017-09-10 23:00:28 | 2
 20888 | 2017-09-10 23:00:28 | 2
 20143 | 2017-09-10 23:00:28 | 2
 21369 | 2017-09-10 23:00:02 | 2

我正在使用查询从该表中查找每天的累计总和:

SELECT DATE_TRUNC('day', signed_up_at::timestamptz) AS date,
       SUM(COUNT(*)) OVER (ORDER BY DATE_TRUNC('day', signed_up_at::timestamptz)) AS sum
FROM "data_users"
WHERE product_id = 2
GROUP BY date ORDER BY date

它返回这样的东西

          date          | sum
------------------------+------
 2017-09-15 00:00:00+02 | 1693
 2017-09-13 00:00:00+02 | 1690
 2017-09-10 00:00:00+02 | 1689

如您所见，我在没有使用该 signed_up_at 的用户的日子里有日期间隔。

而不是这个，我想得到以下结果

          date          | sum
------------------------+------
 2017-09-15 00:00:00+02 | 1693
 2017-09-14 00:00:00+02 | 1690
 2017-09-13 00:00:00+02 | 1690
 2017-09-12 00:00:00+02 | 1689
 2017-09-11 00:00:00+02 | 1689
 2017-09-10 00:00:00+02 | 1689

所以我想获得每一天的总和，而不仅仅是那些拥有该 signed_up_at 用户的日子。

我正在尝试通过 GENERATE_SERIES 实现这一点，但我没有得到想要的结果:

SELECT DATE_TRUNC('day', signed_up_at::timestamptz) AS date,
       SUM(COUNT(*)) OVER (ORDER BY DATE_TRUNC('day', signed_up_at::timestamptz)) AS sum
FROM (SELECT GENERATE_SERIES(MIN(signed_up_at)::DATE, MAX(signed_up_at)::DATE, '1 DAY'::INTERVAL) AS date
      FROM "data_users") AS d
LEFT OUTER JOIN "data_users" u ON u.signed_up_at::DATE = d.date::DATE
WHERE product_id = 2
GROUP BY signed_up_at ORDER BY date

我应该如何修改我的查询以支持它？ Turo 的回答帮助我指明了正确的方向，但我现在对 WHERE 子句有疑问。

Answer 1

我手头没有postgres，通过我看到计数和分组的问题，请尝试

SELECT DATE_TRUNC('day', date::timestamptz) AS date,
       SUM(COUNT(signed_up_at)) OVER (ORDER BY DATE_TRUNC('day', 
date::timestamptz)) AS sum
FROM (SELECT GENERATE_SERIES(MIN(signed_up_at)::DATE, MAX(signed_up_at)::DATE, 
'1 DAY'::INTERVAL) AS date
      FROM "data_users") AS d
LEFT OUTER JOIN "data_users" u ON u.signed_up_at::DATE = d.date::DATE
GROUP BY date ORDER BY date