每月总计和Postgresql [英] Cumulative Monthly Totals and Postgresql
问题描述
我正在尝试为dellstore2数据库累计计算用户数。在这里和其他论坛中查看答案时,我使用了此
I am trying to calculate number of users, cumulatively for the dellstore2 database. Looking at answers here, and other forums, I used this
select
date_trunc('month',orderdate),
sum(count(distinct(customerid)))
over (order by date_trunc('month',orderdate))
from orders group by date_trunc('month',orderdate)
此返回
2004-01-01 00:00:00.0 979
2004-02-01 00:00:00.0 1,952
2004-03-01 00:00:00.0 2,922
2004-04-01 00:00:00.0 3,898
2004-05-01 00:00:00.0 4,873
2004-06-01 00:00:00.0 5,846
2004-07-01 00:00:00.0 6,827
2004-08-01 00:00:00.0 7,799
2004-09-01 00:00:00.0 8,765
2004-10-01 00:00:00.0 9,745
2004-11-01 00:00:00.0 10,710
2004-12-01 00:00:00.0 11,681
每个月是
979
973
970
976
975
973
981
972
966
980
965
971
似乎总计很好,看一下前几项。但是当我运行
It seems to be totaling fine, looking at the first few items. But when I ran
select count(distinct(customerid)) from orders
对于整个事情,我都会得到
for the entire thing, I get
8996
与第一个输出11,681中的最后一项不一致。我猜上面的计算无法确定整个月的唯一性。什么是最快的计算方式(最好不使用自联接)?
which does not agree with the last item in the first output 11,681. I guess the calculation above cannot determine uniqueness across months. What is the fastest way for this calculation, preferably without using self-joins?
推荐答案
除了直接从订单中选择之外,您还可以使用子查询,如下所示:
Instead of selecting directly from orders, you could use a subquery like so:
SELECT OrderDate,
SUM(COUNT(DISTINCT customerid)) OVER (ORDER BY OrderDate)
FROM ( SELECT CustomerID,
DATE_TRUNC('MONTH', MIN(OrderDate)) AS OrderDate
FROM Orders
GROUP BY CustomerID
) AS Orders
GROUP BY OrderDate
我认为这会按要求工作。
I think this would work as required.
http://sqlfiddle.com/#!1/7a8cc/1
编辑
如果您仍然需要这两种方法(即独立和运行总计),则可以使用以下方法:
If you still needed both methods (i.e. distinct and running total) you could use this:
SELECT OrderDate,
COUNT(DISTINCT CustomerID) AS MonthTotal,
SUM(COUNT(DISTINCT customerid)) OVER (ORDER BY OrderDate) AS CumulativeTotal,
SUM(COUNT(DISTINCT CASE WHEN OrderNumber = 1 THEN customerid END)) OVER (ORDER BY OrderDate) AS CumulativeDistinctTotal
FROM ( SELECT CustomerID,
OrderDate,
ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY OrderDate) AS OrderNumber
FROM Orders
) AS Orders
GROUP BY OrderDate
示例:
http://sqlfiddle.com/# !1 / 7a8cc / 10
这篇关于每月总计和Postgresql的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!