sql使用技巧--排名函数、日期类型处理、LAG、LEAD

1.排名函数

ROW_NUMBER

他会为查询出来的每一行记录生成一个序号，依次排序且不会重复。示例代码如下：

select ROW_NUMBER() OVER(order by [price] desc) as row_num,* from [Order]

原始数据为

id	price
1	200
2	400
3	300
4	700
5	500

执行结果为：

row_num	id	price
1	4	700
2	5	500
3	2	400
4	3	300
5	2	200

 

RANK

rank函数用于返回结果集的分区内每行的排名，简单来说rank函数就是对查询出来的记录进行排名，与row_number函数不同的是，rank函数考虑到了over子句中排序字段值相同的情况，也就是并列的情况，如果两行数据并列，他们的名次一样，下一行记录的名次不是简单的增加1，而是由已排完的记录数决定。示例代码如下：

select RANK() OVER(order by [id]) as rank,* from [Order] 

原始数据为

id	price
1	200
1	400
2	300
2	700
3	500

执行结果为：

rank	id	price
1	1	200
1	1	400
3	2	300
3	2	700
5	3	500

DENSE_RANK

dense_rank函数的功能与rank函数类似，dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续。dense_rank函数出现相同排名时，将不跳过相同排名号，rank值紧接上一次的rank值。示例代码如下：

select DENSE_RANK() OVER(order by [id]) as den_rank,* from [Order]

原始数据为

id	price
1	200
1	400
2	300
2	700
3	500

执行结果为：

den_rank	id	price
1	1	200
1	1	400
2	2	300
2	2	700
3	3	500

 

NTILE

ntile函数可以对序号进行分组处理，ntile 将返回此行所属的组的编号。示例代码如下：

select NTILE(3) OVER(order by [price] desc) as ntile,* from [Order]

原始数据为

id	price
1	200
2	400
3	300
4	700
5	500

执行结果为：

ntile	id	price
1	4	700
1	5	500
2	2	400
2	3	300
3	2	200

2.日期类型处理

DATE_ADD() 

向日期添加指定的时间间隔。

时间间隔类型包括：

时间间隔类型值
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

示例如下：

SELECT OrderId,DATE_ADD(OrderDate,INTERVAL 45 DAY) AS OrderPayDate
FROM Orders

 

执行前：

OrderId	ProductName	OrderDate
1	Jarlsberg Cheese	2008-11-11 13:23:44.657

 

执行后：

OrderId	OrderPayDate
1	2008-12-26 13:23:44.657

 

DATEDIFF()

函数返回两个日期之间的天数。

代码示例：

SELECT DATEDIFF('2008-11-30','2008-11-29') AS DiffDate

返回结果1。

DATE_FORMAT()

函数用于以不同的格式显示日期/时间数据。

示例代码：

DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')
DATE_FORMAT(NOW(),'%m-%d-%Y')
DATE_FORMAT(NOW(),'%d %b %y')
DATE_FORMAT(NOW(),'%d %b %Y %T:%f')

结果如下：

Nov 04 2008 11:45 PM
11-04-2008
04 Nov 08
04 Nov 2008 11:45:34:243

其中，NOW()返回当前日期时间。

3.LAG()

这个函数可以取出某个字段前N条记录的值。

示例代码：

SELECT *,LAG(price,1) OVER(ORDER BY id) AS lag FROM Orders

 

原始数据为

id	price
1	200
2	400
3	300
4	700
5	500

执行结果为：

id	price	lag
1	200
2	400	200
3	300	400
4	700	300
5	500	700

4.LEAD()

与LAG()类似，区别是查询某字段的后N条记录的值。

---

引用《Sql 四大排名函数（ROW_NUMBER、RANK、DENSE_RANK、NTILE）简介》

引用《菜鸟教程SQL Date 函数》

引用《数据库常用函数DECODE()、LAG()、LEAD() 基础用法》