分组Top N 问题

浅浅的花香味﹌ 2022-05-19 13:36 136阅读 0赞

今天面试，面试官给了这样一个场景：  
有两张表，一张表存放 ***车队ID，班组ID，司机ID***  
另一种表存放 ***司机ID，运营时间，运营里程***

要查询出 ***7月份每个车队每个班组里的 Top 3***

这就要用到 **ROW\_NUMBER()** 函数

首先按需求建两张表

CREATE TABLE demo_of_topn_car ( companyid varchar(8), classid varchar(8), driverid varchar(8) ) CREATE TABLE demo_of_topn_operating ( driverid varchar(8), operadate datetime, mileage DECIMAL(5,2) )

第一步思路 先把每个车队每个班组每个司机的运营总和计算出来

SELECT car.companyid,car.classid,operat.driverid,sum(mileage) mileage from ( SELECT companyid,classid FROM demo_of_topn_car GROUP BY companyid,classid ) car LEFT JOIN ( SELECT a.companyid,a.classid,a.driverid,b.mileage FROM demo_of_topn_car a LEFT JOIN demo_of_topn_operating b on a.driverid = b.driverid ) operat ON car.companyid = operat.companyid AND car.classid = operat.classid GROUP BY car.companyid,car.classid,operat.driverid

然后对结果集进行分组排序

SELECT companyid,classid,driverid,row_number() over (PARTITION by companyid,classid ORDER BY mileage desc) rid from ( SELECT car.companyid,car.classid,operat.driverid,sum(mileage) mileage from ( SELECT companyid,classid FROM demo_of_topn_car GROUP BY companyid,classid ) car LEFT JOIN ( SELECT a.companyid,a.classid,a.driverid,b.mileage FROM demo_of_topn_car a LEFT JOIN demo_of_topn_operating b on a.driverid = b.driverid ) operat ON car.companyid = operat.companyid AND car.classid = operat.classid GROUP BY car.companyid,car.classid,operat.driverid ORDER BY mileage DESC ) t1

**切记：求 Top N 都是倒序**

最后需要在外面嵌套一层，或者用with

with res as (
    SELECT companyid,classid,driverid,row_number() over (PARTITION by companyid,classid ORDER BY mileage desc) rid from ( SELECT car.companyid,car.classid,operat.driverid,sum(mileage) mileage from ( SELECT companyid,classid FROM demo_of_topn_car GROUP BY companyid,classid ) car LEFT JOIN ( SELECT a.companyid,a.classid,a.driverid,b.mileage FROM demo_of_topn_car a LEFT JOIN demo_of_topn_operating b on a.driverid = b.driverid ) operat ON car.companyid = operat.companyid AND car.classid = operat.classid GROUP BY car.companyid,car.classid,operat.driverid ORDER BY mileage DESC ) t1 ) SELECT * from res where rid <= 3

不知道大家有什么更好的办法，欢迎指导。 ：）