在SQL中计算最近2个月的合计

Question

我是一个新的SQL学习者，正在练习分区、等级和Row_Number。我有一个类似下面的表(查询)。我想计算最近2个月/最近2个月的总数。

create table mytable(billing varchar(50), month date, usd int); 
insert into mytable(billing, month, usd) 
values ('CA - N', '2020-01', 56391),
('CA - N', '2020-02', 61220),
('CA - N', '2020-03', 56980),
('CA - N', '2020-04', 62833),
('CA - N', '2020-05', 66521),
('CA - N', '2020-06', 76571),
('CA - N', '2020-07', 75277),
('CA - N', '2020-08', 128640),
('CA - N', '2020-09', 118395),
('CA - N', '2020-10', 79436),
('CA - N', '2020-11', 136632),
('CA - N', '2020-12', 121607),
('CA - N', '2021-01', 3441113),
('CA - N', '2021-02', 3090556),
('CA - N', '2021-03', 3187036),
('CA - N', '2021-04', 3365094),
('CA - N', '2021-05', 3793464),
('CA - N', '2021-06', 3809898),
('CA - S', '2021-01', 425074),
('CA - S', '2021-02', 426753),
('CA - S', '2021-03', 468677),
('CA - S', '2021-04', 442293),
('CA - S', '2021-05', 219375),
('CA - S', '2021-06', 222490),
('NY', '2020-01', 326096),
('NY', '2020-02', 368638),
('NY', '2020-03', 408190),
('NY', '2020-04', 418234),
('NY', '2020-05', 409246),
('NY', '2020-06', 445423),
('NY', '2020-07', 431093),
('NY', '2020-08', 445916),
('NY', '2020-09', 481881),
('NY', '2020-10', 508123),
('NY', '2020-11', 527837),
('NY', '2020-12', 574562),
('NY', '2021-01', 661497),
('NY', '2021-02', 630369),
('NY', '2021-03', 651762),
('NY', '2021-04', 696871),
('NY', '2021-05', 690926),
('NY', '2021-06', 677027),
('NV', '2020-01', 772361),
('NV', '2020-02', 787274),
('NV', '2020-03', 859108),
('NV', '2020-04', 821224),
('NV', '2020-05', 738949),
('NV', '2020-06', 683280),
('NV', '2020-07', 661305),
('NV', '2020-08', 690829),
('NV', '2020-09', 633983),
('NV', '2020-10', 657713),
('NV', '2020-11', 655847),
('NV', '2020-12', 825628),
('NV', '2021-01', 823582),
('NV', '2021-02', 723389),
('NV', '2021-03', 797042),
('NV', '2021-04', 884270),
('NV', '2021-05', 950537),
('NV', '2021-06', 795128);

这是我计算过去2个月的脚本

SELECT billing, month as reporting_month,
SUM(SUM(usd)) OVER (PARTITION BY billing ORDER BY month ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) AS total_last_2m_usd   
FROM mytable
GROUP BY billing, month
ORDER BY billing, month;

从上面的脚本中，我可以使用total_last_2m (按计费分组)。现在，我坚持计算这两个月所有交易的总数。

例如，我在年份(2021)，

2021-01: total_last_2m_usd is calculated by (2020-11 + 2020-12) (grouping by billing)
2021-02: total_last_2m_usd is calculated by (2020-12 + 2021-01) (grouping by billing)
2021-03: total_last_2m_usd is calculated by (2021-01 + 2021-02) (grouping by billing)
.... below is another column that I want to calculate.
2021-01: total is calculated by (2020-11 + 2020-12) from all billing
2021-02: total is calculated by (2020-12 + 2021-01) from all billing
2021-03: total is calculated by (2021-01 + 2021-02) from all billing
....

如果你能帮我解决这个问题将不胜感激。

谢谢

Answer 1

在标准SQL中，您可以使用带间隔的range：

SELECT billing, month as reporting_month,
       SUM(SUM(usd)) OVER (PARTITION BY billing ORDER BY month ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) AS total_last_2m_usd,
       SUM(SUM(usd)) OVER (ORDER BY month RANGE BETWEEN INTERVAL '2 MONTH' PRECEDING AND INTERVAL '1 MONTH' PRECEDING)   
FROM mytable
GROUP BY billing, month
ORDER BY billing, month;

但是，并非所有数据库都支持此标准语法，或者INTERVAL常量的语法可能略有不同。]