Oracle SQL ROW_NUMBER()窗口,其条件基于滞后

使用Oracle SQL只选择权限,我需要根据条件提供ROW_NUMBER输出.使用游标或循环这很容易,但目前我必须只使用SQL来执行此任务.

我一直在修补row_number()over子句,我认为这是正确的方法,但我现在卡住了.

我目前的代码 – 或者至少是它的代理：

WITH MYTABLE (FK_ID,FK_NAME,PK_ID,BIN_FLAG,MONTH,YEAR)AS (
      SELECT 10000,'VARCHAR DESCRIPTION',75057,1,2016 FROM DUAL
UNION SELECT 10000,2,3,4,5,6,7,8,9,10,11,12,2017 FROM DUAL
UNION SELECT 10000,2017 FROM DUAL

)

SELECT
   FK_ID,ROW_NUMBER() OVER (PARTITION BY FK_ID,BIN_FLAG ORDER BY YEAR,FK_ID,PK_ID ASC) NEEDED_CALC,YEAR
FROM MYTABLE
ORDER BY FK_ID,YEAR,MONTH

这将返回一个如下所示的数据集：

FK_ID   FK_NAME             PK_ID       BIN_FLAG    NEEDED_CALC MONTH   YEAR
10000   VARCHAR DESCRIPTION 75057       1           1           1       2016
10000   VARCHAR DESCRIPTION 75057       1           2           2       2016
10000   VARCHAR DESCRIPTION 75057       1           3           3       2016
10000   VARCHAR DESCRIPTION 75057       0           1           4       2016
10000   VARCHAR DESCRIPTION 75057       1           4           5       2016
10000   VARCHAR DESCRIPTION 75057       0           2           6       2016
10000   VARCHAR DESCRIPTION 75057       0           3           7       2016
10000   VARCHAR DESCRIPTION 75057       1           5           8       2016
10000   VARCHAR DESCRIPTION 75057       0           4           9       2016
10000   VARCHAR DESCRIPTION 75057       0           5           10      2016
10000   VARCHAR DESCRIPTION 75057       1           6           11      2016
10000   VARCHAR DESCRIPTION 75057       0           6           12      2016
10000   VARCHAR DESCRIPTION 75057       0           7           1       2017
10000   VARCHAR DESCRIPTION 75057       0           8           2       2017
10000   VARCHAR DESCRIPTION 75057       0           9           3       2017
10000   VARCHAR DESCRIPTION 75057       0           10          4       2017
10000   VARCHAR DESCRIPTION 75057       0           11          5       2017
10000   VARCHAR DESCRIPTION 75057       0           12          6       2017
10000   VARCHAR DESCRIPTION 75057       0           13          7       2017

我需要的是NEEDED_CALC列,以便在上个月的bin_flag改变时重置calc.

因此,如果bin_flag = 1且前一个月的bin_flag(通过滞后函数识别)不同,则NEEDED_CALC中的计数器列将重置并再次从1开始.

解决方法

第二个是更简单,只需要一个子查询级别：

select t.*,row_number() over (partition by fk_id,bin_flag,seqnum_ym - seqnum_bym
                          order by year,month
                         ) as needed_calc
from (select t.*,row_number() over (partition by fk_id order by year,month) as seqnum_ym,row_number() over (partition by fk_id order by bin_flag,year,month) as seqnum_bym
      from mytable t
     ) t;

行数的差异并不难理解,但确实需要概念上的飞跃.我建议您运行子查询,看看seqnum_ym和seqnum_bym的值是什么,以了解它是如何工作的.

（编辑：通辽站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!