Oracle的LAG和LEAD分析函数

Lag函数和Lead函数

这两个函数可以用来求环比和同比。贼好用

• Lag函数可以在一次查询中取出当前行的同一字段的前面第N行的数据。
• Lead函数可以在一次查询中取出当前行的同一字段的后面第N行的值。
• 这种操作可以使用对相同表的表连接来实现，不过使用Lag和Lead有更高的效率。
• lag(exp_str,offset,defval) over(partion by …order by …)
• lead(exp_str,offset,defval) over(partion by …order by …)

1） 其中exp_str是字段名，

2）offset是偏移量，即是上1个或上N个的值，假设当前行在表中排在第5行，则offset为3，则表示我们所要找的数据行就是表中的第2行（即5-3=2）。

3）defval默认值，当两个函数取上N/下N个值，当在表中从当前行位置向前数N行已经超出了表的范围时，lag（）函数将defval这个参数值作为函数的返回值，若没有指定默认值，则返回NULL，那么在数学运算中，总要给一个默认值才不会出错。

稍后用实际的案例说明。我们先了解下分析函数。

分析函数是什么

分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。

分析函数的形式

分析函数带有一个开窗函数over()，包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) ，窗口就是分析函数分析时要处理的数据范围，他们的使用形式如下：over(partition by xxx order by yyy rows between zzz)。

数据库

表中的数据如下

同比增长率＝（本期数－同期数）/同期数100％ 指和去年同期相比较的增长率。
环比增长率＝（本期数－上期数）/上期数100％ 反映本期比上期增长了多少。

同比增长率案例

我在不知道这个函数之前求同比时是用两个sql语句分别求出今年的数值和去年的数值，然后用union all联合起来再加上group by来计算，这样的sql语句会比较复杂。

现在我们使用lag函数来慢慢完成这个功能

如：求2019年每个月的同比增长率

思路：如一行中需要有2019年某月的数据，也需要有2018年某月的数据

  select a.year,
         a.month,
         a.money,
         lag(a.money, 1, 0) over(partition by month order by year) last_money
    from TEMP_DATA a

查询结果

分析

lag(a.money, 1, 0)表示上一行的money值，如果没有的话就默认为0。所以我们在开窗函数over里面需要对月进行分组，然后按年来升序，这样上一行的数据就是我们想要的本期和同期的数据了。

完整sql

select m.year, m.month, decode(m.last_money,0,0,round((m.money-m.last_money)/m.last_money,2))
  from (
  select a.year,
               a.month,
               a.money,
               lag(a.money, 1, 0) over(partition by month order by year) last_money
          from TEMP_DATA a
          ) m
 where m.year='2019'
 order by m.year, to_number(m.month)

查询结果

环比增长率

sql主要就是要在一行里面既有本期的数据也有上期数据

  select a.year,
         a.month,
         a.money,
         lag(a.money, 1, 0) over(order by year, to_number(month) asc) last_money
    from TEMP_DATA a

查询结果

分析

lag(a.money, 1, 0)表示上一行的money值，如果没有的话就默认为0。所以我们在开窗函数over里面需要对年进行分组，然后按月来升序，这样上一行的数据就是我们想要的本期以及上期数据了。

完整sql

select m.year,
       m.month,
       decode(m.last_money,
              0,
              0,
              round((m.money - m.last_money) / m.last_money, 2))
  from (select a.year,
               a.month,
               a.money,
               lag(a.money, 1, 0) over(order by year, to_number(month) asc) last_money
          from TEMP_DATA a) m
 where m.year = '2019'

查询结果