實戰 SQL：電商平臺銷售排行榜和飆升榜
不知道你有沒有注意過，在亞馬遜或者京東等電商平臺的網站上都提供了準實時的產品分類銷售排行榜。例如，以下就是亞馬遜上銷售排行榜和銷售飆升榜的一個截圖：
今天我們就來討論一下如何使用 SQL 排名窗口函數和取值窗口函數實現這類功能。
本文使用的函數和示例經過以下數據庫驗證：MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。它們支持的常用排名窗口函數和取值窗口函數如下：
窗口函數 | 描述 | MySQL | Oracle | SQL Server | PostgreSQL | SQLite |
ROW_NUMBER() | 為分區中的每行數據分配一個從 1 開始的序列號。 | ?? | ?? | ?? | ?? | ?? |
RANK() | 計算每行數據在分區中的名次，排名可能產生跳躍。 | ?? | ?? | ?? | ?? | ?? |
DENSE_RANK() | 計算每行數據在分區中的名次，排名不會產生跳躍。 | ?? | ?? | ?? | ?? | ?? |
PERCENT_RANK() | 計算每行數據在分區中的相對排名，取值為 (rank - 1) / (rows - 1)。 | ?? | ?? | ?? | ?? | ?? |
CUME_DIST() | 計算每行數據在分區內的累積分布，取值范圍大于 0 且小于等于 1。 | ?? | ?? | ?? | ?? | ?? |
NTILE() | 將分區內的數據分為 N 等份，計算每行數據所在的位置。 | ?? | ?? | ?? | ?? | ?? |
FIRST_VALUE() | 返回窗口內第一行對應的數據。 | ?? | ?? | ?? | ?? | ?? |
LAST_VALUE() | 返回窗口內最后一行對應的數據。 | ?? | ?? | ?? | ?? | ?? |
LAG() | 返回分區中在當前行之前第 N 行對應的數據。 | ?? | ?? | ?? | ?? | ?? |
LEAD() | 返回分區中在當前行之后第 N 行對應的數據。 | ?? | ?? | ?? | ?? | ?? |
NTH_VALUE() | 返回窗口內第 N 行對應的數據。 | ?? | ?? | ? | ?? | ?? |
示例表和數據
本文使用以下簡化的示例表和數據（純屬虛擬，不代表實際銷量）：
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 是產品表，包含產品編號、產品名稱、產品子類和產品分類；sales 是銷量表，按照不同產品每分鐘統計一次銷量，我們生成了 2020 年 7 月 23 日 0 點到 10 點之間的模擬數據。
按照產品分類的銷售排行榜
對于銷售排行榜，我們需要按照產品的分類，計算最近一小時的銷量排名。假如用戶是 2020 年 7 月 23 日 10 點多查看排行榜，可以使用以下語句獲取不同分類下銷量排名前 3 的產品：
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|?? |查詢返回了按照產品分類“大家電”和“手機通訊”顯示的 Top3 銷量產品。該查詢執行的過程如下：
- 首先，通用表表達式 hourly_sales 是不同產品按照小時統計的銷量，我們只需要返回最新一小時的銷量（2020-07-23 09:00:00 到 2020-07-23 09:59:00 之間）；
- 然后，通用表表達式 hourly_rank 是基于 hourly_sales 計算的銷量排名；rank() 函數是一個排名窗口函數，over 子句表示按照小時和產品進行分區，并且按照銷量從到到低進行排序；join 用于關聯產品的信息；
- 最后，查詢 hourly_rank 并返回了每個產品分類中排名前 3 的產品，用于前端頁面顯示。
由于產品分類下面還存在子類，例如“大家電”可以分為“空調”和“冰箱”，我們可以進一步按照子類計算銷售排行榜：
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() 函數 over 子句中的 partition by 分區選項，增加了 product_subcategory 字段。
除了 RANK() 函數之外，ROW_NUMBER() 和 DENSE_RANK() 函數也可以用于實現排名分析；它們的區別在于對排名相同的數據處理不同：
數據 | ROW_NUMBER() | RANK() | DENSE_RANK() |
99 | 1 | 1 | 1 |
66 | 2 | 2 | 2 |
66 | 3 | 2 | 2 |
33 | 4 | 4 | 3 |
ROW_NUMBER() 返回的是不重復的編號；RANK() 對于相同的數據返回相同的排名，后續排名產生了跳躍；DENSE_RANK() 對于相同的數據返回相同的排名，后續排名沒有跳躍。
按照產品分類的銷量飆升榜
銷量飆升榜是指按照過去一段時間內銷量名次的增長率進行排名，返回增長率最大的產品。
亞馬遜是按照過去 24 小時之內的增長率進行計算，我們按照過去 1 小時之內的增長率進行排名。也就是說，如果用戶在 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|對于“大家電”類產品，“美的BCD-213TM(E)”冰箱的銷量排名從第 9 名提高到第 1 名，增長率為 800%，排在第一名。
該查詢執行的過程如下：
- 首先，hourly_sales 是不同產品按照小時統計的銷量，包含了 2020-07-23 08:00:00 到 2020-07-23 09:59:00 之間兩個小時的銷量；
- 然后，hourly_rank 是基于 hourly_sales 計算的銷量排名；rank() 函數是一個排名窗口函數，over 子句表示按照小時和產品進行分區，并且按照銷量從到到低進行排序；join 用于關聯產品的信息；
- 接著，rank_gain 是基于 hourly_rank 計算的產品排名變化情況；lag(rk, 1) 函數返回的是同一產品前一行（對于 9 點到 10 點而言就是 8 點到 9 點）的銷量排名，并且基于該排名計算增長率（100 * (pre_rk - rk)/ rk）；
- 然后，top_gain 是基于 rank_gain 計算的不同分類中的產品增長率排名；這里我們再次使用了 rank() 函數；
- 最后，查詢 top_gain 并返回了每個產品分類中增長率排名前 3 的產品，用于前端頁面顯示。
以上示例中的 LAG(rk, 1) 函數也可以替換為 LEAD(rk ,-1)。另外，FIRST_VALUE()、LAST_VALUE() 以及 NTH_VALUE() 函數的作用比較明確，本文沒有進行演示。
總結
我們以電商平臺的銷售排行榜和銷售飆升榜為案例，介紹了一些常用的 SQL 排名窗口函數和取值窗口函數的使用。包括聚合窗口函數在內的窗口函數為我們提供了強大的數據分析功能，值得我們每個人學習并熟練掌握。
