"oracle 数据库如何滚动累计求和，求当前月前 12 个月累计值，数据跨年，月份不连续？ 数据示例如下 month qty 2018-04 10 2018-05 10 2018-06 10 2018-07 10 2018-08 10 2018-09 10 2018-10 10 2018-11 10 ...."
oracle 数据库如何滚动累计求和，求当前月前 12 个月累计值，数据跨年，月份不连续？
数据示例如下
month qty
2018-04 10
2018-05 10
2018-06 10
2018-07 10
2018-08 10
2018-09 10
2018-10 10
2018-11 10
2018-12 10
2019-04 10
2019-05 10
2019-06 10
2019-07 10
2019-08 10
2019-09 10
2019-10 10
2019-11 10
2019-12 10
求 2019-04 月到 2019-06 的滚动累计求和？
select *
from (select year_month,
newMonth,
delivery_qty,
sum(delivery_qty) over(order by year_month rows between current row and 11 following) as total
from (select year_month,
to_char(add_months(to_date(year_month, ‘yyyy-mm’), 11),
‘yyyy-mm’) as newMonth,
delivery_qty
from stats_month_delivery
where to_date(year_month, ‘yyyy-mm’) >=
add_months(to_date(‘2019-02’, ‘yyyy-mm’), -11)
and to_date(year_month, ‘yyyy-mm’) <=
to_date(‘2019-03’, ‘yyyy-mm’)
order by year_month))
where to_date(newMonth, ‘yyyy-mm’) >= to_date(‘2019-02’, ‘yyyy-mm’)
and to_date(newMonth, ‘yyyy-mm’) <= to_date(‘2019-03’, ‘yyyy-mm’)
SELECT TT.MM,TT.S,TT1.S, TT.S - NVL(TT1.S, 0)
FROM (SELECT T.year_month as MM,
SUM(T.delivery_qty) OVER(ORDER BY T.year_month) S
FROM stats_month_delivery T
WHERE 1 = 1
)TT
LEFT JOIN (SELECT T.year_month as MM,
SUM(T.delivery_qty) OVER(ORDER BY T.year_month) S
FROM stats_month_delivery T
WHERE 1 = 1
) TT1
ON TO_DATE(TT.MM, ‘yyyy-MM’) =
ADD_MONTHS(TO_DATE(TT1.MM, ‘yyyy-MM’), 12)
SELECT TT.MM,TT.S,TT1.S, TT.S - NVL(TT1.S, 0)
FROM (SELECT T.year_month as MM,
SUM(T.delivery_qty) OVER(ORDER BY T.year_month) S
FROM stats_month_delivery T
WHERE 1 = 1
)TT
LEFT JOIN (SELECT T.year_month as MM,
SUM(T.delivery_qty) OVER(ORDER BY T.year_month) S
FROM stats_month_delivery T
WHERE 1 = 1
) TT1
ON TO_DATE(TT.MM, ‘yyyy-MM’) =
ADD_MONTHS(TO_DATE(TT1.MM, ‘yyyy-MM’), 12)