SELECT MONTH
( pay_time ),
sum( pay_amount )
user_order
WHERE
YEAR ( pay_time ) = 2021
GROUP BY
MONTH ( pay_time );
第三步：在第二步的基础上应用窗口函数实现需求
SELECT
a.MONTH AS `month`,-- 月份
a.pay_amount,-- 当月总支付金额
sum( a.pay_amount ) over ( ORDER BY a.MONTH ) AS total_amount
SELECT MONTH
( pay_time ) `month`,
sum( pay_amount ) pay_amount
user_order
WHERE
YEAR ( pay_time ) = 2021
GROUP BY
MONTH ( pay_time )
需求2：查询出2020-2021年每月的支付总额和当年累计支付总额
第一步：根据年和月进行group by分组,得到2020和2021年每月支付总额
SELECT YEAR
( pay_time ) YEAR,
MONTH ( pay_time ) MONTH,
sum( pay_amount ) pay_amount
user_order
WHERE
YEAR ( pay_time ) IN ( 2020, 2021 )
GROUP BY
YEAR ( pay_time ),
MONTH ( pay_time )
第二步：通过sum()over()窗口函数得到累计支付总额
SELECT
a.YEAR,
a.MONTH,
a.pay_amount,
sum( a.pay_amount ) over ( PARTITION BY a.YEAR ORDER BY a.MONTH ) total_amount
SELECT YEAR
( pay_time ) YEAR,
MONTH ( pay_time ) MONTH,
sum( pay_amount ) pay_amount
user_order
WHERE
YEAR ( pay_time ) IN ( 2020, 2021 )
GROUP BY
YEAR ( pay_time ),
MONTH ( pay_time )
2.2 avg() over():移动平均函数
需求3：查询出2021年每个月的近三月移动平均支付金额
SELECT
a.MONTH,
a.pay_amount,
avg( a.pay_amount ) over ( ORDER BY a.MONTH rows BETWEEN 2 preceding AND current ROW ) AS avg_amount
SELECT MONTH
( pay_time ) `month`,
sum( pay_amount ) pay_amount
user_order
WHERE
YEAR ( pay_time ) = 2021
GROUP BY
MONTH ( pay_time )
SELECT
a.mon,
a.pay_amounts,
max( a.pay_amounts ) over ( ORDER BY a.mon rows BETWEEN 3 preceding AND CURRENT ROW ) max_pay_amount
SELECT
SUBSTRING( pay_time, 1, 7 ) mon,
round( sum( pay_amount ), 2 ) pay_amounts
user_order
GROUP BY
SUBSTRING( pay_time, 1, 7 )
Supersist
MySQL
