关键词：
文章目录
大家好，我是只谈技术不剪发的 Tony 老师。不知道你有没有注意过，在亚马逊或者京东等电商平台的网站上都提供了准实时的产品分类销售排行榜。例如，以下就是亚马逊上销售排行榜和销售飙升榜的一个截图：
今天我们就来讨论一下如何使用 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 排名窗口函数和取值窗口函数的使用。包括聚合窗口函数在内的窗口函数为我们提供了强大的数据分析功能，值得我们每个人学习并熟练掌握。
除了上面的使用场景，你还遇到过或者了解哪些应用案例？欢迎关注❤️、评论📝、点赞👍！
京东3c的赋能革命：如何打造智能手机第一平台？
...的记录，而且锤子、美图、360手机更是依靠不俗销量进入排行榜完成了超越记录的销售增长比例。除此之外，众多品牌与京东3C合作定制款手机、京东独家首发手机等产品的举措，更是再次推进了京东3C在 查看详情
视频网站和电商流行起了抱团，联合会员这门生意是怎么做的？
...同样的思路，即与视频平台联手，如唯品会与腾讯视频、亚马逊与腾讯视频等。其中一个很有意思的现象是，腾讯是京东的大股东，但京东却选择了百度持股的爱奇艺作为合作方，腾讯视频却找了与集团并无业务交集的亚马逊合... 查看详情
小象慢跑:“代运营”是否安全？
...联网大爆炸，电商经济也迅速蹿红 一方面阿里巴巴、亚马逊等这些电商巨头深刻的改变了人们日常购物，甚至跨国贸易的方式； 另一方面，在包括但不限于淘宝天猫、京东、亚马逊、阿里巴巴国际站等电商平台上创业也... 查看详情
python爬虫编程思想（70）：项目实战--抓取京东商城手机销售排行榜
...bsp; 本文的例子使用requests抓取京东商城手机销售排行榜，并使用pyquery和CSS选择器提取相关的信息，同时将这些信息保存到Excel文件中。本例抓取总排行榜，并单独提取Apple、华为和小米手机的销售排行榜，将这些信息都... 查看详情
淘宝，天猫，京东都是谁创立的？
...、百度等中国互联网巨头共同跻身全球前十大互联网公司排行榜。2014年，京东市场交易额达到2602亿元，净收入达到1150亿元。天猫：（英文：tmall，亦称淘宝商城、天猫商城）原名淘宝商城，是一个综合性购物网站。2012年1月11日... 查看详情
跨境电商海外分销平台都有哪些？
...商的平台主要如下：1、国际B2C跨境电商平台：速卖通、亚马逊、eBay、Wish、兰亭集势、敦煌；2、进口跨境电商平台：洋码头、天猫国际、苏宁云商海外购，网易考拉海购、顺丰海淘；3、本土化跨境电商平台：Flipkart印度，walmart... 查看详情
网站优化
...子商务网站平台搭建、直通车托管等立体式服务。丰富的实战经验，强大的实战队伍，铸就我们成为滕州地 查看详情
quartile收购sidecar，打造首屈一指的跨渠道电商广告平台
...广告平台，包括谷歌、MicrosoftBing、Facebook、Instagram和亚马逊等。两家公司将以Quartile的名称合并，共同致力于将该品牌发展成为全球首屈一指的跨渠道电商广告平台。两家平台均采用机器学习和自然语言处理技术，为... 查看详情
天然工坊定制开发平台系统
...竹纤维本色纸巾。天然工坊不靠线下商场门店，不靠阿里京东等电商。拥有自己独特的电商模式，如今用户 查看详情
同时支持淘宝天猫拼多多和京东四个平台的电商erp哪家好？
百度同时支持淘宝天猫拼多多和京东四个平台的电商erp哪家好？参考技术A▪⋅这四个平台不管是行业中的哪家，还是我们快麦ERP都是完美支持淘宝、天猫、京东、拼多多这四家电商平台的,这点可以放心选择。如何选择了?... 查看详情
数据中台实战：如何打造可以支撑n条产品线的标签平台
...数据分析高级教程— 1 —为什么要建设标签平台亚马逊的CEOJeffBezos曾说过他的梦想，「如果我有一百万的用户，我就会做一百万个不同的网站！」。当然现在大型的电商公司如亚马逊、淘宝等已经 查看详情
跨境电商有什么优势？导致亚马逊无货源店群备受关注！
跨境电商有什么优势？导致亚马逊无货源店群备受关注！亚马逊作为全球最大的跨境电商平台，在全球市场的知名度是很高的，用户群体也是最多的，全球共14大站点，其中欧洲站和北美站的客户群体最为优质，消费能力强劲，... 查看详情
基于java+spring+springmvc+mybatis茅台酒业电商平台_白酒销售
现如今,酒业电商平台是商业贸易中的一条非常重要的道路，可以把其从传统的实体模式中解放中来，网上购物可以为消费者提供巨大的便利。通过酒业电商平台这个平台，可以使用户足不出户就可以了解现今的流行... 查看详情
python数据分析实战案例：基于电商销售数据的rfm模型构建(代码片段)
目录1.背景2.分析目标3.数据准备4.数据清洗4.1查看是否含有缺失值4.2查看是否有异常值4.3数据整理5.具体目标分析5.1分析每年销售额的增长率5.2各个地区分店的销售额5.3销售淡旺季分析5.4新老客户数5.5用户价值度RFM模型分析6.案例... 查看详情
电商平台应该分析哪些数据？具体怎么去分析
电商平台应该分析的数据和分析的规则如下：1、网站运营指标：网站运营指标主要用来衡量网站的整体运营状况，这里Ec数据分析联盟暂将网站运营指标下面细分为网站流量指标、商品类目指标、以及供应链指标。网站流量指标... 查看详情
当前电商行业的介绍
...和京东京东商城刘强东2004年1月网络零售服务淘宝天猫，亚马逊亚马逊杰夫贝佐斯1995年书籍、电子产品谷歌苏宁易购张近东2009年全品类B2C在线电子商城以上全部唯品会沈亚2008年8月互联网 c2c是个人与个人之间的电子商务... 查看详情
微信小程序一般去哪里对接一些商品接口
...下单、支付等功能。2.第三方电商平台接口：比如淘宝、京东等电商平台提供了自己的开放平台接口，可以为小程序提供商品展示和下单等功能。3.自搭建API接口：自己搭建的API接口可以作为小程序与后台之间的桥梁，提供商品... 查看详情
降低成本
“现在电商化采购平台是京东自己搭建一个采购平台，采购者和供应商并不需要直接见面，通过采购物品直接网上比价、评价的模式，可以在电脑上完成所有的操作。”记者了解到，目前京东已经和中国联通展开合作，将自己的... 查看详情