sql - 在 PostgreSQL 中滚动 12 个月的总和

Question

我需要能够使用 SQL (PostgreSQL) 创建一个追踪 12 个月的报告——本质上是一个窗口/滚动 12 个月的总和，它总结了当月的总数 + 每个月的前 11 个月。

我有这张 table :

CREATE TABLE order_test(
order_id text,
sale_date date,
delivery_date date,
customer_id text,
vendor_id text,
order_total float);

具有这些值:

insert into order_test
values ('1', '2016-06-01', '2016-06-10', '2', '3', 200.10),
   ('2', '2016-06-02', '2016-06-11', '2', '4', 150.50),
   ('3', '2016-07-02', '2016-07-11', '5', '4', 100.50),
   ('4', '2016-07-02', '2016-07-11', '1', '4', 150.50),
   ('5', '2016-07-02', '2016-07-11', '1', '4', 150.50),
   ('6', '2016-08-02', '2016-08-11', '6', '4', 300.50),
   ('7', '2016-08-02', '2016-08-11', '6', '4', 150.50),
   ('8', '2016-09-02', '2016-09-11', '1', '4', 150.50),
   ('9', '2016-10-02', '2016-10-11', '1', '4', 150.50),
   ('10', '2016-11-02', '2016-11-11', '1', '4', 150.50),
   ('11', '2016-12-02', '2016-12-11', '6', '4', 150.50),
   ('12', '2017-01-02', '2017-01-11', '7', '4', 150.50),
   ('13', '2017-01-02', '2017-01-11', '1', '4', 150.50),
   ('14', '2017-01-02', '2017-01-11', '1', '4', 100.50),
   ('15', '2017-02-02', '2017-02-11', '1', '4', 150.50),
   ('16', '2017-02-02', '2017-02-11', '1', '4', 150.50),
   ('17', '2017-03-02', '2017-03-11', '2', '4', 150.50),
   ('18', '2017-03-02', '2017-03-11', '2', '4', 150.50),
   ('19', '2017-04-02', '2017-04-11', '6', '4', 120.50),
   ('20', '2017-05-02', '2017-05-11', '1', '4', 150.50),
   ('21', '2017-06-02', '2017-06-11', '2', '4', 150.50),
   ('22', '2017-06-02', '2017-06-11', '1', '4', 130.50),
   ('23', '2017-07-02', '2017-07-11', '1', '4', 150.50),
   ('24', '2017-07-02', '2017-07-11', '5', '4', 200.50),
   ('25', '2017-08-02', '2017-08-11', '1', '4', 150.50),
   ('26', '2017-09-02', '2017-09-11', '2', '4', 100.50),
   ('27', '2017-09-02', '2017-10-11', '1', '4', 150.50);

这些是个人销售。对于每个月，我需要前 11 个月 + 该月的总数(销售月份)。

我试过这样的窗口计算:

select date_trunc('month', sale_date) as sale_month,
       sum(order_total) over w as total_sales
from order_test
where (delivery_date < current_date) and
      (sale_date >= (date_trunc('month', current_date) - interval '1 year'))
window w as (Partition by date_trunc('month', sale_date)
             order by sale_date
             rows between current row and 11 following)

但它给了我这个:

      sale_month    total_sales
1   01.09.2016 00:00:00 150,5
2   01.10.2016 00:00:00 150,5
3   01.11.2016 00:00:00 150,5
4   01.12.2016 00:00:00 150,5
5   01.01.2017 00:00:00 401,5
6   01.01.2017 00:00:00 251
7   01.01.2017 00:00:00 100,5
8   01.02.2017 00:00:00 301
9   01.02.2017 00:00:00 150,5
10  01.03.2017 00:00:00 301
11  01.03.2017 00:00:00 150,5
12  01.04.2017 00:00:00 120,5
13  01.05.2017 00:00:00 150,5
14  01.06.2017 00:00:00 281
15  01.06.2017 00:00:00 130,5
16  01.07.2017 00:00:00 351
17  01.07.2017 00:00:00 200,5
18  01.08.2017 00:00:00 150,5
19  01.09.2017 00:00:00 100,5

每月应该只有一行。

Answer 1

在内部查询派生表中，您需要截断 Sale_Date列到 month精度使用 date_trunc和 group by结果列得到 Month_total销售然后在外部查询中，使用累积窗口sum功能在 month_total销售数据排序方式 Sale_Month得到你想要的结果如下。

SELECT sale_Month
    ,month_total
    ,sum(month_total) OVER (
        ORDER BY sale_Month ASC rows BETWEEN 11 preceding
                AND CURRENT row
        ) AS Sum_Series
FROM (
    SELECT date_trunc('month', Sale_Date) AS Sale_Month
        ,sum(Order_Total) AS Month_Total
    FROM order_test
    GROUP BY 1
    ORDER BY 1
    ) t

请注意 AND CURRENT row是可选的，因为累积窗口函数包括 current行默认，所以查询可以重写如下。

SELECT sale_Month
    ,month_total
    ,sum(month_total) OVER (
        ORDER BY sale_Month ASC rows 11 preceding
        ) AS Sum_Series
FROM (
    SELECT date_trunc('month', Sale_Date) AS Sale_Month
        ,sum(Order_Total) AS Month_Total
    FROM order_test
    GROUP BY 1
    ORDER BY 1
    ) t

Result:

sale_month            month_total   sum_series
----------------------------------------------
2016-06-01T00:00:00Z    350.6         350.6
2016-07-01T00:00:00Z    401.5         752.1
2016-08-01T00:00:00Z    451           1203.1
2016-09-01T00:00:00Z    150.5         1353.6
2016-10-01T00:00:00Z    150.5         1504.1
2016-11-01T00:00:00Z    150.5         1654.6
2016-12-01T00:00:00Z    150.5         1805.1
2017-01-01T00:00:00Z    401.5         2206.6
2017-02-01T00:00:00Z    301           2507.6
2017-03-01T00:00:00Z    301           2808.6
2017-04-01T00:00:00Z    120.5         2929.1
2017-05-01T00:00:00Z    150.5         3079.6
2017-06-01T00:00:00Z    281           3010
2017-07-01T00:00:00Z    351           2959.5
2017-08-01T00:00:00Z    150.5         2659
2017-09-01T00:00:00Z    251           2759.5

您可以查看演示 here