数据分析SQL环比增长率、同比增长率

概念

环比

• month-on-month ratio
• 连续2个统计周期（比如连续两月）内的量的变化比

环比增长率

• chain growth rate
• 和上期相比较的增长率
• $环比增长率=(本期数-上期数)÷上期数\times 100\%$
  或
  $环比增长率=(本期数÷上期数-1)\times 100\%$

同比

• on year-on-year basis
  一般情况下是本年第n月与去年第n月比

同比增长率

• YoY+%
• 一般是指和去年同期相比较的增长率
• $同比增长率=(当年的指标值-去年同期的值)÷去年同期的值\times 100\%$
  或
  $同比增长率=(当年的指标值÷去年同期的值-1)\times 100\%$

SQL（HIVE）

造数据

存在0以及缺失值的情况

CREATE VIEW sale(ym,sales) AS
SELECT '2020-01',1000 UNION ALL
SELECT '2020-02',800 UNION ALL
SELECT '2020-03',1200 UNION ALL
SELECT '2020-04',0 UNION ALL
SELECT '2020-05',1400 UNION ALL
SELECT '2020-06',1500 UNION ALL
SELECT '2020-07',NULL UNION ALL
SELECT '2020-08',1700 UNION ALL
SELECT '2020-09',1800 UNION ALL
SELECT '2020-10',1900 UNION ALL
SELECT '2020-11',3000 UNION ALL
SELECT '2020-12',2000 UNION ALL
SELECT '2021-01',1200 UNION ALL
SELECT '2021-02',1000 UNION ALL
SELECT '2021-03',1400 UNION ALL
SELECT '2021-04',800 UNION ALL
SELECT '2021-05',1300 UNION ALL
SELECT '2021-06',1200 UNION ALL
SELECT '2021-07',900 UNION ALL
SELECT '2021-08',700 UNION ALL
SELECT '2021-09',1000 UNION ALL
SELECT '2021-10',1800 UNION ALL
SELECT '2021-11',3200 UNION ALL
SELECT '2021-12',1500;

环比

SELECT
    ym,
    sales/LAG(sales,1,NULL) OVER (ORDER BY ym)-1 AS chain_growth_rate
FROM sale;

同比

SELECT
    ym,
    sales/LAG(sales,12,NULL) OVER (ORDER BY ym)-1 AS year_on_year_growth_rate
FROM sale;