mysql sql窗口函数：分组聚合，分组排序：row_number() over() 行数递增，rank() over()跳跃排序，dense_rank() over()连续排序

row_number() over() 行数递增

• row_number() over() 和 rownum差不多，功能更强一点（可以在各个分组内从1开时排序）．

• 转载：https://www.cnblogs.com/alsf/p/6344197.html

• 是一种窗口函数，用于为查询结果集中的每一行分配一个唯一的序号。

  • 分组聚合，分组排序
  • 它可以在ORDER BY子句中使用，以便按特定列对结果集进行排序。

row_number() over(partition by [要分组的列]  order by [排序列])

• over()里头的 分组以及排序的执行 晚于

  • where
  • group by
  • order by 的执行。

• partition by 用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组，它和聚合函数不同的地方在于

  • **它能够返回一个分组中的多条记录，**而聚合函数一般只有一个反映统计值的记录。

一个SQL整理

1. PARTITION BY 后加 1级分组，2级分组
2. row_num > 1

SELECT *
FROM (
    SELECT *,
			ROW_NUMBER() OVER (
				PARTITION BY DATE(created_at), 
				HOUR(created_at) ORDER BY created_at
			) AS row_num
    FROM your_table
) AS subquery
WHERE row_num <= 10;

DATE()
HOUR()

• 分解

1	zhangsan	1
2	lisi	22
3	lisi	33

SELECT
	*,
	ROW_NUMBER() OVER ( PARTITION BY username ORDER BY info DESC ) 
FROM USER

-- 结果。info 倒序排，然后变成 row_num
3	lisi	33	1
2	lisi	22	2
1	zhangsan	1	1

• 条件整理

相等
PARTITION BY username ORDER BY info DESC
PARTITION BY username ORDER BY username, info DESC --普通情况，这样写即可。

PARTITION BY username, username ORDER BY info DESC --容易误导人的写法
-- 加了 逗号，第一个只能升序。 添加了 ORDER BY 后，可以倒序

Row_Number() OVER 案例

SELECT empno,WORKDEPT,SALARY, 

Row_Number() OVER (partition by workdept ORDER BY salary desc) rank 
-- 根据 workdept工作 分组，然后根据 薪资倒序排序。
FROM employee

--------------------------------------  
000010  A00 152750  1  
000110  A00 66500   2  
000120  A00 49250   3  
200010  A00 46500   4  
200120  A00 39250   5  
000020  B01 94250   1  
000030  C01 98250   1  
000130  C01 73800   2

（2）对查询结果进行排序：（无分组）

SELECT empno,WORKDEPT,SALARY, 
Row_Number() OVER (ORDER BY salary desc) rank
-- 直接倒序排序
FROM employee
--------------------------------------  
000010  A00 152750  1  
000030  C01 98250   2  
000070  D21 96170   3  
000020  B01 94250   4  
000090  E11 89750   5  
000100  E21 86150   6  
000050  E01 80175   7  
000130  C01 73800   8  
000060  D11 72250   9

rank() over() 跳跃排序

2、rank() over()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）．

rank
英
/ræŋk/
n.
军衔，军阶；（社会、团体中的）地位，等级；（尤指较高的）地位；
v.
把……分等级，属于某等级；<美>级别高于，地位高于；排列，使排成行
adj.
（植被）茂密的，芜生蔓长的；难闻的，恶臭的；（强调质量、状况等）极端的，糟糕的

select workdept,salary,
rank() over(partition by workdept order by salary) as dense_rank_order
-- 按照部门分组，然后按照 薪资排序
from emp order by workdept;

------------------
A00 39250   1  
A00 46500   2  
A00 49250   3  
A00 66500   4  
A00 152750  5  
B01 94250   1  
C01 68420   1  
C01 68420   1  
C01 73800   3

dense_rank() over() 连续排序

3、dense_rank() over()是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的 ．

dense
英
/dens/
adj.
稠密的；浓密的，浓重的；密度大的；（文字）难懂的；愚钝的，笨拙的

select workdept,salary,
dense_rank() over(partition by workdept order by salary) as dense_rank_order 
from emp 
order by workdept;

------------------  
A00 39250   1  
A00 46500   2  
A00 49250   3  
A00 66500   4  
A00 152750  5

B01 94250   1  
C01 68420   1  
C01 68420   1

C01 73800   2  
C01 98250   3

用ROW_NUMBER删除重复数据

假设表TAB中有a,b,c三列，可以使用下列语句删除a,b,c都相同的重复行。

DELETE FROM 
(
    select year,QUARTER,RESULTS,
    -- 分组和排序 和查出的值，都是 查出这3个字段。如果row_no不是 第一个就删除
    row_number() over(partition by YEAR,QUARTER,RESULTS order by YEAR,QUARTER,RESULTS) AS ROW_NO 
    FROM SALE ) 

WHERE ROW_NO>1