SQL-经典的环比同比问题

老生常谈，指标不是孤立的，必须有对比才有意义，环比和同比就是将当前的指标值与过往的指标值进行对比，从而表明当前的指标值是好是坏。环比是跟上一个周期比(周期通常是月或周)，同比是跟历史同期比(周期通常是年)，区分环比和同比主要看时间周期是否连续，比如，今天比昨天、本周比上周、本月比上月都是环比，本周二比上周二、2021年3月比2019年3月都是同比，2020年全年比2019年全年既是同比也是环比，通常称为同比。

理清楚环比同比的作用和概念后，我们再来看看 SQL环比同比 的实现。假设我们已有如下的销售明细表sales：

• 统计每类产品的每月销售总额并计算环比同比：

示例代码：

--方法1：计算环比同比所对应的周期, 然后匹配计算
with
--1：按产品+月份分组汇总
    tmp1 as (
        select
            product,
            date_trunc('month', order_date) as order_month,
            sum(amount) as amount
        from sales
        group by 1,2
    ),

--2：计算环比同比所对应的月份
    tmp2 as (
        select
            product,
            order_month,
            date_trunc('month', order_month - interval '1' day) as bef_1M_month,
            order_month - interval '12' month as bef_1Y_month,
            amount
        from tmp1
    ),

--3：匹配环比同比所对应的值
    tmp3 as (
        select
            t1.product,
            t1.order_month,
            t1.amount,
            t2.amount as bef_1M_month_amount,
            t3.amount as bef_1Y_month_amount
        from tmp2 as t1
        left join tmp1 as t2
        on t1.product = t2.product and t1.bef_1M_month = t2.order_month
        left join tmp1 as t3
        on t1.product = t3.product and t1.bef_1Y_month = t3.order_month
    )

--4：计算环比同比
select
    product,
    order_month,
    amount,
    round((amount - bef_1M_month_amount)/bef_1M_month_amount,4) as ratio_month_on_month,
    round((amount - bef_1Y_month_amount)/bef_1Y_month_amount,4) as ratio_year_on_year
from tmp3
order by 1,2;



--方法2：使用lag()窗口函数获取环比同比所对应的值,然后计算
--仅当数据中的时间周期是连续的不缺失的时候才可使用此方法
with
--1：按产品+月份分组汇总
    tmp1 as (
        select
            product,
            date_trunc('month', order_date) as order_month,
            sum(amount) as amount
        from sales
        group by 1,2
    ),
 
--2：使用lag()窗口函数获取环比同比所对应的值
    tmp2 as (
        select
            product,
            order_month,
            amount,
            lag(amount,1) over (partition by product order by order_month) as bef_1M_month_amount,
            lag(amount,12) over (partition by product order by order_month) as bef_1Y_month_amount
        from tmp1
    )

--3：计算环比同比
select
    product,
    order_month,
    amount,
    round((amount - bef_1M_month_amount)/bef_1M_month_amount,4) as ratio_month_on_month,
    round((amount - bef_1Y_month_amount)/bef_1Y_month_amount,4) as ratio_year_on_year
from tmp2
order by 1,2;

代码结果：

以上就是 SQL环比同比 的实现，使用Presto SQL执行，其他SQL在日期处理上可能会有差异。 能够看出，方法1的通用性更强，在周期是连续的不缺失的情况下，方法2更简洁高效，我们可视源数据情况选用方法1或方法2。 为便于展示处理逻辑，示例代码以分步的形式进行处理，实际取数时可以对中间的步骤进行合并，以便提高效率。