LAG与LEAD:
lag与lead函数是跟偏移量相关的两个分析函数,通过这两个函数我们可以取到当前行列的偏移N行列的值 lag可以看着是正的向上的偏移 lead可以认为负的向下的偏移,简单的说,它们可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列。注意是oracle9i之后才支持的哦。
ROW_NUMBER:
Row_number函数返回一个唯一的值,当碰到相同数据时,排名按照记录集中记录的顺序依次递增。
DENSE_RANK:
Dense_rank函数返回一个唯一的值,除非当碰到相同数据时,此时所有相同数据的排名都是一样的。
RANK:
Rank函数返回一个唯一的值,除非遇到相同的数据时,此时所有相同数据的排名是一样的,同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。
这样的字面解释可能很难看懂,下面我分别用例子来描述:
LAG与LEAD
表名:tab1
—————————————————————————–
ID employees(员工姓名) month(月份) salary(工资)
1 张三 201509 4800
2 张三 201510 5000
3 张三 201511 5200
—————————————————————————–
如果我现在要分析员工张三在10月份的工资情况,要求分别列出上月工资和下月工资进行比较,要求最后结果如下:
——————————————————————————————————————————————–
ID employees(员工姓名) month(月份) salary(工资) salary_l(上月工资) salary_n(下月工资)
2 张三 201510 5000 4800 5200
——————————————————————————————————————————————–
不用case..when 语句进行判断的话,应该怎么写呢?
SELECT a.*,lead(a.salary,1,0) over(order by ID desc) salary_l,lag(a.salary,1,0) over(order by ID desc) salary_n FROM tab1 a
) WHERE ID=2
ps:lead/lag的两个参数意义
第一个参数:上面sql语句中的1代表偏移量,如果是求上两个月的salary,那就是2咯;
第二个参数:上面语句中的0代表没有符合条件的默认值。
以上是一个简单的例子,简单的总结,其实也是一个行转列的应用,简简单单的一个函数,有时候会非常有用,比如下面这个实际例子:
下面提供部分数据供各位测试及分析!
工号 卡机号 上班日期 打卡时间
00000009 09 2009/03/20 07:33
00000009 09 2009/03/20 07:34
00000009 08 2009/03/20 07:45
00000009 09 2009/03/20 12:38
00000009 09 2009/03/20 12:44
ROW_NUMBER,DENSE_RANK,RANK用法
表名:tab2
——————————————————-
ID employees(员工姓名) salary(工资)
1 张三 5000
2 李四 5000
3 王五 5200
——————————————————-
现在如果要对tab2表中员工的工资进行排名。
使用row_number排名:
结果为:
——————————————————-
ID employees(员工姓名) 排名
1 张三 1
2 李四 2
3 王五 3
——————————————————-
其实当salary字段没有相同值的时候,使用上面这三种方法都可以很轻松的排名,但上面例子中salary有相同的值,用row_number排名就不对了。
使用RANK排名:
结果为:
——————————————————-
ID employees(员工姓名) 排名
1 张三 1
2 李四 1
3 王五 3
——————————————————-
可以看到,rank进行排名时,当张三和李四salary值相同的时候,这两个人的排名会并列第一,然后跳过第二名直接就是第三名了,因为有两个第一名,所以空出了第二名。
使用DENSE_RANK排名:
结果为:
——————————————————-
ID employees(员工姓名) 排名
1 张三 1
2 李四 1
3 王五 2
——————————————————-
从结果可以看到,跟rank不同的是,DENSE_RANK方法并没有空出第二名,两个并列第一名之后,接着的是第二名。