MySQL如何计算月度订单总和对比的金额下降百分比

需求背景 #

从customer_order表中计算相邻两个月的订单金额环比下降百分比，原有查询仅支持按月统计订单总金额，无法直接输出百分比结果。

原有查询语句 #

select extract(MONTH from timestamp) as month,sum(order_value) as total_value 
from customer_order 
group by month;

计算规则 #

月度下降百分比 =（前序月份订单总金额 - 当前月份订单总金额）/ 前序月份订单总金额 * 100

测试样例数据 #

order_id    customer_id order_value timestamp
101         501         25080.50    2020-09-01 10:00:00
102         502         12055.00    2020-09-12 12:00:00
103         503         1500.50     2020-09-25 16:00:00
104         502         1005.00     2020-10-05 12:00:00
105         501         1200.00     2020-10-18 16:00:00
106         503         15000.00    2020-10-30 16:00:00

实现逻辑 #

• 第一步：按年+月维度聚合订单总金额，避免不同年份同月份数据混淆
• 第二步：用窗口函数LAG()取排序后上一行（即上一个月）的总金额作为计算基准
• 第三步：代入下降百分比公式计算，结果保留2位小数

可直接运行的SQL代码 #

WITH monthly_agg AS (
    SELECT
        CONCAT(EXTRACT(YEAR FROM timestamp), '-', LPAD(EXTRACT(MONTH FROM timestamp),2,'0')) AS stat_month,
        EXTRACT(YEAR FROM timestamp) AS y,
        EXTRACT(MONTH FROM timestamp) AS m,
        SUM(order_value) AS month_total
    FROM customer_order
    GROUP BY y, m, stat_month
)
SELECT
    stat_month AS 统计月份,
    month_total AS 当月订单总额,
    LAG(month_total) OVER (ORDER BY y, m) AS 上月订单总额,
    ROUND(
        (LAG(month_total) OVER (ORDER BY y, m) - month_total) 
        / LAG(month_total) OVER (ORDER BY y, m) * 100,
        2
    ) AS 环比下降百分比
FROM monthly_agg
-- 过滤掉没有上月数据的首行可打开下面注释
-- WHERE LAG(month_total) OVER (ORDER BY y, m) IS NOT NULL
;

样例数据计算结果 #

针对给出的测试数据，最终计算结果：

• 2020-09订单总额38636.00，无上月数据，环比值为空
• 2020-10订单总额17205.00，较9月下降55.47%
  提示：如果使用不支持窗口函数的老旧SQL版本，可以通过自关联月度聚合表、匹配月份差值为1的上月数据实现相同计算效果。

内容的提问来源于stack exchange，提问作者user2222