累计月度总数和Postgresql[英] Cumulative Monthly Totals and Postgresql

本文是小编为大家收集整理的关于累计月度总数和Postgresql的处理方法,想解了累计月度总数和Postgresql的问题怎么解决?累计月度总数和Postgresql问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

我正在尝试为 dellstore2 数据库累计计算用户数.在这里查看答案和其他论坛,我使用了这个

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

看起来总体不错,看看前几项.但是当我跑的时候

select count(distinct(customerid)) from orders

对于整个事情,我得到了

8996

与第一个输出 11,681 中的最后一项不一致.我想上面的计算不能确定几个月的唯一性.这种计算的最快方法是什么,最好不使用自连接?

推荐答案

您可以使用这样的子查询,而不是直接从订单中选择:

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

我认为这会按要求工作.

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

本文地址:https://www.itbaoku.cn/post/1763869.html