订单表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