分析函数-CSDN博客

本文链接：https://blog.csdn.net/xiongjin621/article/details/83526177

   --row_number()
   SELECT d.department_name,
          e.last_name,
          e.salary,
          row_number() OVER(PARTITION BY e.department_id ORDER BY e.salary) as drank
     FROM employees e, departments d
    WHERE e.department_id = d.department_id;

   --DENSE_RANK()
   SELECT d.department_name,
          e.last_name,
          e.salary,
          DENSE_RANK() OVER(PARTITION BY e.department_id ORDER BY e.salary) as drank
     FROM employees e, departments d
    WHERE e.department_id = d.department_id;

   --RANK() 

   SELECT d.department_name,
          e.last_name,
          e.salary,
          RANK() OVER(PARTITION BY e.department_id ORDER BY e.salary) as drank
     FROM employees e, departments d
    WHERE e.department_id = d.department_id


结果分别是：
row_number
1	Administration	Whalen	4400.00	1
2	Marketing	Fay	6000.00	1
3	Marketing	Hartstein	13000.00	2
4	Purchasing	Colmenares	2500.00	1
5	Purchasing	Himuro	2600.00	2
6	Purchasing	Tobias	2800.00	3
7	Purchasing	Baida	2900.00	4
8	Purchasing	Raphaely	11000.00	5
9	Purchasing	Khoo	11000.00	6
10	Human Resources	Mavris	6500.00	1
11	Shipping	Olson	2100.00	1
12	Shipping	Markle	2200.00	2
13	Shipping	Philtanker	2200.00	3
14	Shipping	Landry	2400.00	4
15	Shipping	Gee	2400.00	5
16	Shipping	Marlow	2500.00	6
17	Shipping	Perkins	2500.00	7
18	Shipping	Sullivan	2500.00	8
19	Shipping	Vargas	2500.00	9
20	Shipping	Patel	2500.00	10
21	Shipping	Matos	2600.00	11
22	Shipping	OConnell	2600.00	12
23	Shipping	Grant	2600.00	13
24	Shipping	Mikkilineni	2700.00	14


DENSE_RANK
1	Administration	Whalen	4400.00	1
2	Marketing	Fay	6000.00	1
3	Marketing	Hartstein	13000.00	2
4	Purchasing	Colmenares	2500.00	1
5	Purchasing	Himuro	2600.00	2
6	Purchasing	Tobias	2800.00	3
7	Purchasing	Baida	2900.00	4
8	Purchasing	Raphaely	11000.00	5
9	Purchasing	Khoo	11000.00	5
10	Human Resources	Mavris	6500.00	1
11	Shipping	Olson	2100.00	1
12	Shipping	Markle	2200.00	2
13	Shipping	Philtanker	2200.00	2
14	Shipping	Landry	2400.00	3
15	Shipping	Gee	2400.00	3
16	Shipping	Marlow	2500.00	4
17	Shipping	Perkins	2500.00	4
18	Shipping	Sullivan	2500.00	4
19	Shipping	Vargas	2500.00	4
20	Shipping	Patel	2500.00	4
21	Shipping	Matos	2600.00	5
22	Shipping	OConnell	2600.00	5
23	Shipping	Grant	2600.00	5
24	Shipping	Mikkilineni	2700.00	6


RANK
1	Administration	Whalen	4400.00	1
2	Marketing	Fay	6000.00	1
3	Marketing	Hartstein	13000.00	2
4	Purchasing	Colmenares	2500.00	1
5	Purchasing	Himuro	2600.00	2
6	Purchasing	Tobias	2800.00	3
7	Purchasing	Baida	2900.00	4
8	Purchasing	Raphaely	11000.00	5
9	Purchasing	Khoo	11000.00	5
10	Human Resources	Mavris	6500.00	1
11	Shipping	Olson	2100.00	1
12	Shipping	Markle	2200.00	2
13	Shipping	Philtanker	2200.00	2
14	Shipping	Landry	2400.00	4
15	Shipping	Gee	2400.00	4
16	Shipping	Marlow	2500.00	6
17	Shipping	Perkins	2500.00	6
18	Shipping	Sullivan	2500.00	6
19	Shipping	Vargas	2500.00	6
20	Shipping	Patel	2500.00	6
21	Shipping	Matos	2600.00	11
22	Shipping	OConnell	2600.00	11
23	Shipping	Grant	2600.00	11
24	Shipping	Mikkilineni	2700.00	14

结论是：row_number中order列不管是否有重复，number都按序列递增，dense_rank和rank则是roder列有重复，那么number列保持重复，但是dense_rank和rank不同的是rank对于每个重复都会记住，等不重复的时候在加起来，比如
14	Shipping	Landry	2400.00	4
15	Shipping	Gee	2400.00	4
16	Shipping	Marlow	2500.00	6
17	Shipping	Perkins	2500.00	6
18	Shipping	Sullivan	2500.00	6
19	Shipping	Vargas	2500.00	6
20	Shipping	Patel	2500.00	6
21	Shipping	Matos	2600.00	11
22	Shipping	OConnell	2600.00	11
23	Shipping	Grant	2600.00	11

1234