在线午夜精品自拍小视频_无码av无码专区线_亚洲无码精品人妻_人人澡欧美一区

實戰(zhàn) SQL：亞馬遜、京東等電商平臺的銷售排行榜和飆升榜

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目錄

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

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

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

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

示例表和數(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’, ‘空調(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(10rand(0)) from products
union all
select product_id, sale_time + interval 1 minute, floor(10rand(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;

查詢返回了按照產(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 用于關(guān)聯(lián)產(chǎn)品的信息；
最后，查詢 hourly_rank 并返回了每個產(chǎn)品分類中排名前 3 的產(chǎn)品，用于前端頁面顯示。

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

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;

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

除了 RANK() 函數(shù)之外，ROW_NUMBER() 和 DENSE_RANK() 函數(shù)也可以用于實現(xiàn)排名分析；它們的區(qū)別在于對排名相同的數(shù)據(jù)處理不同：

ROW_NUMBER() 返回的是不重復(fù)的編號；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;

對于“大家電”類產(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 用于關(guān)聯(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ù)的作用比較明確，本文沒有進行演示。
總結(jié)

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