我們在業(yè)務(wù)中常常需要統(tǒng)計(jì)這個(gè)月銷售量多少，同比增加多少，環(huán)比增加多少。這篇博文我們就看看如何利用窗口函數(shù)實(shí)現(xiàn)同比及環(huán)比的計(jì)算。

用到的關(guān)鍵字包括：lag, window

準(zhǔn)備工作

首先我們得有MySQL 8.0及以上版本, 然后我們準(zhǔn)備一張統(tǒng)計(jì)表。

CREATE TABLE `my_stat` (
  `month` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `profit` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

表里的內(nèi)容如下：

數(shù)據(jù) 插入的SQL

INSERT INTO `my_stat` (`month`, `profit`)
VALUES
  ('2022-05', 10.00),
  ('2022-06', 12.00),
  ('2022-07', 6.00),
  ('2022-08', 30.00),
  ('2022-09', 20.00),
  ('2022-10', 8.00),
  ('2022-11', 2.00),
  ('2022-12', 4.00),
  ('2023-01', 14.00),
  ('2023-02', 10.00),
  ('2023-03', 8.00),
  ('2023-04', 9.00),
  ('2023-05', 7.00),
  ('2023-06', 10.00),
  ('2023-07', 15.00);

環(huán)比計(jì)算

先看最終SQL及輸出結(jié)果

select `month`,profit,
 lag(profit) OVER w AS 上月,
 (profit - lag(profit) OVER w) as 環(huán)比,
 100*(profit - lag(profit) OVER w)/lag(profit) OVER w as 環(huán)比比例
 from my_stat
 WINDOW w AS (ORDER BY `month`);

輸出結(jié)果:

month|profit|上月|環(huán)比|環(huán)比比例
--|--|--|--|--
2022-05|10.00|NULL|NULL|NULL
2022-06|12.00|10.00|2.00|20.000000
2022-07|6.00|12.00|-6.00|-50.000000
2022-08|30.00|6.00|24.00|400.000000
2022-09|20.00|30.00|-10.00|-33.333333
2022-10|8.00|20.00|-12.00|-60.000000
2022-11|2.00|8.00|-6.00|-75.000000
2022-12|4.00|2.00|2.00|100.000000
2023-01|14.00|4.00|10.00|250.000000
2023-02|10.00|14.00|-4.00|-28.571429
2023-03|8.00|10.00|-2.00|-20.000000
2023-04|9.00|8.00|1.00|12.500000
2023-05|7.00|9.00|-2.00|-22.222222
2023-06|10.00|7.00|3.00|42.857143
2023-07|15.00|10.00|5.00|50.000000

lag

lag 函數(shù)的完整定義如下：

LAG(expr [, N[, default]]) [null_treatment] over_clause

• 參數(shù)一 expr（表達(dá)式）是必須的，這里可以是字段名，如上面的示例SQL，也可以是其它運(yùn)算表達(dá)式。
• 參數(shù)二 N 是可選的，必須是大于等于0的整數(shù)。默認(rèn)1，1表示上一行，0表示當(dāng)前行，12表示前12行，后面計(jì)算同比時(shí)會(huì)用到
• 參數(shù)三 是默認(rèn)值，比如第一行的 上一行是不存在的，這個(gè)時(shí)候 返回什么值就可以通過參數(shù)三來控制，默認(rèn)是 NULL
• null_treatment 的定義是處理NULL的策略，但是因?yàn)镸ySQL只實(shí)現(xiàn)了 RESPECT NULLS 并且作為默認(rèn)值，就感覺它只是提醒我們 計(jì)算結(jié)果需要考慮NULL

over_clause 是必須的，它描述窗口的定義， 如上面的SQL示例最后一行：

WINDOW w AS (ORDER BY `month`);

這里定義了一個(gè) 窗口(window), 基于 month這個(gè)字段升序。

window

上一節(jié)的 over_clause 定義如下：

over_clause:
    {OVER (window_spec) | OVER window_name}

我們看到，可以 over (窗口定義) 或者  over 窗口名稱。

lag(profit) OVER w AS 上月

這就是一個(gè) over 窗口名稱的示例。
接下來我們看 window_spec 的定義：

window_spec:
    [window_name] [partition_clause] [order_clause] [frame_clause]

• window_name 就是給窗口起個(gè)名字，方便使用
• partition_clause 分區(qū)定義, 就是定義查詢結(jié)果如何分組，有點(diǎn)類似group by 的意思。
• order_clause 排序定義,定義窗口內(nèi)容排序字段及方式，如前面示例里的 order by `month` 。如果省略了order by , 那就按查詢到結(jié)果的順序來排序。
• frame_clause frame_clause(框架子句)指定如何定義子集

同比環(huán)比計(jì)算

還是先看最終SQL

select `month`,profit,
 lag(profit) OVER w AS 上月,
 (profit - lag(profit) OVER w) as 環(huán)比,
 100*(profit - lag(profit) OVER w)/lag(profit) OVER w as 環(huán)比比例,
 lag(profit,12) OVER w AS 上年同月,
 (profit - lag(profit,12) OVER w) as 同比,
 100*(profit - lag(profit,12) OVER w)/lag(profit,12) OVER w as 同比比例
 from my_stat
 WINDOW w AS (ORDER BY `month`);

返回結(jié)果

同比與環(huán)比的差別其實(shí)不大，只是 lag(profit)變成了 lag(profit,12)。 lag(profit,12)表示取前12行的數(shù)據(jù)。

需要注意的是計(jì)算同比的時(shí)候，我們需要保證數(shù)據(jù)是連續(xù)的，不然數(shù)據(jù)會(huì)有偏差，因?yàn)檫@里的lag(profit,12)取的是前12行的數(shù)據(jù)，如果月份數(shù)據(jù)有缺失就可以取錯(cuò)數(shù)據(jù)。比如：如果少了一個(gè)月，那么前12行取的可能就是去年上個(gè)月的數(shù)據(jù)了。

partition_clause 聊聊分區(qū)

我們先準(zhǔn)備一下數(shù)據(jù)，先創(chuàng)建一張表 my_stat_food

CREATE TABLE `my_stat_food` (
  `month` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `profit` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

然后插入數(shù)據(jù)

INSERT INTO `my_stat_food` (`month`, `type`, `profit`)
VALUES
 ('2023-05', '蔬菜', 1.00),
 ('2023-05', '水果', 2.00),
 ('2023-05', '肉類', 3.00),
 ('2023-06', '蔬菜', 4.00),
 ('2023-06', '水果', 5.00),
 ('2023-06', '肉類', 6.00),
 ('2023-07', '蔬菜', 7.00),
 ('2023-07', '水果', 8.00),
 ('2023-07', '肉類', 9.00);

定義

partition_clause:
    PARTITION BY expr [, expr] ...

然后我們試試pratition的效果

select *,lag(`profit`) over (partition by `type`) as lp from my_stat_food

輸出：

我們的數(shù)據(jù)是按月分排序的，但我們看到通過 partition進(jìn)行分區(qū)后，就會(huì)分區(qū)來返回內(nèi)容了。這樣我們就可以通過分區(qū)來統(tǒng)計(jì)不同品類的環(huán)比。

問題

• 上面的給數(shù)據(jù)都是按月統(tǒng)計(jì)好的數(shù)據(jù)。但原始數(shù)據(jù)往往是一筆筆訂單，那么如何通過原始數(shù)據(jù)生成每個(gè)月的統(tǒng)計(jì)數(shù)據(jù)呢？
• 如何保證數(shù)據(jù)的連續(xù)性？在業(yè)務(wù)上每個(gè)月的數(shù)據(jù)往往是有保證的，畢竟一個(gè)月一單不成交的話，也沒有統(tǒng)計(jì)的必要了。但是考慮到天呢？可以一些小店一天一個(gè)成交也沒有，也是正常的。那么如何實(shí)現(xiàn)如果某天沒有數(shù)據(jù)，統(tǒng)計(jì)的時(shí)候讓那天的統(tǒng)計(jì)數(shù)據(jù)變成0呢？

參考

https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

到此這篇關(guān)于MySQL8.0實(shí)現(xiàn)窗口函數(shù)計(jì)算同比環(huán)比的文章就介紹到這了,更多相關(guān)MySQL 窗口函數(shù)計(jì)算內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論