排名函數、分割函數 ROW_NUMBER、RANK、DENSE_RANK、LEAD、LAG、NTILE 的用法

 

準備資料

Drop TABLE #testTable

CREATE TABLE #testTable
(
    [id] int,
    [name] varchar(10),
	[age] int,
);

INSERT INTO #testTable ([id],[name],[age]) values (1,'Bill',40);
INSERT INTO #testTable ([id],[name],[age]) values (2,'Tom',10);
INSERT INTO #testTable ([id],[name],[age]) values (3,'Mary',60);
INSERT INTO #testTable ([id],[name],[age]) values (4,'Joe',10);
INSERT INTO #testTable ([id],[name],[age]) values (5,'Tim',60);
INSERT INTO #testTable ([id],[name],[age]) values (6,'Alice',5);

原資料內容如下

SELECT * FROM #testtable order by id;

id	name	age
1	Bill	40
2	Tom	10
3	Mary	60
4	Joe	10
5	Tim	60
6	Alice	5

一、ROW_NUMBER()

-- 對 age 排序後，再補上一行對 age 排序後的編號
SELECT ROW_NUMBER() OVER (ORDER BY [age]) AS row, a.*
FROM #testtable a;

row	id	name	age
1	6	Alice	5
2	2	Tom	10
3	4	Joe	10
4	1	Bill	40
5	5	Tim	60
6	3	Mary	60

二、RANK()

-- 對 age 排序後，再補上一行對 age 排序後之排名(同名次之後會跳號)
SELECT RANK() OVER (ORDER BY [age]) AS row, a.*
FROM #testtable a;

row	id	name	age
1	6	Alice	5
2	2	Tom	10
2	4	Joe	10
4	1	Bill	40
5	5	Tim	60
5	3	Mary	60

三、DENSE_RANK()

-- 對 age 排序後，再補上一行對 age 排序後之排名(同名次之後不會跳號)
SELECT DENSE_RANK() OVER (ORDER BY [age]) AS row, a.*
FROM #testtable a;

row	id	name	age
1	6	Alice	5
2	2	Tom	10
2	4	Joe	10
3	1	Bill	40
4	5	Tim	60
4	3	Mary	60

四、LEAD()

-- 對 age 排序後，再補上一行對 age 排序後之下一筆 age 值
SELECT * FROM #testtable order by age;
SELECT LEAD(age) OVER (ORDER BY [age]) AS row, a.*
FROM #testtable a;

row	id	name	age
10	6	Alice	5
10	2	Tom	10
40	4	Joe	10
60	1	Bill	40
60	5	Tim	60
null	3	Mary	60

五、LAG()

-- 對 age 排序後，再補上一行對 age 排序後之上一筆 age 值
SELECT * FROM #testtable order by age;
SELECT LAG(age) OVER (ORDER BY [age]) AS row, a.*
FROM #testtable a;

row	id	name	age
null	6	Alice	5
5	2	Tom	10
10	4	Joe	10
10	1	Bill	40
40	5	Tim	60
60	3	Mary	60

六、NTILE()

-- 對 age 排序後，再補上一行對 age 排序後之自動對 age 值之等級判斷(此例有三個等級)
SELECT * FROM #testtable order by age;
SELECT NTILE(3) OVER (ORDER BY [age]) AS row, a.*
FROM #testtable a;

row	id	name	age
1	6	Alice	5
1	2	Tom	10
2	4	Joe	10
2	1	Bill	40
3	5	Tim	60
3	3	Mary	60