基于lag的条件的Oracle SQL ROW_NUMBER()窗口函数

Question

使用仅具有选择权限的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,1,2016 FROM DUAL
UNION SELECT 10000,'VARCHAR DESCRIPTION',75057,1,2,2016 FROM DUAL
UNION SELECT 10000,'VARCHAR DESCRIPTION',75057,1,3,2016 FROM DUAL
UNION SELECT 10000,'VARCHAR DESCRIPTION',75057,0,4,2016 FROM DUAL
UNION SELECT 10000,'VARCHAR DESCRIPTION',75057,1,5,2016 FROM DUAL
UNION SELECT 10000,'VARCHAR DESCRIPTION',75057,0,6,2016 FROM DUAL
UNION SELECT 10000,'VARCHAR DESCRIPTION',75057,0,7,2016 FROM DUAL
UNION SELECT 10000,'VARCHAR DESCRIPTION',75057,1,8,2016 FROM DUAL
UNION SELECT 10000,'VARCHAR DESCRIPTION',75057,0,9,2016 FROM DUAL
UNION SELECT 10000,'VARCHAR DESCRIPTION',75057,0,10,2016 FROM DUAL
UNION SELECT 10000,'VARCHAR DESCRIPTION',75057,1,11,2016 FROM DUAL
UNION SELECT 10000,'VARCHAR DESCRIPTION',75057,0,12,2016 FROM DUAL
UNION SELECT 10000,'VARCHAR DESCRIPTION',75057,0,1,2017 FROM DUAL
UNION SELECT 10000,'VARCHAR DESCRIPTION',75057,0,2,2017 FROM DUAL
UNION SELECT 10000,'VARCHAR DESCRIPTION',75057,0,3,2017 FROM DUAL
UNION SELECT 10000,'VARCHAR DESCRIPTION',75057,0,4,2017 FROM DUAL
UNION SELECT 10000,'VARCHAR DESCRIPTION',75057,0,5,2017 FROM DUAL
UNION SELECT 10000,'VARCHAR DESCRIPTION',75057,0,6,2017 FROM DUAL
UNION SELECT 10000,'VARCHAR DESCRIPTION',75057,0,7,2017 FROM DUAL

)

SELECT
   FK_ID
,  FK_NAME
,  PK_ID
,  BIN_FLAG
,  ROW_NUMBER() OVER (PARTITION BY FK_ID,PK_ID,BIN_FLAG ORDER BY YEAR,MONTH,FK_ID,PK_ID ASC) NEEDED_CALC
,  MONTH
,  YEAR
FROM MYTABLE
ORDER BY FK_ID,PK_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

我需要的是每当上个月的 bin_flag 改变时，NEEDED_CALC 列都要重新计算。

因此，如果 bin_flag = 1 并且通过 lag 函数确定上一个月的 bin_flag 不同，则在 NEEDED_CALC 中的计数器列将被重置，并从 1 开始重新开始计算。

- Jeff

---

糟糕 - 这里不支持HTML...我该如何制作一个表格？ - Jeff

@siyual 感谢您清理了那里的结果 :) - Jeff

Answer 1

这是一个“间隙和岛屿”问题。关键在于为具有相同值的相邻行分配组标识符。有两种简单的方法可以做到这一点：一种基于lag()，另一种是row_number()之差。

第二种方法稍微简单一些，只需要一个子查询层级：

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的值，以了解其工作原理。

- Gordon Linoff

---

感谢您的帮助 - 正是我想要的！ - Jeff

Answer 2

如果你有幸在使用12c数据库，你可以使用match recognize子句来完成这个任务。在这种情况下，我告诉匹配引擎查找0行或多行的组，其中bin_flag等于前一行上的bin_flag，使用与问题中相同的排序方式。

WITH MYTABLE (FK_ID,FK_NAME,PK_ID,BIN_FLAG,MONTH,YEAR)AS (
 <<<as from the question>>>> 
)
SELECT
 FK_ID 
,FK_NAME
,PK_ID
,BIN_FLAG
,seq NEEDED_CALC
,MONTH
,YEAR
FROM MYTABLE
MATCH_RECOGNIZE (
  ORDER BY year,month,fk_id,pk_id asc
  MEASURES
    count(*) +1 seq 
  ALL ROWS PER MATCH
  PATTERN (a*)
  DEFINE 
  a AS bin_flag = prev(bin_flag)
  )
ORDER BY FK_ID,PK_ID,YEAR,MONTH

Match Recognize可以成为这种需要在行组之间查找模式的查询中有用的工具。

- James

---

不幸的是，我被困在11g中。我也在阅读相关文档，看起来这是一个CQL查询函数，而我并不熟悉它。我正在使用SQL编写代码。 - Jeff

这绝对是SQL（也许不是ANSI SQL，但在Oracle上肯定有效）。正如你所说，它在11g上不起作用（Gordon的答案可能是你最好的选择 - 或许还有另一种使用MODEL子句的方法，但那会更加繁琐）。如果你想在12g上尝试它，可以使用：http://www.oracle.com/technetwork/database/application-development/livesql/index.html - James

这是正确的，但可能不是最有效的。在匹配之后，紧接着的下一行不是 a - 因此找到了一个空匹配。然后新行变成“prev”，并开始一个新的非空匹配。似乎更自然（也许略微更有效）的方法是使用模式 (z a*)，其中 z 没有定义，而 a 则完全按照您的定义。否则 - 这是适用于 Oracle 12 的正确方法！ - user5683823