Skip to Main Content
  • Questions
  • Max of consecutive numbers when greater than 0

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Nalin.

Asked: September 09, 2016 - 10:53 pm UTC

Last updated: September 13, 2016 - 2:22 pm UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi Tom,
I am looking for a solution to get
MAX COUNT of CONSECUTIVE NUMBER of ROWS for each BID where DUE GREATER THAN 0, when 0 appear reset counter for that BID.
In below example I have max 3 consecutive row of BID 111 for MAR/APR/MAY and have max 5 consecutive row of BID 222 for FEB/APR/MAY/JUL/OCT

BID Date due
--- ----------- ----

111 01-JAN-2016 1
111 01-FEB-2016 0
111 01-MAR-2016 2<--
111 01-APR-2016 4<--
111 01-MAY-2016 5<--
111 01-JUN-2016 0
111 01-JUL-2016 3
111 01-AUG-2016 8
111 01-SEP-2016 0

222 01-JAN-2016 0
222 01-FEB-2016 2<--
222 01-APR-2016 3<--
222 01-MAY-2016 1<--
222 01-JUL-2016 8<--
222 01-OCT-2016 1<--

OUTPUT
-------

bid MAX(DUE)
111 3
222 5

Thanks in advance...

and Connor said...

Here's a nice video to walk you through it

https://www.youtube.com/watch?v=yvimYixXo2Q


Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Wonderful way of explaining

Arnab Panja, September 11, 2016 - 7:13 am UTC

Watched the video. Truely it adds more value when we include voice and video to the queries and solutions.
This is very insightful and the presentation in this video has left a lasting impact on me and surely will be same for others who watch this video.

Thanks
Connor
Connor McDonald
September 12, 2016 - 2:08 am UTC

Glad we could help

MAX COUNT of CONSECUTIVE NUMBER of ROWS

Nalin Singh, September 12, 2016 - 5:02 pm UTC

This video is very useful for lag (finding missing date range)
My problem is little different then this:

1) I have to count number of rows within 0 for each BID (no matter if rows or date range missing)
1, 2, 0, 2, 4, 5, 6, 0, 3
[2] [4] [1]

2) Then pic the max of this count
max([2], [4], [1]...[n])

3) repeat for next BID

Chris Saxon
September 13, 2016 - 2:29 am UTC

Send us some create table / insert statements for the sample data, and we'll take another look.

But our aim was to get your 90% of the way there...not to spoon feed the entire solution :-)

To Nalin Singh

Rajeshwaran Jeyabal, September 13, 2016 - 6:02 am UTC

Check if this helps.

demo@ORA11G> set feedback off
demo@ORA11G> drop table t purge;
demo@ORA11G> create table t(bid int,dt date,due int);
demo@ORA11G> insert into t values(1, to_date('01-jan-2016','dd-mon-yyyy'),1);
demo@ORA11G> insert into t values(1, to_date('01-feb-2016','dd-mon-yyyy'),2);
demo@ORA11G> insert into t values(1, to_date('01-mar-2016','dd-mon-yyyy'),0);
demo@ORA11G> insert into t values(1, to_date('01-apr-2016','dd-mon-yyyy'),2);
demo@ORA11G> insert into t values(1, to_date('01-may-2016','dd-mon-yyyy'),4);
demo@ORA11G> insert into t values(1, to_date('01-jun-2016','dd-mon-yyyy'),5);
demo@ORA11G> insert into t values(1, to_date('01-jul-2016','dd-mon-yyyy'),6);
demo@ORA11G> insert into t values(1, to_date('01-aug-2016','dd-mon-yyyy'),0);
demo@ORA11G> insert into t values(1, to_date('01-sep-2016','dd-mon-yyyy'),3);
demo@ORA11G> insert into t values(2, to_date('01-jan-2016','dd-mon-yyyy'),1);
demo@ORA11G> insert into t values(2, to_date('01-feb-2016','dd-mon-yyyy'),0);
demo@ORA11G> insert into t values(2, to_date('01-mar-2016','dd-mon-yyyy'),1);
demo@ORA11G> insert into t values(2, to_date('01-apr-2016','dd-mon-yyyy'),0);
demo@ORA11G> insert into t values(2, to_date('01-may-2016','dd-mon-yyyy'),1);
demo@ORA11G> insert into t values(2, to_date('01-jun-2016','dd-mon-yyyy'),0);
demo@ORA11G> commit;
demo@ORA11G> set feedback on
demo@ORA11G>
demo@ORA11G>
demo@ORA11G> select bid, cnt
  2  from (
  3  select bid, grp,count(*) cnt,
  4    dense_rank() over(partition by bid order by count(*) desc) rnk
  5  from (
  6  select bid,dt,due,last_value(x ignore nulls) over(partition by bid order by dt) grp
  7  from (
  8  select t.*,
  9     case when lag(due) over(partition by bid order by dt) is null or
 10                     lag(due) over(partition by bid order by dt) = 0 then
 11             row_number() over(partition by bid order by dt) end x
 12  from t
 13       )
 14  where due <> 0
 15       )
 16  group by bid,grp
 17       )
 18  where rnk = 1 ;

       BID        CNT
---------- ----------
         1          4
         2          1
         2          1
         2          1

4 rows selected.

demo@ORA11G>

Connor McDonald
September 13, 2016 - 2:22 pm UTC

Yes, that's one way to do it.

in case of 12c - SQL pattern matching

Rajeshwaran Jeyabal, September 13, 2016 - 3:04 pm UTC

demo@ORA12C> select bid,cnt
  2  from (
  3  select bid,mno,cls,cnt,
  4      dense_rank() over(partition by bid order by cnt desc) rnk
  5  from t
  6  match_recognize(
  7     partition by bid
  8     order by dt
  9     measures
 10             match_number() mno,
 11             classifier() cls,
 12      count(down.*) as cnt
 13     one row per match
 14     pattern( down+ strt )
 15     define
 16             down as down.due <> 0 )
 17       )
 18  where rnk = 1     ;

       BID        CNT
---------- ----------
         1          4
         2          1
         2          1
         2          1

demo@ORA12C>

Respectfully disagree with Rajeshwaran

Stew Ashton, September 13, 2016 - 7:25 pm UTC

The OP wants only one row per bid. If MATCH_RECOGNIZE:
select bid, max(cnt) from t
match_recognize(
  partition by bid order by dt
  measures count(*) cnt
  pattern (a+)
  define a as due > 0
)
group by bid;

BID MAX(CNT)
--- --------
1   4
2   1

To Stew

Rajeshwaran Jeyabal, September 14, 2016 - 5:40 am UTC

Stew - given this
....
I am looking for a solution to get 
MAX COUNT of CONSECUTIVE NUMBER of ROWS for each BID where DUE GREATER THAN 0
...


The max count could be more than one for each BID.

demo@ORA12C> select *
  2  from (
  3  select bid,cnt,
  4      dense_rank() over(partition by bid order by cnt desc) rnk
  5    from t
  6  match_recognize(
  7    partition by bid
  8    order by dt
  9    measures
 10      count(*) as cnt
 11    one row per match
 12    pattern( down+ )
 13    define
 14      down as due > 0 )
 15      )
 16  where rnk = 1    ;

       BID        CNT        RNK
---------- ---------- ----------
         1          4          1
         2          1          1
         2          1          1
         2          1          1

4 rows selected.

demo@ORA12C>


since more than one max count available for bid=2 so it list all the max count.