累计每月总计和Postgresql
发布时间:2020-12-13 16:03:01 所属栏目:百科 来源:网络整理
导读:我试图计算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
我试图计算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 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |