您当前的位置:首页 > 互联网百科 > 电子商务

实战 SQL:亚马逊、京东等电商平台的销售排行榜和飙升榜

时间:2020-07-27 17:23:42  来源:  作者:

文章来源:https://blog.csdn.net

原文作者:不剪发的Tony老师

来源平台:CSDN

实战 SQL:亚马逊、京东等电商平台的销售排行榜和飙升榜

 

大家好,我是只谈技术不剪发的 Tony 老师。不知道你有没有注意过,在亚马逊或者京东等电商平台的网站上都提供了准实时的产品分类销售排行榜。例如,以下就是亚马逊上销售排行榜和销售飙升榜的一个截图:

实战 SQL:亚马逊、京东等电商平台的销售排行榜和飙升榜

 

今天我们就来讨论一下如何使用 SQL 排名窗口函数和取值窗口函数实现这类功能。

本文使用的函数和示例经过以下数据库验证:MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。它们支持的常用排名窗口函数和取值窗口函数如下:

实战 SQL:亚马逊、京东等电商平台的销售排行榜和飙升榜

 

示例表和数据

本文使用以下简化的示例表和数据(纯属虚拟,不代表实际销量):

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() 函数也可以用于实现排名分析;它们的区别在于对排名相同的数据处理不同:

实战 SQL:亚马逊、京东等电商平台的销售排行榜和飙升榜

 

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 排名窗口函数和取值窗口函数的使用。包括聚合窗口函数在内的窗口函数为我们提供了强大的数据分析功能,值得我们每个人学习并熟练掌握。

除了上面的使用场景,你还遇到过或者了解哪些应用案例?欢迎关注❤️、评论、点赞!


目前在职JAVA开发,如果你现在也在学习Java,在入门学习Java的过程当中缺乏基础入门的视频教程, 可以关注并私信我:01。免费领取2020年最新Java基础精讲视频教程,学习手册,面试题,开发工具,PDF文档书籍教程,以下资料截图:

实战 SQL:亚马逊、京东等电商平台的销售排行榜和飙升榜

 


实战 SQL:亚马逊、京东等电商平台的销售排行榜和飙升榜

 


实战 SQL:亚马逊、京东等电商平台的销售排行榜和飙升榜

 


实战 SQL:亚马逊、京东等电商平台的销售排行榜和飙升榜

 


实战 SQL:亚马逊、京东等电商平台的销售排行榜和飙升榜

 


实战 SQL:亚马逊、京东等电商平台的销售排行榜和飙升榜

 


实战 SQL:亚马逊、京东等电商平台的销售排行榜和飙升榜

 


实战 SQL:亚马逊、京东等电商平台的销售排行榜和飙升榜

 


实战 SQL:亚马逊、京东等电商平台的销售排行榜和飙升榜

 


实战 SQL:亚马逊、京东等电商平台的销售排行榜和飙升榜

 


实战 SQL:亚马逊、京东等电商平台的销售排行榜和飙升榜

 


实战 SQL:亚马逊、京东等电商平台的销售排行榜和飙升榜

 

 



Tags:京东等电商平台   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
文章来源:https://blog.csdn.net原文作者:不剪发的Tony老师来源平台:CSDN 大家好,我是只谈技术不剪发的 Tony 老师。不知道你有没有注意过,在亚马逊或者京东等电商平台的网站上都...【详细内容】
2020-07-27  Tags: 京东等电商平台  点击:(903)  评论:(0)  加入收藏
关于选品,电商行业有句话叫:"选对产品就成功了80%"。虽然这句话不够严谨,无法将选品和运营等各阶段工作的效果进行量化,但起码说明了电商选品的重要性。选对了产品至少有50%成功...【详细内容】
2020-03-17  Tags: 京东等电商平台  点击:(236)  评论:(0)  加入收藏
▌简易百科推荐
来源:瘾工厂 在这个消费主义盛行的时代,逛商场扫货已经成为了现代人的一种解压运动。 当手中的购物车被一件件心仪的商品所填满时,心里头那份幸福感也跟着爆棚~ 但...遇到排队...【详细内容】
2022-07-14  预制建筑网    Tags:亚马逊   点击:(4)  评论:(0)  加入收藏
在外贸中,我们会遇到各种问题,这时候就需要借助一些工具来解决问题,可以看下下面网站,让你事半功倍1. 翻译:谷歌翻译,DeepL2. 开发客户:谷歌搜索3. 汇率查询:直接百度4. 海关编码查...【详细内容】
2022-07-13  猫咪的旅行日记    Tags:外贸   点击:(6)  评论:(0)  加入收藏
淘宝 iOS 版今日发布 10.14.0 版本,根据官方更新公告,用户可在下单时选择多个地址,凑单更自由。据官方介绍,目前仅支持手机淘宝 App 多地址下单,PC 端暂不支持。天猫超市、天猫国...【详细内容】
2022-07-08    IT之家  Tags:淘宝   点击:(11)  评论:(0)  加入收藏
我是星星,一个亚马逊小卖家。混迹亚马逊多年,虽然说不上是大神级运营,但是比新手还是好那么一丢丢,我想毫无保留地分享给大家我总结的一些亚马逊知识,希望大家能够喜欢。最近有人...【详细内容】
2022-07-08  星星跨境    Tags:亚马逊   点击:(11)  评论:(0)  加入收藏
7亿包裹或被美国拦截?跨境电商的集体悲鸣?中国出海企业恐将遭遇重创?直邮小包一网打尽?最后的红利已关闭?低价商品已是命悬一线? 看一下这些句子吓不吓人?是不是感觉跨境电商行业都...【详细内容】
2022-07-08  跨境电商鹰熊汇    Tags:小包业务   点击:(10)  评论:(0)  加入收藏
京东国际买手店也推出来几个月了,目前入住会有京东的流量扶持,第一批入住的有京东广告流量坑位。到现在有什么变化呢?具体有什么变动小编这里一一跟大家讲解一下. 首先给大家讲...【详细内容】
2022-07-08  a朱JDRZi6    Tags:京东买手店   点击:(10)  评论:(0)  加入收藏
对于淘宝商家来说,开店了以后需要上传产品,产品的相关信息也需要设置好,其中就包括了商品标题。如果淘宝屏蔽关键词了话,应该如何取消呢?下面的内容中为大家进行介绍。淘宝屏蔽...【详细内容】
2022-07-08  电商工具箱    Tags:淘宝   点击:(11)  评论:(0)  加入收藏
今天跟大家分享视频号带货赚钱的入局主要分成4个方面来说1、视频号现状和如何抓住视频号红利期2、视频号主要变现方式3、新手入局视频号带货需要准备些什么4、如何选择类目...【详细内容】
2022-07-06  每日精彩哥    Tags:视频号带货   点击:(11)  评论:(0)  加入收藏
在今年4月份的时候,淘宝自己本身就推出了一个叫做光合平台,主要服务淘宝逛逛。淘宝光合平台是什么?核心就是:逛逛将以光合平台为依托,加大内容创作者扶持力度,重点打造1000个新品...【详细内容】
2022-07-06  侠侠爱分享    Tags:光合平台   点击:(19)  评论:(0)  加入收藏
大家好,我是陈江河,常年从事维权工作,至今已成功协助数百家企业管控好电商渠道。从业以来,我发现很多的企业朋友都有管控的意识,也了解维权控价的一些操作方式。但是对于怎么把这...【详细内容】
2022-07-06  我叫陈江河    Tags:电商渠道   点击:(17)  评论:(0)  加入收藏
站内最新
站内热门
站内头条