row_number() over partition by 分组聚合

末蓝、 2023-03-03 08:46 23阅读 0赞

# [row\_number() over partition by 分组聚合][row_number_ over partition by] #

**分组聚合，就是先分组再排序，可以的话顺手标个排名；如果不想分组也可以排名；如果不想分组同时再去重排名也可以**

ROW_NUMBER() OVER(
        [PARTITION BY column_1, column_2,…]
        [ORDER BY column_3,column_4,…]
    )

Oracle和SQL server的关键字是over partition by

mysql的无关键字row\_number() over (partition by col1 order by col2),表示根据col1分组，在分组内部根据col2排序

## Oracle和sqlserver ##

最终效果：

[![Image][]][Image 1]

例子：

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

-- 建表
    USE db_03;
    DROP TABLE IF EXISTS employee;
    create table employee (empid int ,deptid int ,salary decimal(10,2));
    insert into employee values(1,10,5500.00);
    insert into employee values(2,10,4500.00);
    insert into employee values(3,20,1900.00);
    insert into employee values(4,20,4800.00);
    insert into employee values(5,40,6500.00);
    insert into employee values(6,40,14500.00);
    insert into employee values(7,40,44500.00);
    insert into employee values(8,50,6500.00);
    insert into employee values(9,50,7500.00);
    
    SELECT * FROM employee;

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

[![Image \[1\]][Image _1]][Image _1_Image _1]

SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee

结果：

[![Image \[2\]][Image _2]][Image _2_Image _2]

**如果不要分组，就仅仅order by 的话**

需求：给username加上唯一标示id

背景：需要一个纬度表，里面有仅仅username的唯一标示，因为hive中不存在自增id

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

select distinct 
        price,
        row_number() over (order by price)
    from
        products
    order by 
        price;

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

price  | row_number
    ---------+------------
      300.00 |          1
      300.00 |          2
      400.00 |          3
      500.00 |          4
      600.00 |          5
      600.00 |          6
      700.00 |          7
      800.00 |          8
      800.00 |          9
      900.00 |         10
     1100.00 |         11

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

**需求同上，如果需要去重的话（distinct）**

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

with prices as (
        select distinct
            price
        from 
            products
    )
    select price,row_numer()over(order by price) from prices;

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

price  | row_number
    ---------+------------
      300.00 |          1
      400.00 |          2
      500.00 |          3
      600.00 |          4
      700.00 |          5
      800.00 |          6
      900.00 |          7
     1100.00 |          8

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

## mysql ##

因为不能使用这个关键字，所以配合其他关键字使用

预期效果

[![Image \[3\]][Image _3]][Image _3_Image _3][![Image \[4\]][Image _4]][Image _4_Image _4]

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

select deptid,salary
    from employee a
    where 2 > (
    select count(1)
    from employee b
    where a.salary<b.salary and a.deptid=b.deptid
    )
    order by a.deptid,a.salary desc;

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

但是有弊端，如果最大值有多个，那么就会出现多个最大值，so，要动态的

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

SET @row=0;
    SET @groupid='';
    select a.deptid,a.salary
    from
    (
    select deptid,salary,case when @groupid=deptid then @row:=@row+1 else @row:=1 end rownum,@groupid:=deptid from employee
    order by deptid,salary desc
    )a
    where a.rownum<=2;

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

mysql还有其他写法，通过求出极值再进行关联

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

SELECT t.stuid,  
            t.stuname,  
            t.score,  
            t.classid  
    FROM stugrade t  
    where t.score = (SELECT max(tmp.score) from stugrade tmp where tmp.classid=t.classid)

[row_number_ over partition by]: https://www.cnblogs.com/starzy/p/11146156.html
[Image]: /images/20230208/bf2d22ce6a99400bb9108ac8ba2b16c5.png
[Image 1]: https://img2018.cnblogs.com/blog/421906/201907/421906-20190707140648346-126363591.png
[aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]: /images/20230208/23be7e585c2945508dc2f93109dbfefa.png
[Image _1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWcyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNDIxOTA2LzIwMTkwNy80MjE5MDYtMjAxOTA3MDcxNDA3MDUwMDYtMTYyMTcxNzI1Mi5wbmc?x-oss-process=image/format,png
[Image _1_Image _1]: /images/20230208/84f9e45f5d0b4aa1a106abe715009ed6.png
[Image _2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWcyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNDIxOTA2LzIwMTkwNy80MjE5MDYtMjAxOTA3MDcxNDA3MTYzNTMtMTEyODkzMzcyOS5wbmc?x-oss-process=image/format,png
[Image _2_Image _2]: /images/20230208/3b0ff2674dae4619bd326920a1300619.png
[Image _3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWcyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNDIxOTA2LzIwMTkwNy80MjE5MDYtMjAxOTA3MDcxNDA3MzI4MDctODM1MDQzODAxLnBuZw?x-oss-process=image/format,png
[Image _3_Image _3]: https://img2018.cnblogs.com/blog/421906/201907/421906-20190707140722124-1222270679.png
[Image _4]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWcyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNDIxOTA2LzIwMTkwNy80MjE5MDYtMjAxOTA3MDcxNDA3NDQwOTMtMTYyODgzNTI3NS5wbmc?x-oss-process=image/format,png
[Image _4_Image _4]: /images/20230208/6086b72c9aa84733a93055abde18f77e.png