oracle lead/lag 函数 ignore nulls 功能在11g以下版本的实现

我就是我 2021-09-26 17:04 622阅读 0赞

*   *   *   *  建立测试表
             *  11g中的写法
             *  model写法
             *  使用first\_value/last\_value+ignore的方法
             *  自连接
             *  其他

oracle在11g中允许对lead/lag分析函数使用`ignore nulls` 语法以排除空值。这是一个很实用的语法，然而在低版本中却不能使用。本文将介绍几种可以在较低版本中实现类似功能的sql写法。

#### 建立测试表 ####

create table nayi_test_180515 as select level ord, decode(level, 5, null, 6, null, level) num from dual connect by level <= 8;
    
    select*from nayi_test_180515;

对测试数据做了简化。其中ORD列视为按指定排序后获得的递增的唯一序列，NUM列视为数据列。

#### 11g中的写法 ####

select t1.ord, t1.num, lag(t1.num) over(order by t1.ord) lag_, lag(t1.num) ignore nulls over(order by t1.ord) lag_ignore_, lead(t1.num) over(order by t1.ord) lead_, lead(t1.num ignore nulls) over(order by t1.ord) lead_ignore_ from nayi_test_180515 t1 order by t1.ord;

<table> 
 <thead> 
  <tr> 
   <th>ORD</th> 
   <th>NUM</th> 
   <th>LAG_</th> 
   <th>LAG_IGNORE_</th> 
   <th>LEAD_</th> 
   <th>LEAD_IGNORE_</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>1</td> 
   <td></td> 
   <td></td> 
   <td>2</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>2</td> 
   <td>1</td> 
   <td>1</td> 
   <td>3</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>3</td> 
   <td>2</td> 
   <td>2</td> 
   <td>4</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>4</td> 
   <td>3</td> 
   <td>3</td> 
   <td></td> 
   <td>7</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td></td> 
   <td>4</td> 
   <td>4</td> 
   <td></td> 
   <td>7</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td></td> 
   <td></td> 
   <td>4</td> 
   <td>7</td> 
   <td>7</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>7</td> 
   <td></td> 
   <td>4</td> 
   <td>8</td> 
   <td>8</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>8</td> 
   <td>7</td> 
   <td>7</td> 
   <td></td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

#### model写法 ####

select*from nayi_test_180515 t1
    model
    dimension by(row_number() over(order by t1.ord) rn)
    measures(ord, num, num lag_, num lag_ignore_, num lead_, num lead_ignore_)
    rules( lag_[rn] = num[cv() - 1], lag_ignore_[rn] = nvl(num[cv() - 1], lag_ignore_[cv() - 1]), lead_[rn] = num[cv() + 1], lead_ignore_[rn] order by rn desc = nvl(num[cv() + 1], lead_ignore_[cv() + 1]) )
    ;

<table> 
 <thead> 
  <tr> 
   <th>RN</th> 
   <th>ORD</th> 
   <th>NUM</th> 
   <th>LAG_</th> 
   <th>LAG_IGNORE_</th> 
   <th>LEAD_</th> 
   <th>LEAD_IGNORE_</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>1</td> 
   <td>1</td> 
   <td></td> 
   <td></td> 
   <td>2</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>2</td> 
   <td>2</td> 
   <td>1</td> 
   <td>1</td> 
   <td>3</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>3</td> 
   <td>3</td> 
   <td>2</td> 
   <td>2</td> 
   <td>4</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>4</td> 
   <td>4</td> 
   <td>3</td> 
   <td>3</td> 
   <td></td> 
   <td>7</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>5</td> 
   <td></td> 
   <td>4</td> 
   <td>4</td> 
   <td></td> 
   <td>7</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>6</td> 
   <td></td> 
   <td></td> 
   <td>4</td> 
   <td>7</td> 
   <td>7</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>7</td> 
   <td>7</td> 
   <td></td> 
   <td>4</td> 
   <td>8</td> 
   <td>8</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>8</td> 
   <td>8</td> 
   <td>7</td> 
   <td>7</td> 
   <td></td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

#### 使用first\_value/last\_value+ignore的方法 ####

select t1.*, first_value(t1.num) over(order by t1.ord rows between 1 following and unbounded following) first_lead, first_value(t1.num ignore nulls) over(order by t1.ord rows between 1 following and unbounded following) first_ignore, last_value(t1.num) over(order by t1.ord rows between unbounded preceding and 1 preceding) last_lag, last_value(t1.num ignore nulls) over(order by t1.ord rows between unbounded preceding and 1 preceding) last_ignore from nayi_test_180515 t1 order by ord ;

<table> 
 <thead> 
  <tr> 
   <th>ORD</th> 
   <th>NUM</th> 
   <th>FIRST_LEAD</th> 
   <th>FIRST_IGNORE</th> 
   <th>LAST_LAG</th> 
   <th>LAST_IGNORE</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>1</td> 
   <td>2</td> 
   <td>2</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>2</td> 
   <td>3</td> 
   <td>3</td> 
   <td>1</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>3</td> 
   <td>4</td> 
   <td>4</td> 
   <td>2</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>4</td> 
   <td></td> 
   <td>7</td> 
   <td>3</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td></td> 
   <td></td> 
   <td>7</td> 
   <td>4</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td></td> 
   <td>7</td> 
   <td>7</td> 
   <td></td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>7</td> 
   <td>8</td> 
   <td>8</td> 
   <td></td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>8</td> 
   <td></td> 
   <td></td> 
   <td>7</td> 
   <td>7</td> 
  </tr> 
 </tbody> 
</table>

#### 自连接 ####

select t1.ord, t1.num, t3.num lag_ignore_ from nayi_test_180515 t1, (select t1.ord, max(t2.ord) t2_ord from (select * from nayi_test_180515 t1) t1, (select * from nayi_test_180515 t2 where t2.num is not null) t2 where 1 = 1 and t1.ord > t2.ord group by t1.ord) t2, nayi_test_180515 t3 where 1 = 1 and t1.ord = t2.ord(+) and t2.t2_ord = t3.ord(+) order by t1.ord;

<table> 
 <thead> 
  <tr> 
   <th>ORD</th> 
   <th>NUM</th> 
   <th>LAG_IGNORE_</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>1</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>2</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>3</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>4</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td></td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td></td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>7</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>8</td> 
   <td>7</td> 
  </tr> 
 </tbody> 
</table>

因为实现起来过于复杂并且效率非常低，只实现了`lag ignore nulls` 。

#### 其他 ####

方法肯定不只这些，欢迎交流。