Postgresql计算每个日期的行数

Question

我想做一个图表，这样我就可以跟踪不断增长的用户数量。

因此，我编写了一条SQL语句，查找特定时间段内新增的用户:

select distinct Createtime, count (user_id) as new_users 
from user 
where createtime between '2015-12-03 00:00:00' and CURRENT_DATE 
group by createtime 
order by createtime asc;

显示如下结果:

createtime|new_users
---------------------
2015-12-04|    1
2016-01-20|    1     
2016-02-03|    5
2016-02-04|    1

我想把结果加起来，得到每个日期的当前用户数量，所以结果应该看起来更像这样:

createtime|new_users
---------------------
2015-12-04|    1
2016-01-20|    2     
2016-02-03|    7
2016-02-04|    8

是否有SQL语句或不同的方式来实现这一点?我希望你能帮我解决这个问题。

Answer 1

您可以使用窗口函数:

select createtime, sum(new_users) over (order by createtime) as new_users
from (
  select createtime::date as createtime, count(user_id) as new_users 
  from "user"
  where createtime between '2015-12-03 00:00:00' and CURRENT_DATE 
  group by createtime 
) t
order by createtime asc;

---

user是一个保留关键字，需要加引号，否则不能用作标识符。

Answer 2

try this:

select x.Createtime, 
(select count (sub.user_id) from USER sub 
WHERE sub.createtime between '2015-12-03 00:00:00' and x.Createtime )as new_users 
from user x
where createtime between '2015-12-03 00:00:00' and CURRENT_DATE 
group by createtime 
order by createtime asc;