文章目錄

• • 示例表和數(shù)據(jù)
  • 按照產(chǎn)品分類(lèi)的銷(xiāo)售排行榜
  • 按照產(chǎn)品分類(lèi)的銷(xiāo)量飆升榜
  • 總結(jié)

大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。不知道你有沒(méi)有注意過(guò)，在亞馬遜或者京東等電商平臺(tái)的網(wǎng)站上都提供了準(zhǔn)實(shí)時(shí)的產(chǎn)品分類(lèi)銷(xiāo)售排行榜。例如，以下就是亞馬遜上銷(xiāo)售排行榜和銷(xiāo)售飆升榜的一個(gè)截圖：

今天我們就來(lái)討論一下如何使用 SQL 排名窗口函數(shù)和取值窗口函數(shù)實(shí)現(xiàn)這類(lèi)功能。關(guān)于窗口函數(shù)的介紹和聚合窗口函數(shù)的應(yīng)用案例可以參考實(shí)戰(zhàn) SQL：銀行等金融機(jī)構(gòu)可疑支付交易的監(jiān)測(cè)。

本文使用的函數(shù)和示例經(jīng)過(guò)以下數(shù)據(jù)庫(kù)驗(yàn)證：MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。它們支持的常用排名窗口函數(shù)和取值窗口函數(shù)如下：

窗口函數(shù)描述MySQLOracleSQL ServerPostgreSQLSQLite

ROW_NUMBER()	為分區(qū)中的每行數(shù)據(jù)分配一個(gè)從 1 開(kāi)始的序列號(hào)。	??	??	??	??	??
RANK()	計(jì)算每行數(shù)據(jù)在分區(qū)中的名次，排名可能產(chǎn)生跳躍。	??	??	??	??	??
DENSE_RANK()	計(jì)算每行數(shù)據(jù)在分區(qū)中的名次，排名不會(huì)產(chǎn)生跳躍。	??	??	??	??	??
PERCENT_RANK()	計(jì)算每行數(shù)據(jù)在分區(qū)中的相對(duì)排名，取值為 (rank - 1) / (rows - 1)。	??	??	??	??	??
CUME_DIST()	計(jì)算每行數(shù)據(jù)在分區(qū)內(nèi)的累積分布，取值范圍大于 0 且小于等于 1。	??	??	??	??	??
NTILE()	將分區(qū)內(nèi)的數(shù)據(jù)分為 N 等份，計(jì)算每行數(shù)據(jù)所在的位置。	??	??	??	??	??
FIRST_VALUE()	返回窗口內(nèi)第一行對(duì)應(yīng)的數(shù)據(jù)。	??	??	??	??	??
LAST_VALUE()	返回窗口內(nèi)最后一行對(duì)應(yīng)的數(shù)據(jù)。	??	??	??	??	??
LAG()	返回分區(qū)中在當(dāng)前行之前第 N 行對(duì)應(yīng)的數(shù)據(jù)。	??	??	??	??	??
LEAD()	返回分區(qū)中在當(dāng)前行之后第 N 行對(duì)應(yīng)的數(shù)據(jù)。	??	??	??	??	??
NTH_VALUE()	返回窗口內(nèi)第 N 行對(duì)應(yīng)的數(shù)據(jù)。	??	??	?	??	??

示例表和數(shù)據(jù)

本文使用以下簡(jiǎn)化的示例表和數(shù)據(jù)（純屬虛擬，不代表實(shí)際銷(xiāo)量）：

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', '手機(jī)', '手機(jī)通訊'); insert into products values(2, 'HUAWEI P40', '手機(jī)', '手機(jī)通訊'); insert into products values(3, '小米10', '手機(jī)', '手機(jī)通訊'); insert into products values(4, 'OPPO Reno4', '手機(jī)', '手機(jī)通訊'); insert into products values(5, 'vivo Y70s', '手機(jī)', '手機(jī)通訊'); 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', '空調(diào)', '大家電'); insert into products values(12, '美的KFR-35GW', '空調(diào)', '大家電'); insert into products values(13, 'TCLKFRd-26GW', '空調(diào)', '大家電'); insert into products values(14, '奧克斯KFR-35GW', '空調(diào)', '大家電'); insert into products values(15, '海爾KFR-35GW', '空調(diào)', '大家電');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 productsunion allselect 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)品編號(hào)、產(chǎn)品名稱(chēng)、產(chǎn)品子類(lèi)和產(chǎn)品分類(lèi)；sales 是銷(xiāo)量表，按照不同產(chǎn)品每分鐘統(tǒng)計(jì)一次銷(xiāo)量，我們生成了 2020 年 7 月 23 日 0 點(diǎn)到 10 點(diǎn)之間的模擬數(shù)據(jù)。

按照產(chǎn)品分類(lèi)的銷(xiāo)售排行榜

對(duì)于銷(xiāo)售排行榜，我們需要按照產(chǎn)品的分類(lèi)，計(jì)算最近一小時(shí)的銷(xiāo)量排名。假如用戶(hù)是 2020 年 7 月 23 日 10 點(diǎn)多查看排行榜，可以使用以下語(yǔ)句獲取不同分類(lèi)下銷(xiāo)量排名前 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 saleswhere 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 rkfrom hourly_sales sjoin 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|🔥🔥🔥 | 大家電 |空調(diào) |海爾KFR-35GW | 293| 2|🔥🔥 | 大家電 |冰箱 |康佳BCD-155C2GBU| 291| 3|🔥 | 手機(jī)通訊 |手機(jī) |vivo Y70s | 298| 1|🔥🔥🔥 | 手機(jī)通訊 |手機(jī) |HUAWEI P40 | 273| 2|🔥🔥 | 手機(jī)通訊 |手機(jī) |iPhone 11 | 261| 3|🔥 |

查詢(xún)返回了按照產(chǎn)品分類(lèi)“大家電”和“手機(jī)通訊”顯示的 Top3 銷(xiāo)量產(chǎn)品。該查詢(xún)執(zhí)行的過(guò)程如下：

• 首先，通用表表達(dá)式 hourly_sales 是不同產(chǎn)品按照小時(shí)統(tǒng)計(jì)的銷(xiāo)量，我們只需要返回最新一小時(shí)的銷(xiāo)量（2020-07-23 09:00:00 到 2020-07-23 09:59:00 之間）；
• 然后，通用表表達(dá)式 hourly_rank 是基于 hourly_sales 計(jì)算的銷(xiāo)量排名；rank() 函數(shù)是一個(gè)排名窗口函數(shù)，over 子句表示按照小時(shí)和產(chǎn)品進(jìn)行分區(qū)，并且按照銷(xiāo)量從到到低進(jìn)行排序；join 用于關(guān)聯(lián)產(chǎn)品的信息；
• 最后，查詢(xún) hourly_rank 并返回了每個(gè)產(chǎn)品分類(lèi)中排名前 3 的產(chǎn)品，用于前端頁(yè)面顯示。

由于產(chǎn)品分類(lèi)下面還存在子類(lèi)，例如“大家電”可以分為“空調(diào)”和“冰箱”，我們可以進(jìn)一步按照子類(lèi)計(jì)算銷(xiāo)售排行榜：

with hourly_sales(product_id, ymdh, quantity) as (select product_id, date_format(sale_time, '%Y%m%d%H'), sum(quantity)from saleswhere 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 rkfrom hourly_sales sjoin 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| 大家電 |空調(diào) |海爾KFR-35GW | 293| 1| 大家電 |空調(diào) |格力KFR-35GW | 279| 2| 大家電 |空調(diào) |美的KFR-35GW | 277| 3| 手機(jī)通訊 |手機(jī) |vivo Y70s | 298| 1| 手機(jī)通訊 |手機(jī) |HUAWEI P40 | 273| 2| 手機(jī)通訊 |手機(jī) |iPhone 11 | 261| 3|

該查詢(xún)只修改了 rank() 函數(shù) over 子句中的 partition by 分區(qū)選項(xiàng)，增加了 product_subcategory 字段。

除了 RANK() 函數(shù)之外，ROW_NUMBER() 和 DENSE_RANK() 函數(shù)也可以用于實(shí)現(xiàn)排名分析；它們的區(qū)別在于對(duì)排名相同的數(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() 返回的是不重復(fù)的編號(hào)；RANK() 對(duì)于相同的數(shù)據(jù)返回相同的排名，后續(xù)排名產(chǎn)生了跳躍；DENSE_RANK() 對(duì)于相同的數(shù)據(jù)返回相同的排名，后續(xù)排名沒(méi)有跳躍。

按照產(chǎn)品分類(lèi)的銷(xiāo)量飆升榜

銷(xiāo)量飆升榜是指按照過(guò)去一段時(shí)間內(nèi)銷(xiāo)量名次的增長(zhǎng)率進(jìn)行排名，返回增長(zhǎng)率最大的產(chǎn)品。

亞馬遜是按照過(guò)去 24 小時(shí)之內(nèi)的增長(zhǎng)率進(jìn)行計(jì)算，我們按照過(guò)去 1 小時(shí)之內(nèi)的增長(zhǎng)率進(jìn)行排名。也就是說(shuō)，如果用戶(hù)在 2020 年 7 月 23 日 10 點(diǎn)多查看排行榜，使用 9 點(diǎn)到 10 點(diǎn)的銷(xiāo)量排名和 8 點(diǎn)到 9 點(diǎn)的銷(xiāo)量排名計(jì)算增長(zhǎng)率：

with hourly_sales(product_id, ymdh, quantity) as (select product_id, date_format(sale_time, '%y%m%d%H'), sum(quantity)from saleswhere 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 rkfrom hourly_sales sjoin 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 gainfrom hourly_rank ), top_gain as(select *, rank() over (partition by product_category order by gain desc) gain_rkfrom rank_gainwhere 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| 大家電 |空調(diào) |海爾KFR-35GW | 6| 2|200.0000%| 2| 大家電 |空調(diào) |美的KFR-35GW | 10| 5|100.0000%| 3| 手機(jī)通訊 |手機(jī) |vivo Y70s | 4| 1|300.0000%| 1| 手機(jī)通訊 |手機(jī) |小米10 | 5| 5|0.0000% | 2| 手機(jī)通訊 |手機(jī) |OPPO Reno4 | 3| 4|-25.0000%| 3|

對(duì)于“大家電”類(lèi)產(chǎn)品，“美的BCD-213TM(E)”冰箱的銷(xiāo)量排名從第 9 名提高到第 1 名，增長(zhǎng)率為 800%，排在第一名。

該查詢(xún)執(zhí)行的過(guò)程如下：

• 首先，hourly_sales 是不同產(chǎn)品按照小時(shí)統(tǒng)計(jì)的銷(xiāo)量，包含了 2020-07-23 08:00:00 到 2020-07-23 09:59:00 之間兩個(gè)小時(shí)的銷(xiāo)量；
• 然后，hourly_rank 是基于 hourly_sales 計(jì)算的銷(xiāo)量排名；rank() 函數(shù)是一個(gè)排名窗口函數(shù)，over 子句表示按照小時(shí)和產(chǎn)品進(jìn)行分區(qū)，并且按照銷(xiāo)量從到到低進(jìn)行排序；join 用于關(guān)聯(lián)產(chǎn)品的信息；
• 接著，rank_gain 是基于 hourly_rank 計(jì)算的產(chǎn)品排名變化情況；lag(rk, 1) 函數(shù)返回的是同一產(chǎn)品前一行（對(duì)于 9 點(diǎn)到 10 點(diǎn)而言就是 8 點(diǎn)到 9 點(diǎn)）的銷(xiāo)量排名，并且基于該排名計(jì)算增長(zhǎng)率（100 * (pre_rk - rk)/ rk）；
• 然后，top_gain 是基于 rank_gain 計(jì)算的不同分類(lèi)中的產(chǎn)品增長(zhǎng)率排名；這里我們?cè)俅问褂昧?rank() 函數(shù)；
• 最后，查詢(xún) top_gain 并返回了每個(gè)產(chǎn)品分類(lèi)中增長(zhǎng)率排名前 3 的產(chǎn)品，用于前端頁(yè)面顯示。

以上示例中的 LAG(rk, 1) 函數(shù)也可以替換為 LEAD(rk ,-1)。另外，FIRST_VALUE()、LAST_VALUE() 以及 NTH_VALUE() 函數(shù)的作用比較明確，本文沒(méi)有進(jìn)行演示。

總結(jié)

我們以電商平臺(tái)的銷(xiāo)售排行榜和銷(xiāo)售飆升榜為案例，介紹了一些常用的 SQL 排名窗口函數(shù)和取值窗口函數(shù)的使用。包括聚合窗口函數(shù)在內(nèi)的窗口函數(shù)為我們提供了強(qiáng)大的數(shù)據(jù)分析功能，值得我們每個(gè)人學(xué)習(xí)并熟練掌握。

除了上面的使用場(chǎng)景，你還遇到過(guò)或者了解哪些應(yīng)用案例？歡迎關(guān)注??、評(píng)論📝、點(diǎn)贊👍！

總結(jié)

以上是生活随笔為你收集整理的实战 SQL：亚马逊、京东等电商平台的销售排行榜和飙升榜的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。