row_number() over partition by分组聚合
虽然转载,但自己手写的,所以标为自创。仅仅为学习资料保存,因为转载要求链接,怕因为链接原因,博主删了关联自己的。所以仅写出,来源写为自研。来源:https://www.cnblogs.com/starzy/p/11146156.html原主人:昵称: starzy园龄: 8年10个月分组聚合,就是先分组在再排序,可以的话顺手标个排名;如果不想分组也可以排名;如果不想分组同时再去重排也可以。ROW_
分组聚合,就是先分组在再排序,可以的话顺手标个排名;如果不想分组也可以排名;如果不想分组同时再去重排也可以。
ROW_NUMBER() OVER(
[PARTITION BY column_1, column_2,…]
[ORDER BY column_3,column_4,…]
)
Oracle和SQLserver的关键字是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;
结果为
//表示根据deptid分组,在分组内部根据salary从大到小排序
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)
虽然转载,但自己手写的,所以标为自创。仅仅为学习资料保存,因为转载要求链接,怕因为链接原因,博主删了关联自己的。所以仅写出,来源写为自研。
来源:https://www.cnblogs.com/starzy/p/11146156.html
原主人:
昵称: starzy
园龄: 8年10个月
更多推荐
所有评论(0)