SQL累计3个月，含Yearmonth

Question

嗨，我有一个数据集，其中有帐户，年，月和贸易计数

account   Yearmonth   Trade_count
XXXXX       201701        1
XXXXX       201701        0
XXXXX       201702        1
XXXXX       201703        1
XXXXX       201704        1
XXXXX       201704        1
XXXXX       201705        1

如果交易已处理，则trade_count为1；如果未处理，则为0

我想要这样的输出

Account  Yearmonth    Total_Trades_Month     past_3_month trades
XXXXX      201703           1                       3
XXXXX      201704           2                       3
XXXXX      201705           1                       4

到目前为止，我已经尝试过：

select yearmonth, (yearmonth - 3) as 'ym',
 SUM(Case when COMMISSION is not null and Commission > 0 then Trade_Count Else 0 END) as 'TotalTrades',
 sum(CASE when yearmonth between (yearmonth - 3) and yearmonth and commission > 0 then Trade_Count else 0 end) as  'rolling'
 sum(Trade_Count)over(partition by yearmonth)
FROM WEALTHDB.DBO.WF_PM_DOR_DB 
group by  yearmonth
order by yearmonth

请注意，年、月只是一个整数，并未编码为日期。任何帮助我们都将不胜感激

Answer 1

如果您没有丢失数据的月份(如您的示例数据所示)，则可以执行以下操作：

select account, yearmonth, 
       sum(trade_count) as TotalTrades,
       sum(sum(trade_count)) over (partition by account order by yearmonth
                                   rows between 2 preceding and current row
                                  ) as past_3_month_trades
from WEALTHDB.DBO.WF_PM_DOR_DB 
group by account, yearmonth
order by yearmonth