订单表TOP商品渠道TOP2窗口函数练习题一、需求拆解先按商品product_id分组统计每个商品总订单量sum(amount)筛选总订单量前4的商品针对筛选出的4个商品按product_id分区对每个渠道的订单量排名取每个商品渠道排名前2的渠道使用ROW_NUMBER()/RANK()做分组排名窗口函数结果需嵌套子查询过滤。二、原始数据表 ordersOidproduct_idchannelamount11直播224主页338搜索1410其他751商品页964直播172主页383商品页297直播2109搜索1115直播2121其他8三、建表与测试数据MySQLCREATETABLEorders(OidINTCOMMENT订单ID,product_idINTCOMMENT商品ID,channelVARCHAR(20)COMMENT销售渠道,amountINTCOMMENT订单量)ENGINEInnoDBDEFAULTCHARSETutf8mb4;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_idtem.product_idwhereres.rank_id2运行结果