分组聚合，就是先分组再排序，可以的话顺手标个排名；如果不想分组也可以排名；如果不想分组同时再去重排名也可以
ROW_NUMBER() OVER( [PARTITION BY column_1, column_2,…] [ORDER BY column_3,column_4,…])
Oracle和SQL server的关键字是over partition by
mysql的无关键字
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 productsorder 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,salaryfrom employee awhere 2 > (select count(1)from employee bwhere a.salary
但是有弊端，如果最大值有多个，那么就会出现多个最大值，so，要动态的
SET @row=0;SET @groupid='';select a.deptid,a.salaryfrom(select deptid,salary,case when @groupid=deptid then @row:=@row+1 else @row:=1 end rownum,@groupid:=deptid from employeeorder by deptid,salary desc)awhere 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)