Geek asks and answers

Cumulative monthly totals and Postgresql

I am trying to calculate the number of users cumulatively for a dellstore2 database. Looking at the answers here and to 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)

It returns

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

Every month

979
973
970
976
975
973
981
972
966
980
965
971

Everything seems to be fine, looking at the first few items. But when I ran

select count(distinct(customerid)) from orders

for everything i get

8996

which is not consistent with the last paragraph on the first exit 11.681. I think that the calculation above cannot determine uniqueness for several months. What is the fastest way for this calculation, preferably without using independent compounds?

+5
source share
1 answer

Instead of directly selecting from orders, you can use a subquery, for example:

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 will work as needed.

http://sqlfiddle.com/#!1/7a8cc/1

(.. ), :

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

+7

More articles:


All Articles