进行sql查询以生成累积订户的最简单方法是什么？

7xzttuei 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(384)

我有一个 subscriptions 带有列的表（在postgresql中） subscribed_at 以及 unsubscribed_at . 我想编写一个简单的查询，生成每个月底的累计订户计数列表（订阅减去截至该日期的未订阅）。
每一行基本上都是在查询：如果groupubymonth是“一月-2020”，那么有多少订阅 subscribed_at 日期为2020年1月或之前的一个月，以及 unsubscribed_at 无效日期或2020年1月之后。
我只是对sql不够熟悉，不知道这里的正确语法。
任何帮助都将不胜感激！
table

+----+------+---------------+-----------------+
| id | name | subscribed_at | unsubscribed_at |
+----+------+---------------+-----------------+
|  1 | John | '2020-01-08'  | null            |
|  2 | Mary | '2020-01-09'  | '2020-01-20'    |
|  3 | Jeff | '2020-01-10'  | null            |
|  4 | Bill | '2020-02-02'  | null            |
|  5 | Bob  | '2020-02-08   | '2020-02-21'    |
+----+------+---------------+-----------------+

查询

SELECT DATE_TRUNC('month', subscribed_at) as month,
COUNT(*) as subscribers
FROM subscriptions
GROUP BY 1
ORDER BY 1

期望输出

+---------------+-------------+
|     month     | subscribers |
+---------------+-------------+
| January 2020  |           2 |
| February 2020 |           3 |
+---------------+-------------+

哪里 subscribers 是该月底的累计净订户数。
下面是一个处理数据和查询的sqlfiddle：http://www.sqlfiddle.com/#!15/cd7725/1号文件

sql postgresql Date count time-series

来源：https://stackoverflow.com/questions/62583512/whats-the-simplest-way-to-make-a-sql-query-to-generate-cumulative-subscribers

2条答案

按热度按时间

amrnrhlw1#

你可以用 generate_series() 枚举从第一个订阅开始到最后一个订阅结束之间的所有月份，然后横向联接以计算活动订阅计数。

select d.dt, n.no_active_subscriptions
from (
    select generate_series(
        date_trunc('month', min(subscribed_at)), 
        date_trunc('month', max(unsubsribed_at)), 
        interval '1 month'
    ) dt
    from subscriptions
) d
cross join lateral (
    select count(*) no_active_subscriptions
    from subscriptions s
    where 
        s.subscribed_at < d.dt + interval '1 month' 
        and (s.unsubscribed_at >= d.dt or s.unsubscribed_at is null)
) n

赞(0）回复(0）举报 2021-07-24

8dtrkrch2#

这里有一个方法。计算某人开始的日期+1和停止的日期-1。然后做一个累加。
在月末，记录每月的最后一条记录：

with s as (
      select dte, sum(inc) ondate, sum(sum(inc)) over (order by dte) as subs
      from subscriptions s cross join lateral
           (values (subscribed_at, 1), (unsubscribed_at, -1)
           ) v(dte, inc)
      where v.dte is not null
      group by v.dte
     )
 select distinct on (date_trunc('month', dte)) *
 from s
 order by date_trunc('month', dte), dte desc;

这是一把小提琴。
这也许正是你想要的。或者你可能想调整一下。
停止计算的日期可能是停止后的第二天。如果是，则在子查询中添加一个日期。
如果要在每月的最后一天报告，可以使用表达式 (date_trunc('month', dte) + interval '1 month - 1 day')::date .

赞(0）回复(0）举报 2021-07-24

我来回答

进行sql查询以生成累积订户的最简单方法是什么？

2条答案

相关问题

热门标签

最新问答