sql - 按周计算首次订阅者

Question

我在 PostgreSQL 10.5 中有一个订阅表:

id  user_id  starts_at  ends_at
--------------------------------
1   233      02/04/19   03/03/19
2   233      03/04/19   04/03/19
3   296      02/09/19   03/08/19
4   126      02/01/19   02/28/19
5   126      03/01/19   03/31/19
6   922      02/22/19   03/22/19

对于每周，我想统计我们有多少新订阅者。新订阅者可以是在该周之前没有订阅条目的任何用户 ID。

编辑 我稍微修改了@fubar 解决方案以适应我喜欢的日期格式。我忘记在此处添加的一个说明是，我希望看到有 0 的星期。我如何将 generate_series 集成到下面的查询中，这样我还可以看到有 0 个订阅者的周数？

SELECT TO_CHAR(date_trunc('week', s.starts_at), 'YYYY-MM-DD') as week, COUNT(*) AS count
FROM subscriptions s
LEFT JOIN subscriptions s1 ON s.user_id = s1.user_id AND s.starts_at > s1.starts_at
WHERE s1.id IS NULL
GROUP BY week
ORDER BY week desc

Answer 1

您可以使用以下查询找到每个用户的第一个订阅:

SELECT s.*
FROM subscriptions s
LEFT JOIN subscriptions s1 ON s.user_id = s1.user_id AND s.starts_at > s1.starts_at
WHERE s1.id IS NULL

然后您可以使用以下查询计算每年/每周的新订阅者数量:

SELECT 
    EXTRACT(YEAR FROM s.starts_at) AS year,
    EXTRACT(WEEK FROM s.starts_at) AS week,
    COUNT(*) AS count
FROM subscriptions s
LEFT JOIN subscriptions s1 ON s.user_id = s1.user_id AND s.starts_at > s1.starts_at
WHERE s1.id IS NULL
GROUP BY year, week;

下面是一个更新的查询，它将我上面的回答与 generate_series() 和您首选的星期日期格式相结合。

SELECT 
  TO_CHAR(date_trunc('week', w.date), 'YYYY-MM-DD') AS week, 
  COUNT(DISTINCT s.*) AS count
FROM generate_series('2018-12-31', NOW(), INTERVAL '1 WEEK') w(date)
LEFT JOIN subscriptions s ON s.starts_at BETWEEN w.date AND w.date + INTERVAL '6 DAY'
LEFT JOIN subscriptions s1 ON s.user_id = s1.user_id AND s.starts_at > s1.starts_at
WHERE s1.id IS NULL
GROUP BY w.date;

数据库 fiddle :https://www.db-fiddle.com/f/b73AbU3KU6dsfTvXu3mzjz/1