Oracle SQL-在连续日期查找连续值 [英] Oracle SQL - Find Consecutive Values on Consecutive Dates

问题描述

我必须写一个查询来查找一个值(引用列标志)的3个或更多连续出现，并且它必须在连续的日期发生.如果该出现不是在连续的日期发生，则查询不应选择值

I have to write a query to find 3 or more consecutive occurrence of a value (Refer Column Flag) and it must happen on consecutive dates.The query should not pick values if the occurrence doesn't happen on consecutive dates For e.g

COLUMN ID   DATE            FLAG
100         10-JUL-2015     Y
100         11-JUL-2015     Y
100         12-JUL-2015     Y
100         13-JUL-2015     N
100         14-JUL-2015     Y
100         15-JUL-2015     Y
100         16-JUL-2015     N
100         17-JUL-2015     Y
100         18-JUL-2015     Y
100         19-JUL-2015     Y
100         20-JUL-2015     Y
100         21-JUL-2015     Y

输出

COLUMN ID   DATE            FLAG
100         10-JUL-2015     Y
100         11-JUL-2015     Y
100         12-JUL-2015     Y
100         17-JUL-2015     Y
100         18-JUL-2015     Y
100         19-JUL-2015     Y
100         20-JUL-2015     Y
100         21-JUL-2015     Y

在Oracle SQL中完成此操作的任何想法.我正在尝试使用LAG和LEAD之类的分析功能，但无法完成此任务.

Any Idea to accomplish this in Oracle SQL. I am trying to use analytic functions like LAG and LEAD but unable to accomplish this.

推荐答案

您可以通过一个非常方便的技巧来做到这一点.连续值组可以使用row_number() s之差来计算.然后，您需要获取每个组的计数并选择与您的条件相匹配的计数:

You can do this with a very handy trick. The groups of consecutive values can be calculated using a difference of row_number()s. Then, you need to get the count for each group and select the ones that match your condition:

select t.*
from (select t.*, count(*) over (partition by id, flag, grp) as cnt
      from (select t.*,
                   (row_number() over (partition by id order by date) -
                    row_number() over (partition by id, flag order by date)
                   ) as grp
            from table t
           ) t
     ) t
where cnt >= 3;

严格来说，您不需要row_numbers()的区别.假设您的日期没有时间成分，那么下面的内容就足够了:

Strictly speaking, you do not need the difference of row_numbers(). Assuming your dates have no time components, the following will also suffice:

select t.*
from (select t.*, count(*) over (partition by id, flag, grp) as cnt
      from (select t.*,
                   (date -
                    row_number() over (partition by id, flag order by date)
                   ) as grp
            from table t
           ) t
     ) t
where cnt >= 3;

这篇关于Oracle SQL-在连续日期查找连续值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！