时间戳

首页 > 解决方案 > 如何编写 sql 以在 Postgres 中生成每个客户的月累计销售额

sql - 如何编写 sql 以在 Postgres 中生成每个客户的月累计销售额

问题描述

鉴于我有一张名为orders

订单

id
customer_id
created_at

如何编写查询以返回每个客户的每月累计订单数？我想在 2018 年 1 月至 2018 年 5 月的系列中包含缺失的月份

数据

id   customer_id   created_at
1    200           01/20/2018
2    300           01/21/2018
3    200           01/22/2018
4    200           03/20/2018
5    300           03/20/2018
6    200           04/20/2018
7    200           04/20/2018

预期结果

customer_id   month       count
200           01/01/2018  2
200           02/01/2018  2
200           03/01/2018  3
200           04/01/2018  5
200           05/01/2018  5

300           01/01/2018  1
300           02/01/2018  1
300           03/01/2018  2
300           04/01/2018  2
300           05/01/2018  2

我有一个查询来计算每月的净累积计数。在将查询转换为按客户累积计数工作时，我没有取得太大的成功。

WITH monthly_orders AS (
   SELECT date_trunc('month', orders.created_at) AS mon, 
          COUNT(orders.id) AS mon_count
   from   orders
   GROUP  BY 1
)
SELECT TO_CHAR(mon, 'YYYY-MM') AS mon_text,
       COALESCE(SUM(c.mon_count) OVER (ORDER BY c.mon), 0) AS running_count
FROM   generate_series('2018-01-01'::date, '2018-06-01'::date, interval '1 month') mon
LEFT JOIN monthly_orders c USING(mon)
ORDER  BY mon_text;

标签： sqlpostgresql

解决方案

如果我理解正确，您可以这样做：

select o.customer_id, date_trunc('month', o.created_at) AS mon, 
       count(*) AS mon_count,
       sum(count(*)) over (partition by o.customer_id
                           order by date_trunc('month', o.created_at) 
                          ) as running_count
from  orders o
group by o.customer_id, mon
order by o.customer_id, mon;

推荐阅读