顺子の杂货铺
生命不息，折腾不止，且行且珍惜~
        找回密码
当前位置：順子の杂货铺 > BigData > 正文

03-环比同比分析

2026-01-18 分类：BigData / Database / SQL 阅读(7) 评论(0)
DMIT VPS

环比同比分析

环比（MoM, Month-on-Month）和同比（YoY, Year-on-Year）是数据分析中最常用的时间对比方法，用于识别趋势变化。

📊 支持情况

数据库支持情况备注
MySQL✅ 完整支持日期函数丰富
Oracle✅ 完整支持LEAD/LAG语法
ClickHouse✅ 完整支持日期函数略有不同
Hologres✅ 完整支持兼容PG语法
MaxCompute✅ 完整支持支持窗口函数
Hive✅ 完整支持支持窗口函数

1. 环比分析 (MoM – Month on Month)

核心思路

使用 LAG() 函数获取上一期数据，然后计算增长率

MySQL / Hive / MaxCompute / Hologres

SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month_rev,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month))
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100,
        2
    ) AS mom_pct
FROM monthly_revenue;

Oracle

SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month_rev,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month))
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100,
        2
    ) AS mom_pct
FROM monthly_revenue;

ClickHouse

SELECT
    month,
    revenue,
    lagInFrame(revenue) OVER (ORDER BY month) AS prev_month_rev,
    ROUND(
        (revenue - lagInFrame(revenue)) / NULLIF(lagInFrame(revenue), 0) * 100,
        2
    ) AS mom_pct
FROM monthly_revenue;

2. 同比分析 (YoY – Year on Year)

核心思路

使用 LAG(col, n) 往前推n期获取去年同期数据

MySQL

SELECT
    month,
    revenue,
    LAG(revenue, 12) OVER (ORDER BY month) AS same_month_last_year,
    ROUND(
        (revenue - LAG(revenue, 12) OVER (ORDER BY month))
        / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0) * 100,
        2
    ) AS yoy_pct
FROM monthly_revenue;

Oracle

SELECT
    month,
    revenue,
    LAG(revenue, 12) OVER (ORDER BY month) AS same_month_last_year,
    ROUND(
        (revenue - LAG(revenue, 12) OVER (ORDER BY month))
        / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0) * 100,
        2
    ) AS yoy_pct
FROM monthly_revenue;

Hive / MaxCompute

SELECT
    month,
    revenue,
    LAG(revenue, 12, 0) OVER (ORDER BY month) AS same_month_last_year,
    ROUND(
        (revenue - LAG(revenue, 12, 0) OVER (ORDER BY month))
        / NULLIF(LAG(revenue, 12, 0) OVER (ORDER BY month), 0) * 100,
        2
    ) AS yoy_pct
FROM monthly_revenue;

ClickHouse

SELECT
    month,
    revenue,
    lagInFrame(revenue, 12, 0) OVER (ORDER BY month) AS same_month_last_year,
    ROUND(
        (revenue - lagInFrame(revenue, 12)) / NULLIF(lagInFrame(revenue, 12), 0) * 100,
        2
    ) AS yoy_pct
FROM monthly_revenue;

3. 环比+同比组合查询

MySQL / Hive

SELECT
    month,
    revenue,
    LAG(revenue) OVER w AS prev_month,
    LAG(revenue, 12) OVER w AS same_month_ly,
    -- 环比
    ROUND(
        (revenue - LAG(revenue) OVER w)
        / NULLIF(LAG(revenue) OVER w, 0) * 100, 2
    ) AS mom_pct,
    -- 同比
    ROUND(
        (revenue - LAG(revenue, 12) OVER w)
        / NULLIF(LAG(revenue, 12) OVER w, 0) * 100, 2
    ) AS yoy_pct
FROM monthly_revenue
WINDOW w AS (ORDER BY month);

Oracle

SELECT
    TO_CHAR(stat_month, 'YYYY-MM') AS month,
    revenue,
    LAG(revenue) OVER (ORDER BY stat_month) AS prev_month,
    LAG(revenue, 12) OVER (ORDER BY stat_month) AS same_month_ly,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY stat_month))
        / NULLIF(LAG(revenue) OVER (ORDER BY stat_month), 0) * 100, 2
    ) AS mom_pct,
    ROUND(
        (revenue - LAG(revenue, 12) OVER (ORDER BY stat_month))
        / NULLIF(LAG(revenue, 12) OVER (ORDER BY stat_month), 0) * 100, 2
    ) AS yoy_pct
FROM monthly_revenue;

4. 日维度环比同比

日环比（前一天）

-- MySQL
SELECT
    DATE(sale_date) AS day,
    sales,
    LAG(sales) OVER (ORDER BY sale_date) AS prev_day,
    ROUND(
        (sales - LAG(sales) OVER (ORDER BY sale_date))
        / NULLIF(LAG(sales) OVER (ORDER BY sale_date), 0) * 100, 2
    ) AS dod_pct
FROM daily_sales;

日同比（去年同一天）

SELECT
    sale_date,
    sales,
    LAG(sales, 365) OVER (ORDER BY sale_date) AS same_day_last_year,
    ROUND(
        (sales - LAG(sales, 365) OVER (ORDER BY sale_date))
        / NULLIF(LAG(sales, 365) OVER (ORDER BY sale_date), 0) * 100, 2
    ) AS yoy_pct
FROM daily_sales;

5. 季度环比同比

按季度统计

-- MySQL
SELECT
    CONCAT(YEAR(stat_date), '-Q', QUARTER(stat_date)) AS quarter,
    revenue,
    LAG(revenue) OVER (ORDER BY stat_date) AS prev_quarter,
    LAG(revenue, 4) OVER (ORDER BY stat_date) AS same_quarter_ly,
    ROUND((revenue - LAG(revenue) OVER w) / NULLIF(LAG(revenue) OVER w, 0) * 100, 2) AS qoq_pct,
    ROUND((revenue - LAG(revenue, 4) OVER w) / NULLIF(LAG(revenue, 4) OVER w, 0) * 100, 2) AS yoy_pct
FROM quarterly_revenue
WINDOW w AS (ORDER BY stat_date);

Oracle

SELECT
    TO_CHAR(stat_date, 'YYYY') || '-Q' || TO_CHAR(stat_date, 'Q') AS quarter,
    revenue,
    LAG(revenue) OVER (ORDER BY stat_date) AS prev_quarter,
    LAG(revenue, 4) OVER (ORDER BY stat_date) AS same_quarter_ly,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY stat_date))
        / NULLIF(LAG(revenue) OVER (ORDER BY stat_date), 0) * 100, 2
    ) AS qoq_pct,
    ROUND(
        (revenue - LAG(revenue, 4) OVER (ORDER BY stat_date))
        / NULLIF(LAG(revenue, 4) OVER (ORDER BY stat_date), 0) * 100, 2
    ) AS yoy_pct
FROM quarterly_revenue;

6. 多维度环比同比

按地区+产品分组

SELECT
    region,
    product,
    month,
    sales,
    LAG(sales) OVER (
        PARTITION BY region, product
        ORDER BY month
    ) AS prev_month_sales,
    LAG(sales, 12) OVER (
        PARTITION BY region, product
        ORDER BY month
    ) AS same_month_ly,
    ROUND(
        (sales - LAG(sales) OVER (PARTITION BY region, product ORDER BY month))
        / NULLIF(LAG(sales) OVER (PARTITION BY region, product ORDER BY month), 0) * 100, 2
    ) AS mom_pct,
    ROUND(
        (sales - LAG(sales, 12) OVER (PARTITION BY region, product ORDER BY month))
        / NULLIF(LAG(sales, 12) OVER (PARTITION BY region, product ORDER BY month), 0) * 100, 2
    ) AS yoy_pct
FROM monthly_sales_by_region_product;

7. 进阶：使用JOIN实现环比同比

使用自连接（MySQL）

SELECT
    t1.month,
    t1.revenue AS curr_rev,
    t2.revenue AS prev_rev,
    t3.revenue AS same_month_ly,
    ROUND((t1.revenue - t2.revenue) / NULLIF(t2.revenue, 0) * 100, 2) AS mom_pct,
    ROUND((t1.revenue - t3.revenue) / NULLIF(t3.revenue, 0) * 100, 2) AS yoy_pct
FROM monthly_revenue t1
LEFT JOIN monthly_revenue t2
    ON DATE_SUB(t1.month, INTERVAL 1 MONTH) = t2.month
LEFT JOIN monthly_revenue t3
    ON DATE_SUB(t1.month, INTERVAL 1 YEAR) = t3.month;

使用DATE函数（Oracle）

SELECT
    t1.month,
    t1.revenue AS curr_rev,
    t2.revenue AS prev_rev,
    t3.revenue AS same_month_ly,
    ROUND((t1.revenue - t2.revenue) / NULLIF(t2.revenue, 0) * 100, 2) AS mom_pct,
    ROUND((t1.revenue - t3.revenue) / NULLIF(t3.revenue, 0) * 100, 2) AS yoy_pct
FROM monthly_revenue t1
LEFT JOIN monthly_revenue t2
    ON ADD_MONTHS(t1.month, -1) = t2.month
LEFT JOIN monthly_revenue t3
    ON ADD_MONTHS(t1.month, -12) = t3.month;

8. 环比同比可视化数据准备

月度销售趋势表

WITH monthly_sales AS (
    SELECT
        DATE_FORMAT(sale_date, 'yyyy-MM') AS month,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY DATE_FORMAT(sale_date, 'yyyy-MM')
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    LAG(revenue, 12) OVER (ORDER BY month) AS same_month_last_year,
    -- 环比增长率
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month))
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 2
    ) AS mom_growth_pct,
    -- 同比增长率
    ROUND(
        (revenue - LAG(revenue, 12) OVER (ORDER BY month))
        / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0) * 100, 2
    ) AS yoy_growth_pct,
    -- 环比增量
    revenue - LAG(revenue) OVER (ORDER BY month) AS mom_delta,
    -- 同比增量
    revenue - LAG(revenue, 12) OVER (ORDER BY month) AS yoy_delta
FROM monthly_sales
ORDER BY month;

⚠️ 注意事项

1. NULL值处理

-- 开头几期没有同期/环比数据，会返回NULL
-- 使用 COALESCE 或 IFNULL 处理
SELECT
    month,
    revenue,
    COALESCE(
        ROUND((revenue - prev_rev) / NULLIF(prev_rev, 0) * 100, 2),
        0
    ) AS mom_pct
FROM (
    SELECT
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) AS prev_rev
    FROM monthly_revenue
) t;

2. 除零保护

-- 必须使用 NULLIF 避免除零错误
-- ❌ 错误：/ prev_rev
-- ✅ 正确：/ NULLIF(prev_rev, 0)

3. 日期类型

-- 确保日期字段是日期类型，否则LAG可能不准确
-- 转换示例（MySQL）
SELECT
    CAST(sale_date AS DATE) AS sale_date,
    ...
FROM ...

📝 练习题

题目：电商销售数据分析

-- 建表
CREATE TABLE ecom_sales (
    id INT,
    stat_date DATE,
    category STRING,
    region STRING,
    sales_amount DECIMAL(12,2)
);

-- 插入数据
INSERT INTO ecom_sales VALUES
(1, '2023-01-01', '电子产品', '华东', 50000),
(2, '2023-02-01', '电子产品', '华东', 45000),
(3, '2023-03-01', '电子产品', '华东', 60000),
(4, '2023-01-01', '服装', '华东', 30000),
(5, '2023-02-01', '服装', '华东', 35000),
(6, '2023-03-01', '服装', '华东', 40000),
(7, '2022-03-01', '电子产品', '华东', 48000),
(8, '2022-03-01', '服装', '华东', 28000);

要求：

  1. 计算每个类别、地区的月度环比增长率
  2. 计算每个类别、地区的同比增长率
  3. 标记环比增长/下降（正/负）

参考SQL：

SELECT
    category,
    region,
    stat_date,
    sales_amount,
    prev_month,
    same_month_ly,
    ROUND((sales_amount - prev_month) / NULLIF(prev_month, 0) * 100, 2) AS mom_pct,
    ROUND((sales_amount - same_month_ly) / NULLIF(same_month_ly, 0) * 100, 2) AS yoy_pct,
    CASE
        WHEN sales_amount > prev_month THEN '↑'
        WHEN sales_amount < prev_month THEN '↓'
        ELSE '-'
    END AS mom_trend
FROM (
    SELECT
        category,
        region,
        stat_date,
        sales_amount,
        LAG(sales_amount) OVER (
            PARTITION BY category, region
            ORDER BY stat_date
        ) AS prev_month,
        LAG(sales_amount, 12) OVER (
            PARTITION BY category, region
            ORDER BY stat_date
        ) AS same_month_ly
    FROM ecom_sales
) t
ORDER BY category, region, stat_date;
赞(0)
未经允许不得转载：順子の杂货铺 » 03-环比同比分析
标签：
Avatar photo

shunzi

一半忧伤，一半明媚！喜欢看书，听歌，写字，打游戏，交朋友！
搬瓦工VPS

相关推荐

评论 抢沙发

Avatar photo

取消

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
吐血推荐

New

热门文章

热门标签

sql (48)mysql (22)oracle (21)python (15)Hive (13)MaxCompute (13)Hologres (13)ClickHouse (12)rm (6)password (5)port (5)install (4)linux (4)log (4)join (3)pos (3)google (3)vi (3)tab (3)redis (3)deepin (3)htaccess (2)master (2)centos (2)kangle (2)easypanel (2)list (2)django (2)plsql (2)docker (2)

网站统计

  • 日志总数：88
  • 评论总数：55
  • 标签总数：125
  • 最后更新：2026-01-18

分享创造快乐

联系我们联系我们