我试图显示今天添加的新用户以及总数。
这个
SELECT
DATE("CreatedAt"),
COUNT(DATE("CreatedAt")),
COUNT(DISTINCT"Id")
FROM "User"
GROUP BY DATE("CreatedAt")
ORDER BY DATE("CreatedAt") DESC
输出此
Date. New Total
"2020-04-21" 50 50
"2020-04-20" 40 40
"2020-04-19" 30 30
"2020-04-18" 20 20
"2020-04-17" 10 10
如何在"总计"列中显示(迄今为止(用户总数?第21条应改为150。
您可以使用窗口函数来实现这一点:基本上,您需要每个组的行数的窗口总和,如:
select
date("CreatedAt") created_day,
count(*) "New",
sum(count(*)) over(order by date("CreatedAt")) "Total"
from "User"
group by date("CreatedAt")
order by created_day desc