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

最终效果：

例子：

-- 建表

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;

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

结果：

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

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

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

select distinct

    price,

    row_number() over (order by price)

from

    products

order by

    price;

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

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

with prices as (

    select distinct

        price

    from

        products

)

select price,row_numer()over(order by price) from prices;

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

mysql

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

预期效果

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;

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

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;

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

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 分组聚合

Oracle和sqlserver

mysql

最新文章

热门文章