【窗口函数】ROWS_NUMBER练习题-拓冰建站

订单表TOP商品+渠道TOP2窗口函数练习题

一、需求拆解

先按商品product_id分组，统计每个商品总订单量sum(amount)，筛选总订单量前4的商品；
针对筛选出的4个商品，按product_id分区，对每个渠道的订单量排名，取每个商品渠道排名前2的渠道；
使用ROW_NUMBER()/RANK()做分组排名，窗口函数结果需嵌套子查询过滤。

二、原始数据表 orders

Oid	product_id	channel	amount
1	1	直播	2
2	4	主页	3
3	8	搜索	1
4	10	其他	7
5	1	商品页	9
6	4	直播	1
7	2	主页	3
8	3	商品页	2
9	7	直播	2
10	9	搜索	1
11	5	直播	2
12	1	其他	8

三、建表与测试数据（MySQL）

CREATETABLEorders(OidINTCOMMENT'订单ID',product_idINTCOMMENT'商品ID',channelVARCHAR(20)COMMENT'销售渠道',amountINTCOMMENT'订单量')ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;INSERTINTOorders(Oid,product_id,channel,amount)VALUES(1,1,'直播',2),(2,4,'主页',3),(3,8,'搜索',1),(4,10,'其他',7),(5,1,'商品页',9),(6,4,'直播',1),(7,2,'主页',3),(8,3,'商品页',2),(9,7,'直播',2),(10,9,'搜索',1),(11,5,'直播',2),(12,1,'其他',8);

selecttem.product_id,res.channelfrom(selectproduct_id,sum(amount)asamountsfromorders ogroupbyproduct_idorderbyamountsdesclimit4)temleftJOIN(selectproduct_id,channel,ROW_NUMBER()over(PARTITIONbyproduct_idORDERBYamountdesc)asrank_idfromorders)resonres.product_id=tem.product_idwhereres.rank_id<=2