排名函数(ROW_NUMBER、RANK、DENSE_RANK)及OVER子句

--在部门内新水排名（如果有相同名次，用dense_rank)
SELECT row_number() over
(PARTITION BY t.deptno  --按部门分组
ORDER BY t.sal) rn, --部门内按薪水排序
 t.sal, t.ename, t.deptno 
  FROM emp t;
--分组累计计数，注意相同sal情况
SELECT COUNT(*) over(PARTITION BY t.deptno ORDER BY sal) cn, t.sal, t.ename, t.deptno 
FROM emp t;


一、排名函数
1>、计算行号ROW_NUMBER函数
2>、排名和密集排号RANK和DESN_RANK

--示例:
CREATE TABLE dbo.Sales
(
  empid VARCHAR(10) NOT NULL PRIMARY KEY,
  mgrid VARCHAR(10) NOT NULL,
  qty   INT         NOT NULL
);

INSERT INTO dbo.Sales(empid, mgrid, qty) VALUES
  ('A', 'Z', 300),
  ('B', 'X', 100),
  ('C', 'X', 200),
  ('D', 'Y', 200),
  ('E', 'Z', 250),
  ('F', 'Z', 300),
  ('G', 'X', 100),
  ('H', 'Y', 150),
  ('I', 'X', 250),
  ('J', 'Z', 100),
  ('K', 'Y', 200);

CREATE INDEX idx_qty_empid ON dbo.Sales(qty, empid);
CREATE INDEX idx_mgrid_qty_empid ON dbo.Sales(mgrid, qty, empid);
GO

select ROW_NUMBER() OVER (ORDER BY qty) AS seq,
RANK()OVER (ORDER BY qty) AS seq1,
 DENSE_RANK() OVER (ORDER BY qty) AS seq2,
 *
FROM Sales s			
--运行结果

seq	seq1	seq2	empid	mgrid	qty
1	1	1	B	X	100
2	1	1	G	X	100
3	1	1	J	Z	100
4	4	2	H	Y	150
5	5	3	C	X	200
6	5	3	D	Y	200
7	5	3	K	Y	200
8	8	4	E	Z	250
9	8	4	I	X	250
10	10	5	A	Z	300
11	10	5	F	Z	300


区别:当order by 列表不能唯一决定排序顺序时，ROW_NUMBER是非确定性的。而RANK和DENSE_RANK总是确定性的。即,具有相同排序值的行总是得到相同的排名值。而RANK和DENSE_RANK的区别在于,RANK生成的排名值可能有间断，但可以表明有多少行具有更低的排序值。DENSE_RANK生成的排名值没有间断。



--分析函数：
count(a) over (partition by b order by c)
--上面的count()就是一个分析函数；over可以理解为一个关键字或者标识，有over就表示它前面的函数
--是一个分析函数，否则就是普通的求和函数了；
--（）中的partition by是指定分区，或者理解为分组
--（）中的order by是指定该分区内的数据的顺序