oracle实现查询某状态标志位最大连续出现次数

展开全文

oracle实现查询某状态标志位最大连续出现次数( row_number() over(PARTITION BY)使用 )

2018年01月02日 12:40:19

阅读数：252

首先讲下需求背景：

系统中有一张记录交易流水的表，

表结构大致包含：交易流水、交易状态（成功0、失败3、未知2）、交易时间、等等其他业务相关字段。

业务场景：交易流水为记录每天交易情况的表，鉴于实际情况可能出现连续交易失败的情况，也可能会有连续成功中间夹着这未知或者失败的情况。

业务需求：查出最大连续失败次数。

下图是数据库中部分数据展示，可以看到其中包含连续出现失败3 的状态码。

具体该如何实现该功能呢？

这里使用了oracle的分析函数。ROW NUMBER() OVER()

ROW_NUMBER() OVER (ORDER BY col1 DESC) 是先把col1 列降序，再为降序以后的没条col1 记录返回一个序号。

ROW_NUMBER() OVER(partition by col1 order by col2) 表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内是连续且唯一的）。

具体实现sql如下：


select trans_status, count(1)
  from (select trans_id,
               trans_status,
               -- row_number() over(order by trans_id),
               -- row_number() over(partition by trans_status order by trans_id),
               row_number() over(order by trans_id) - row_number() over(partition by trans_status order by trans_id) sx
          from wallet_txn_info
         where 
            to_char(create_timestamp, 'yyyymmdd') = '2018101'
         order by trans_id)
 where trans_status = '3'
 group by trans_status, sx

查询结果：

至此只需在外部套一层 max就能查处最大连续出现次数；

分析：

使用 row_number() over(order by trans_id) 减去 row_number() over(partition by trans_status order by trans_id) 的原理在于前半部是根据transid排序的序列号，后半部为根据trans_status分组后再根据transid排序的序列号。当出现连续的相同trans_status时其前半部序列减去后半部序列的差值是一样的。最终根据差值group即出结果。