實戰(zhàn) SQL：電商平臺銷售排行榜和飆升榜
不知道你有沒有注意過，在亞馬遜或者京東等電商平臺的網(wǎng)站上都提供了準實時的產(chǎn)品分類銷售排行榜。例如，以下就是亞馬遜上銷售排行榜和銷售飆升榜的一個截圖：
今天我們就來討論一下如何使用 SQL 排名窗口函數(shù)和取值窗口函數(shù)實現(xiàn)這類功能。
本文使用的函數(shù)和示例經(jīng)過以下數(shù)據(jù)庫驗證：MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。它們支持的常用排名窗口函數(shù)和取值窗口函數(shù)如下：
窗口函數(shù) | 描述 | MySQL | Oracle | SQL Server | PostgreSQL | SQLite |
ROW_NUMBER() | 為分區(qū)中的每行數(shù)據(jù)分配一個從 1 開始的序列號。 | ?? | ?? | ?? | ?? | ?? |
RANK() | 計算每行數(shù)據(jù)在分區(qū)中的名次，排名可能產(chǎn)生跳躍。 | ?? | ?? | ?? | ?? | ?? |
DENSE_RANK() | 計算每行數(shù)據(jù)在分區(qū)中的名次，排名不會產(chǎn)生跳躍。 | ?? | ?? | ?? | ?? | ?? |
PERCENT_RANK() | 計算每行數(shù)據(jù)在分區(qū)中的相對排名，取值為 (rank - 1) / (rows - 1)。 | ?? | ?? | ?? | ?? | ?? |
CUME_DIST() | 計算每行數(shù)據(jù)在分區(qū)內(nèi)的累積分布，取值范圍大于 0 且小于等于 1。 | ?? | ?? | ?? | ?? | ?? |
NTILE() | 將分區(qū)內(nèi)的數(shù)據(jù)分為 N 等份，計算每行數(shù)據(jù)所在的位置。 | ?? | ?? | ?? | ?? | ?? |
FIRST_VALUE() | 返回窗口內(nèi)第一行對應的數(shù)據(jù)。 | ?? | ?? | ?? | ?? | ?? |
LAST_VALUE() | 返回窗口內(nèi)最后一行對應的數(shù)據(jù)。 | ?? | ?? | ?? | ?? | ?? |
LAG() | 返回分區(qū)中在當前行之前第 N 行對應的數(shù)據(jù)。 | ?? | ?? | ?? | ?? | ?? |
LEAD() | 返回分區(qū)中在當前行之后第 N 行對應的數(shù)據(jù)。 | ?? | ?? | ?? | ?? | ?? |
NTH_VALUE() | 返回窗口內(nèi)第 N 行對應的數(shù)據(jù)。 | ?? | ?? | ? | ?? | ?? |
示例表和數(shù)據(jù)
本文使用以下簡化的示例表和數(shù)據(jù)（純屬虛擬，不代表實際銷量）：
create table products(
product_id integer not null primary key,
product_name varchar(100) not null unique,
product_subcategory varchar(100) not null,
product_category varchar(100) not null
);
insert into products values(1, 'iPhone 11', '手機', '手機通訊');
insert into products values(2, 'HUAWEI P40', '手機', '手機通訊');
insert into products values(3, '小米10', '手機', '手機通訊');
insert into products values(4, 'OPPO Reno4', '手機', '手機通訊');
insert into products values(5, 'vivo Y70s', '手機', '手機通訊');
insert into products values(6, '海爾BCD-216STPT', '冰箱', '大家電');
insert into products values(7, '康佳BCD-155C2GBU', '冰箱', '大家電');
insert into products values(8, '容聲BCD-529WD11HP', '冰箱', '大家電');
insert into products values(9, '美的BCD-213TM(E)', '冰箱', '大家電');
insert into products values(10, '格力BCD-230WETCL', '冰箱', '大家電');
insert into products values(11, '格力KFR-35GW', '空調', '大家電');
insert into products values(12, '美的KFR-35GW', '空調', '大家電');
insert into products values(13, 'TCLKFRd-26GW', '空調', '大家電');
insert into products values(14, '奧克斯KFR-35GW', '空調', '大家電');
insert into products values(15, '海爾KFR-35GW', '空調', '大家電');
create table sales(
product_id integer not null,
sale_time timestamp not null,
quantity integer not null
);
insert into sales
with recursive s(product_id, sale_time, quantity) as (
select product_id, '2020-07-23 00:01:00', floor(10*rand(0)) from products
union all
select product_id, sale_time + interval 1 minute, floor(10*rand(0))
from s
where sale_time < '2020-07-23 10:00:00'
)
select * from s;其中，products 是產(chǎn)品表，包含產(chǎn)品編號、產(chǎn)品名稱、產(chǎn)品子類和產(chǎn)品分類；sales 是銷量表，按照不同產(chǎn)品每分鐘統(tǒng)計一次銷量，我們生成了 2020 年 7 月 23 日 0 點到 10 點之間的模擬數(shù)據(jù)。
按照產(chǎn)品分類的銷售排行榜
對于銷售排行榜，我們需要按照產(chǎn)品的分類，計算最近一小時的銷量排名。假如用戶是 2020 年 7 月 23 日 10 點多查看排行榜，可以使用以下語句獲取不同分類下銷量排名前 3 的產(chǎn)品：
with hourly_sales(product_id, ymdh, quantity) as (
select product_id, date_format(sale_time, '%Y%m%d%H'), sum(quantity)
from sales
where sale_time between '2020-07-23 09:00:00' and '2020-07-23 09:59:00'
group by product_id, date_format(sale_time, '%Y%m%d%H')
),
hourly_rank as(
select product_category, product_subcategory, product_name, quantity,
rank() over (partition by ymdh, product_category order by quantity desc) as rk
from hourly_sales s
join products p on (p.product_id = s.product_id)
)
select *, repeat('??', 4- rk) as hotness
from hourly_rank
where rk <= 3
order by product_category, rk;
product_category|product_subcategory|product_name |quantity|rk|hotness|
----------------|-------------------|---------------|--------|--|-------|
大家電 |冰箱 |美的BCD-213TM(E)| 315| 1|?????? |
大家電 |空調 |海爾KFR-35GW | 293| 2|???? |
大家電 |冰箱 |康佳BCD-155C2GBU| 291| 3|?? |
手機通訊 |手機 |vivo Y70s | 298| 1|?????? |
手機通訊 |手機 |HUAWEI P40 | 273| 2|???? |
手機通訊 |手機 |iPhone 11 | 261| 3|?? |查詢返回了按照產(chǎn)品分類“大家電”和“手機通訊”顯示的 Top3 銷量產(chǎn)品。該查詢執(zhí)行的過程如下：
- 首先，通用表表達式 hourly_sales 是不同產(chǎn)品按照小時統(tǒng)計的銷量，我們只需要返回最新一小時的銷量（2020-07-23 09:00:00 到 2020-07-23 09:59:00 之間）；
- 然后，通用表表達式 hourly_rank 是基于 hourly_sales 計算的銷量排名；rank() 函數(shù)是一個排名窗口函數(shù)，over 子句表示按照小時和產(chǎn)品進行分區(qū)，并且按照銷量從到到低進行排序；join 用于關聯(lián)產(chǎn)品的信息；
- 最后，查詢 hourly_rank 并返回了每個產(chǎn)品分類中排名前 3 的產(chǎn)品，用于前端頁面顯示。
由于產(chǎn)品分類下面還存在子類，例如“大家電”可以分為“空調”和“冰箱”，我們可以進一步按照子類計算銷售排行榜：
with hourly_sales(product_id, ymdh, quantity) as (
select product_id, date_format(sale_time, '%Y%m%d%H'), sum(quantity)
from sales
where sale_time between '2020-07-23 09:00:00' and '2020-07-23 09:59:00'
group by product_id, date_format(sale_time, '%Y%m%d%H')
),
hourly_rank as(
select product_category, product_subcategory, product_name, quantity,
rank() over (partition by ymdh, product_category, product_subcategory order by quantity desc) as rk
from hourly_sales s
join products p on (p.product_id = s.product_id)
)
select *
from hourly_rank
where rk <= 3
order by product_category, product_subcategory, rk;
product_category|product_subcategory|product_name |quantity|rk|
----------------|-------------------|----------------|--------|--|
大家電 |冰箱 |美的BCD-213TM(E)| 315| 1|
大家電 |冰箱 |康佳BCD-155C2GBU| 291| 2|
大家電 |冰箱 |海爾BCD-216STPT | 259| 3|
大家電 |空調 |海爾KFR-35GW | 293| 1|
大家電 |空調 |格力KFR-35GW | 279| 2|
大家電 |空調 |美的KFR-35GW | 277| 3|
手機通訊 |手機 |vivo Y70s | 298| 1|
手機通訊 |手機 |HUAWEI P40 | 273| 2|
手機通訊 |手機 |iPhone 11 | 261| 3|該查詢只修改了 rank() 函數(shù) over 子句中的 partition by 分區(qū)選項，增加了 product_subcategory 字段。
除了 RANK() 函數(shù)之外，ROW_NUMBER() 和 DENSE_RANK() 函數(shù)也可以用于實現(xiàn)排名分析；它們的區(qū)別在于對排名相同的數(shù)據(jù)處理不同：
數(shù)據(jù) | ROW_NUMBER() | RANK() | DENSE_RANK() |
99 | 1 | 1 | 1 |
66 | 2 | 2 | 2 |
66 | 3 | 2 | 2 |
33 | 4 | 4 | 3 |
ROW_NUMBER() 返回的是不重復的編號；RANK() 對于相同的數(shù)據(jù)返回相同的排名，后續(xù)排名產(chǎn)生了跳躍；DENSE_RANK() 對于相同的數(shù)據(jù)返回相同的排名，后續(xù)排名沒有跳躍。
按照產(chǎn)品分類的銷量飆升榜
銷量飆升榜是指按照過去一段時間內(nèi)銷量名次的增長率進行排名，返回增長率最大的產(chǎn)品。
亞馬遜是按照過去 24 小時之內(nèi)的增長率進行計算，我們按照過去 1 小時之內(nèi)的增長率進行排名。也就是說，如果用戶在 2020 年 7 月 23 日 10 點多查看排行榜，使用 9 點到 10 點的銷量排名和 8 點到 9 點的銷量排名計算增長率：
with hourly_sales(product_id, ymdh, quantity) as (
select product_id, date_format(sale_time, '%y%m%d%H'), sum(quantity)
from sales
where sale_time between '2020-07-23 08:00:00' and '2020-07-23 09:59:00'
group by product_id, date_format(sale_time, '%y%m%d%H')
),
hourly_rank as(
select ymdh, product_category, product_subcategory, product_name,
rank() over (partition by ymdh, product_category order by quantity desc) as rk
from hourly_sales s
join products p on (p.product_id = s.product_id)
),
rank_gain as(
select product_category, product_subcategory, product_name,
rk, lag(rk, 1) over (partition by product_category, product_name order by ymdh) pre_rk,
100 * (ifnull(lag(rk, 1) over (partition by product_category, product_name order by ymdh), 99999999) - rk)
/rk as gain
from hourly_rank
),
top_gain as(
select *, rank() over (partition by product_category order by gain desc) gain_rk
from rank_gain
where pre_rk is not null
)
select product_category, product_subcategory, product_name, pre_rk, rk, concat(gain,'%') gain, gain_rk
from top_gain
where gain_rk <= 3
order by product_category, product_subcategory, gain desc;
product_category|product_subcategory|product_name |pre_rk|rk|gain |gain_rk|
----------------|-------------------|---------------|------|--|---------|-------|
大家電 |冰箱 |美的BCD-213TM(E)| 9| 1|800.0000%| 1|
大家電 |空調 |海爾KFR-35GW | 6| 2|200.0000%| 2|
大家電 |空調 |美的KFR-35GW | 10| 5|100.0000%| 3|
手機通訊 |手機 |vivo Y70s | 4| 1|300.0000%| 1|
手機通訊 |手機 |小米10 | 5| 5|0.0000% | 2|
手機通訊 |手機 |OPPO Reno4 | 3| 4|-25.0000%| 3|對于“大家電”類產(chǎn)品，“美的BCD-213TM(E)”冰箱的銷量排名從第 9 名提高到第 1 名，增長率為 800%，排在第一名。
該查詢執(zhí)行的過程如下：
- 首先，hourly_sales 是不同產(chǎn)品按照小時統(tǒng)計的銷量，包含了 2020-07-23 08:00:00 到 2020-07-23 09:59:00 之間兩個小時的銷量；
- 然后，hourly_rank 是基于 hourly_sales 計算的銷量排名；rank() 函數(shù)是一個排名窗口函數(shù)，over 子句表示按照小時和產(chǎn)品進行分區(qū)，并且按照銷量從到到低進行排序；join 用于關聯(lián)產(chǎn)品的信息；
- 接著，rank_gain 是基于 hourly_rank 計算的產(chǎn)品排名變化情況；lag(rk, 1) 函數(shù)返回的是同一產(chǎn)品前一行（對于 9 點到 10 點而言就是 8 點到 9 點）的銷量排名，并且基于該排名計算增長率（100 * (pre_rk - rk)/ rk）；
- 然后，top_gain 是基于 rank_gain 計算的不同分類中的產(chǎn)品增長率排名；這里我們再次使用了 rank() 函數(shù)；
- 最后，查詢 top_gain 并返回了每個產(chǎn)品分類中增長率排名前 3 的產(chǎn)品，用于前端頁面顯示。
以上示例中的 LAG(rk, 1) 函數(shù)也可以替換為 LEAD(rk ,-1)。另外，F(xiàn)IRST_VALUE()、LAST_VALUE() 以及 NTH_VALUE() 函數(shù)的作用比較明確，本文沒有進行演示。
總結
我們以電商平臺的銷售排行榜和銷售飆升榜為案例，介紹了一些常用的 SQL 排名窗口函數(shù)和取值窗口函數(shù)的使用。包括聚合窗口函數(shù)在內(nèi)的窗口函數(shù)為我們提供了強大的數(shù)據(jù)分析功能，值得我們每個人學習并熟練掌握。
