在Postgresql中计算累积总数

Question

在Postgresql中计算累积总数

78

我正在使用count和group by来获取每天注册用户的数量：

  SELECT created_at, COUNT(email)  
    FROM subscriptions 
GROUP BY created at;

结果：

created_at  count
-----------------
04-04-2011  100
05-04-2011   50
06-04-2011   50
07-04-2011  300

我希望获取每天的累积订阅者总数，该如何实现？

created_at  count
-----------------
04-04-2011  100
05-04-2011  150
06-04-2011  200
07-04-2011  500

- khairul

6个回答

9

使用：

SELECT a.created_at,
       (SELECT COUNT(b.email)
          FROM SUBSCRIPTIONS b
         WHERE b.created_at <= a.created_at) AS count
  FROM SUBSCRIPTIONS a

- OMG Ponies

3

如果您在今天（2021年）看到这个答案，您可以使用Rollup。

SELECT created_at, COUNT(email)  
    FROM subscriptions 
GROUP BY rollup(created_at);

这将为您提供一个带有总数的新行。

created_at  count
-----------------
04-04-2011  100
05-04-2011   50
06-04-2011   50
07-04-2011  300
NULL        500

如果您有多个参数要在group by中显示，您也可以使用rollup来获取部分结果。例如，如果您有一个“created_by”参数：

SELECT created_at, created_by COUNT(email)  
    FROM subscriptions 
GROUP BY rollup(created_at, created_by);

这将为您提供一个带有总计的新行

created_at  created_by  count
-----------------------------
04-04-2011     1        80
04-04-2011     2        20
04-04-2021    NULL      100
05-04-2011     1        20
05-04-2011     2        30
05-04-2011    NULL      50
NULL          NULL      150

我只取了前两天的数字，但这是个想法。它会按日期分组显示，然后显示那一天的总数，接着是总计的总数。

在这里，rollup() 中的顺序很重要，因为它决定了部分总数的显示方式。

- Pstr

2

这真的应该是2021年及以后的最佳答案！在我的系统上：窗口函数（计划时间：1.134毫秒，执行时间：1.045毫秒），Rollup（计划时间：0.245毫秒，执行时间：0.642毫秒）。Rollup的性能显著更高。 - Little Code

7

这不是累积的，正如原帖所要求的那样。 - jeffdill2

Rollup 对我不起作用。 - Pencilcheck

2

SELECT
  s1.created_at,
  COUNT(s2.email) AS cumul_count
FROM subscriptions s1
  INNER JOIN subscriptions s2 ON s1.created_at >= s2.created_at
GROUP BY s1.created_at

- Andriy M

我尝试了 sum(s2.count)，但控制台报错：'聚合函数调用不能嵌套'。 - khairul

我的意思是 COUNT(s2.email)，抱歉。请查看我编辑过的解决方案。 - Andriy M

谢谢伙计！我一直在处理一个更复杂的查询，而你的结构很容易理解（因此也容易实现）。 - khairul

2

我假设您每天只想显示一行，并且仍然想显示没有任何订阅的日期（假设某个日期没有人订阅，您是否想要显示该日期和前一天的余额？）。如果是这种情况，则可以使用'with'功能：

with recursive serialdates(adate) as (
    select cast('2011-04-04' as date)
    union all
    select adate + 1 from serialdates where adate < cast('2011-04-07' as date)
)
select D.adate,
(
    select count(distinct email)
    from subscriptions
    where created_at between date_trunc('month', D.adate) and D.adate
)
from serialdates D

- Endy Tjahjono

谢谢，那个 with 函数也很有用。学到了新东西。 - khairul

2

你可以使用内置函数来代替 serialdates: generate_series(timestamp '2011-04-04', timestamp '2011-04-07', interval '1 day')。 - intgr

-3

最好的方法是建立一个日历表： calendar ( date日期, month月份, quarter季度, half半年, week周数, year年份 )

然后，您可以将此表与需要汇总的字段进行连接。

- mentat

2

那与获取累计总数无关。 - user330315

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- intgr · Accepted Answer

对于较大的数据集，窗口函数是执行此类查询的最有效方式 - 表格只会被扫描一次，而不是像自连接一样每个日期都要扫描一次。它看起来也更简单。 :) PostgreSQL 8.4及以上版本支持窗口函数。

它是这个样子的：

SELECT created_at, sum(count(email)) OVER (ORDER BY created_at)
FROM subscriptions
GROUP BY created_at;

OVER创建窗口；ORDER BY created_at表示必须按照created_at的顺序汇总计数。

编辑：如果您想在一天内删除重复的电子邮件，可以使用sum(count(distinct email))。不幸的是，这无法消除跨越不同日期的重复项。

如果要删除所有重复项，则最简单的方法是使用子查询和DISTINCT ON。这将把电子邮件归属到它们最早的日期（因为我按升序排序created_at，所以它会选择最早的日期）:

SELECT created_at, sum(count(email)) OVER (ORDER BY created_at)
FROM (
    SELECT DISTINCT ON (email) created_at, email
    FROM subscriptions ORDER BY email, created_at
) AS subq
GROUP BY created_at;

如果你在(email, created_at)上创建了一个索引，那么这个查询也不应该太慢。

（如果您想进行测试，这是我创建样本数据集的方法）

create table subscriptions as
   select date '2000-04-04' + (i/10000)::int as created_at,
          'foofoobar@foobar.com' || (i%700000)::text as email
   from generate_series(1,1000000) i;
create index on subscriptions (email, created_at);