文章目录

不知道你有没有注意过，在亚马逊或者京东等电商平台的网站上都提供了准实时的产品分类销售排行榜。例如，以下就是亚马逊上销售排行榜和销售飙升榜的一个截图：

今天我们就来讨论一下如何使用 SQL 排名窗口函数和取值窗口函数实现这类功能。关于窗口函数的介绍和聚合窗口函数的应用案例可以参考实战 SQL：银行等金融机构可疑支付交易的监测。

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

示例表和数据

本文使用以下简化的示例表和数据（纯属虚拟，不代表实际销量）：

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(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 是产品表，包含产品编号、产品名称、产品子类和产品分类；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