SQL业务场景：如何计算同比和环比？

1.题目背景

根据按年月统计的销售总值，计算环比（销售总额同比上期）、同比（销售总额同比去年同期）

【说明：本例子中所用数据为22年1、2、3月和23年1、2、3月，所以对应的答案跟实际业务处理有所不同，但是最后也写了正常12个月版本的答案】

2.数据准备

CREATE TABLE `food_orders_info` (
  `id` varchar(36) NOT NULL COMMENT 'id',
  `order_money` decimal(10,2) NOT NULL COMMENT '订单金额',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立时间（下单时间）',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='订单表';
-- 插入数据
INSERT INTO `food_orders_info` (`id`,`order_money`, `create_time`)
VALUES
VALUES
('1', 100.00, '2022-01-05'),
('2', 150.00, '2022-01-15'),
('3', 200.00, '2022-01-25'),
('4', 250.00, '2022-01-30'),
('5', 300.00, '2022-02-05'),
('6', 350.00, '2022-02-15'),
('7', 400.00, '2022-02-25'),
('8', 450.00, '2022-02-28'),
('9', 500.00, '2022-03-05'),
('10', 550.00, '2022-03-15'),
('11', 600.00, '2022-03-25'),
('12', 650.00, '2022-03-30'),
('13', 110.00, '2023-01-05'),
('14', 160.00, '2023-01-15'),
('15', 210.00, '2023-01-25'),
('16', 260.00, '2023-01-30'),
('17', 310.00, '2023-02-05'),
('18', 360.00, '2023-02-15'),
('19', 410.00, '2023-02-25'),
('20', 460.00, '2023-02-28'),
('21', 510.00, '2023-03-05'),
('22', 560.00, '2023-03-15'),
('23', 610.00, '2023-03-25'),
('24', 660.00, '2023-03-30');

3.答题 

• 最后一层子查询：

  • 使用DATE_FORMAT(create_time, '%Y-%m')获取每个订单的年月信息，并计算每个月的总销售额。

• 中间子查询：

  • 利用LAG(ttl_mon, 12)来获取前一年的同月销售额。
  • 用来LAG(ttl_mon, 1)获取前一个月的收入。

• 最外层查询：

  • 计算一个和一个环比，并统计为一个百分点。

--针对该数据版本
SELECT
    year_mon,
    ttl_mon,
    a1 AS previous_year_sales,
    ROUND((ttl_mon - a1) / ABS(a1) * 100, 2) AS year_over_year_growth,
    a3 AS previous_month_sales,
    ROUND((ttl_mon - a3) / ABS(a3) * 100, 2) AS month_over_month_growth
FROM (
    SELECT
        year_mon,
        ttl_mon,
        LAG(ttl_mon, 3) OVER (ORDER BY year_mon) AS a1,  -- 前一年的同月
        LAG(ttl_mon, 1) OVER (PARTITION BY year ORDER BY year_mon) AS a3   -- 前一个月
    FROM (
        SELECT 
            DATE_FORMAT(create_time, '%Y') AS year,
            DATE_FORMAT(create_time, '%Y-%m') AS year_mon,
            SUM(order_money) AS ttl_mon
        FROM food_orders_info
        GROUP BY year, year_mon
    ) t1
) t2
ORDER BY year_mon;


--正常12个月份版本
SELECT
		--计算同比（与去年同月相比的增长率）和环比（与上个月相比的增长率）增长
    year_mon,
    ttl_mon,
    a1 AS previous_year_sales,
		--使用 ROUND 函数计算同比和环比增长率，并格式化为百分比
    ROUND((ttl_mon - a1) / ABS(a1) * 100, 2) AS year_over_year_growth,
    a3 AS previous_month_sales,
    ROUND((ttl_mon - a3) / ABS(a3) * 100, 2) AS month_over_month_growth
FROM (
    SELECT
        year_mon,
        ttl_mon,
        LAG(ttl_mon, 12) OVER (ORDER BY year_mon) AS a1,
        LAG(ttl_mon, 1) OVER (ORDER BY year_mon) AS a3
    FROM (
				--按年月统计销售额
        SELECT 
            DATE_FORMAT(create_time, '%Y-%m') AS year_mon,
            SUM(order_money) AS ttl_mon
        FROM food_orders_info
        GROUP BY year_mon
    ) t1
) t2
ORDER BY year_mon;

4.举一反三——季度同比&环比 

• 最里层子查询 (t1)：

  • 使用 YEAR(create_time) 和 QUARTER(create_time) 获取每条订单的年份和季度信息。
  • 利用 SUM(order_money) 计算每个季度的总销售额。
  • 将季度信息和销售总额分组汇总。

• 中间子查询 (t2)：

  • 使用 LAG 函数来获取前一年的同一季度销售额和前一个季度的销售额。
  • LAG(ttl_quarter, 4) 表示获取前四个季度的销售额（即前一年同季度）。
  • LAG(ttl_quarter, 1) 表示获取前一个季度的销售额。

• 最外层查询：

  • 展示当前季度的销售总额、前一年同季度的销售总额以及前一个季度的销售总额。
  • 计算同比增长率：(当前季度销售额 - 前一年同季度销售额) / 前一年同季度销售额 * 100，并使用 ROUND 函数将结果保留两位小数。
  • 计算环比增长率：(当前季度销售额 - 前一个季度销售额) / 前一个季度销售额 * 100，并保留两位小数。

SELECT
    year_quarter,
    ttl_quarter,
    a1 AS previous_year_quarter_sales,
    ROUND((ttl_quarter - a1) / ABS(a1) * 100, 2) AS year_over_year_growth,
    a3 AS previous_quarter_sales,
    ROUND((ttl_quarter - a3) / ABS(a3) * 100, 2) AS quarter_over_quarter_growth
FROM (
    SELECT
        year_quarter,
        ttl_quarter,
        LAG(ttl_quarter, 4) OVER (ORDER BY year_quarter) AS a1,  -- 前一年的同季度
        LAG(ttl_quarter, 1) OVER (ORDER BY year_quarter) AS a3   -- 前一个季度
    FROM (
        SELECT 
            CONCAT(YEAR(create_time), '-', QUARTER(create_time)) AS year_quarter,
            SUM(order_money) AS ttl_quarter
        FROM food_orders_info
        GROUP BY year_quarter
    ) t1
) t2
ORDER BY year_quarter;