SQL Server学习笔记6之--排名函数和缺失 已有范围

1.排名函数：
SQL2005引进了4个新的排序函数,分别是 row_number  rank dense_rank ntile .我不知道怎么排版写,就散乱地写点吧。

a.row_number :
row_number()不仅在排名上有作用,而且在许多实际问题上都有不错的应用,效率也非常不错.
排名函数 就是用来为记录添上编号--行号。这里说个分区排名的用法的内在介绍吧
row_number() over(partition by col1 order by col2)
这里就是需要先按分区列再按排序列对数据排序。 你看执行计划，会发现一个叫系列摄影的运算符号,它负责计算排名值.对于表中的每条记录,她要检查2个方面.
1) 该行是否为分区的第一行? 意思就是检查你COL1列的值和上一个PARTITION BY的值是否相同。如果不同，就说明它是第一行，那么返回一个TRUE；
否则返回FALSE;返回TRUE就要重新排名,从1开始.普通的ROW_NUMBER() 没有分区 那么它只有第一行为TRUE 其他都是false,则一直排序下去。
2) 该行是否不同于上一个值？意思就是和上面一个值比较.就相当于在一个分区内根据条件的排序规则排序。这里的ROW_NUMBER 是一定会为每行递增下去的，
但是RANK DECSE_RANK是会经过判断后，决定是否递增的.
介绍几个情况：
SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECT_ID('dbo.Sales') IS NOT NULL
  DROP TABLE dbo.Sales;
GO
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);
INSERT INTO dbo.Sales(empid, mgrid, qty) VALUES('B', 'X', 100);
INSERT INTO dbo.Sales(empid, mgrid, qty) VALUES('C', 'X', 200);
INSERT INTO dbo.Sales(empid, mgrid, qty) VALUES('D', 'Y', 200);
INSERT INTO dbo.Sales(empid, mgrid, qty) VALUES('E', 'Z', 250);
INSERT INTO dbo.Sales(empid, mgrid, qty) VALUES('F', 'Z', 300);
INSERT INTO dbo.Sales(empid, mgrid, qty) VALUES('G', 'X', 100);
INSERT INTO dbo.Sales(empid, mgrid, qty) VALUES('H', 'Y', 150);
INSERT INTO dbo.Sales(empid, mgrid, qty) VALUES('I', 'X', 250);
INSERT INTO dbo.Sales(empid, mgrid, qty) VALUES('J', 'Z', 100);
INSERT INTO dbo.Sales(empid, mgrid, qty) VALUES('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);
如果有唯一的列只根据EMPID排序)
2000:
SELECT empid,
  (SELECT COUNT(*)
   FROM dbo.Sales AS S2
   WHERE S2.empid <= S1.empid) AS rownum
FROM dbo.Sales AS S1
ORDER BY empid;
2005:
select empid,
rownum=ROW_NUMBER() over(order by empid)
from Sales
--顺便提下:通过执行计划你可以清楚看到后者强烈的优势
如果只有一列不够唯一,需要加附加列来确定唯一的排名(根据qty,empid排序)
--2000
SELECT empid, qty,
  (SELECT COUNT(*)
   FROM dbo.Sales AS S2
   WHERE S2.qty < S1.qty
      OR (S2.qty = S1.qty AND S2.empid <= S1.empid)) AS rownum
FROM dbo.Sales AS S1
ORDER BY qty, empid;
--2005
select
empid,qty,
rownum=ROW_NUMBER()over(order by qty,empid)
from Sales
--顺便提下:通过执行计划你可以清楚看到后者强烈的优势
如果只有一列,而且没有附加的列
IF OBJECT_ID('dbo.T1') IS NOT NULL
  DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1(col1 VARCHAR(5));
INSERT INTO dbo.T1(col1) VALUES('A');
INSERT INTO dbo.T1(col1) VALUES('A');
INSERT INTO dbo.T1(col1) VALUES('A');
INSERT INTO dbo.T1(col1) VALUES('B');
INSERT INTO dbo.T1(col1) VALUES('B');
INSERT INTO dbo.T1(col1) VALUES('C');
INSERT INTO dbo.T1(col1) VALUES('C');
INSERT INTO dbo.T1(col1) VALUES('C');
INSERT INTO dbo.T1(col1) VALUES('C');
INSERT INTO dbo.T1(col1) VALUES('C');

--2000
SELECT number + smaller AS rownum, col1
FROM (SELECT col1, COUNT(*) AS dups,
        (SELECT COUNT(*) FROM dbo.T1 AS B
         WHERE B.col1 < A.col1) AS smaller
      FROM dbo.T1 AS A
      GROUP BY col1) AS D, master..spt_values
WHERE number <= dups and type='p'
order by col1
--2005
select col1,
rownum=ROW_NUMBER() over(order by col1 )
from t1
可能大家发现了一个东西我没写 IDENTITY.它其实是2005之前一个计算行号非常常用效率也很高的方法.
一般的思路就是:通过IDENTITY带着数据放入一个临时表,之后就可以用行号了,就是多了个临时表.
当然你也可以用游标处理：
DECLARE @SalesRN TABLE(empid VARCHAR(5), qty INT, rn INT);
DECLARE @empid AS VARCHAR(5), @qty AS INT, @rn AS INT;
BEGIN TRAN
DECLARE rncursor CURSOR FAST_FORWARD FOR
  SELECT empid, qty FROM dbo.Sales ORDER BY qty, empid;
OPEN rncursor;
SET @rn = 0;
FETCH NEXT FROM rncursor INTO @empid, @qty;
WHILE @@fetch_status = 0
BEGIN
  SET @rn = @rn + 1;
  INSERT INTO @SalesRN(empid, qty, rn) VALUES(@empid, @qty, @rn);
  FETCH NEXT FROM rncursor INTO @empid, @qty;
END
CLOSE rncursor;
DEALLOCATE rncursor;
COMMIT TRAN
SELECT empid, qty, rn FROM @SalesRN;
--关于游标应该尽量避免使用,因为它包含太多的开销.当然在分区非常小,它的速度也会非常快。一般表越小,它的速度也越快。
经过书上测试:这几个方法排序是->row_number()<identity<cursor<2000的子查询
b.RANK ,Dense RANK,NTILE
它们分别用于排名 积分应用 计算百分比或组中排列各个元素
它们区别我直接给出例子，就可以很清楚的看到
create table rankorder(
orderid int,
qty int
)
go
--插入数据
insert rankorder values(30,10)
insert rankorder values(10,10)
insert rankorder values(80,10)
insert rankorder values(40,10)
insert rankorder values(30,15)
insert rankorder values(30,20)
insert rankorder values(22,20)
insert rankorder values(21,20)
insert rankorder values(10,30)
insert rankorder values(30,30)
insert rankorder values(40,40)
go
SELECT orderid,qty,
  ROW_NUMBER() OVER(ORDER BY qty) AS rownumber,
  RANK()       OVER(ORDER BY qty) AS [rank],
  DENSE_RANK() OVER(ORDER BY qty) AS denserank ,
  NTILE(3) OVER(ORDER BY qty) AS [NTILE]
FROM rankorder
ORDER BY qty
--结果
--ROW_NUMBER()是按qty由小到大逐一排名，不并列，排名连续
--RANK()是按qty由小到大逐一排名，并列，排名不连续
--DENSE_RANK()是按qty由小到大逐一排名，并列，排名连续
--NTILE()是按qty由小到大分成3组逐一排名，并列，排名连续
/*
orderid    qty    rownumber    rank    denserank    NTILE
30        10    1            1        1            1
10        10    2            1        1            1
80        10    3            1        1            1
40        10    4            1        1            1
30        15    5            5        2            2
30        20    6            6        3            2
22        20    7            6        3            2
21        20    8            6        3            2
10        30    9            9        4            3
30        30    10           9        4            3
40        40    11           11       5            3*/

2.已有范围和缺失范围
这是一个很实际的问题,偷懒下,我直接用书上的数据了.
USE tempdb;
GO
IF OBJECT_ID('dbo.T1') IS NOT NULL
  DROP TABLE dbo.T1
GO
CREATE TABLE dbo.T1(col1 INT NOT NULL PRIMARY KEY);
INSERT INTO dbo.T1(col1) VALUES(1);
INSERT INTO dbo.T1(col1) VALUES(2);
INSERT INTO dbo.T1(col1) VALUES(3);
INSERT INTO dbo.T1(col1) VALUES(100);
INSERT INTO dbo.T1(col1) VALUES(101);
INSERT INTO dbo.T1(col1) VALUES(103);
INSERT INTO dbo.T1(col1) VALUES(104);
INSERT INTO dbo.T1(col1) VALUES(105);
INSERT INTO dbo.T1(col1) VALUES(106);
--首先要看的是缺失范围的查询:
--方法1:找出断点的第一个数字后,找出距离这个断点数字最近的数字-1(关于找出缺失数字我前面的学习笔记有介绍了几种方法)
select col1+1 as startnum,
(select MIN(col1)-1 from T1 where k.col1<col1) as endnum
from T1 k
where not exists(select * from T1 where k.col1+1=col1)
and col1<(select MAX(col1) from T1)--防止最大数字的后一个数字出现
--方法2：利用前后2个数字相减的差来判断是否连续
select CUr+1 as stratnum,nxt-1 as endnum
from(
select col1 as cur,(select MIN(col1) from T1 where k.col1<col1) as nxt
from T1 k) p
where nxt-cur>1
/*
stratnum    endnum
----------- -----------
4           99
102         102
*/
--再来看看已有范围的查询
--方法1：利用比当前值大的最接近最小间断值的值
select MIN(col1) as startnum,
grp as endnum
from(
select col1,
(select MIN(col1) from T1 a where a.col1>=k.col1 and
    not exists(select * from T1 where a.col1+1=col1 )) as grp
from T1 k ) z
group by grp
--方法2:利用ROW_NUMBER 以前看P梁写过,就是产生一个分组因子（上面那个方法是通过子查询来确定一个分组因子其实）
select MIN(col1) as startnum,
max(col1) as endnum
from(
select col1,
col1-ROW_NUMBER()over(order by col1) as grp
from T1 k ) z
group by grp
/*
startnum    endnum
----------- -----------
1           3
100         101
103         106
*/