Q
Problem solve Get help with specific problems with your technologies, process and projects.

Analytic solution to counting rows in runs

Reader Paul Moore has supplied an analytic solution to the problem posed in a previous answer, "Counting rows in runs."

Reader Paul Moore of Atos Origin S.A. was kind enough to respond with an analytic solution to the problem posed...

in a previous answer, Counting rows in runs. His solution follows.

Assume a table called runs, defined as follows:

create table runs 
( id number
, category varchar2(1)
); 
insert into runs values (1, 'X'); 
insert into runs values (2, 'X'); 
insert into runs values (3, 'X'); 
insert into runs values (4, 'Y'); 
insert into runs values (5, 'Y'); 
insert into runs values (6, 'X'); 
insert into runs values (7, 'X'); 
commit;

The following works:

select block, category, count(*)
from (
     select
         id,
         category,
         sum(prev_cat) over 
           (order by id 
              rows unbounded preceding) block 
     from ( 
          select
              id,
              category,
              case when (category = lag(category) 
                               over (order by id) )
                  then 0
                  else 1
              end
              prev_cat
          from
              runs
          )
     )
group by block, category
order by block

BLOCK   C  COUNT(*)
-----   -  --------
    1   X         3
    2   Y         2
    3   X         2

The trick is to work out from the inner inline view. First, use "lag" to identify when the category changes. You can use decode to get an Oracle 8i solution -- I used the 9i case operator. You also need to watch out for the first row, where lag() returns null. Then, sum the number of category changes which occur "before" a given row - that is its "block" (ie, the sequence number of the run it's in). Then you just count and group by the block number.

Hope this is of interest.

Yes, it is. Thanks very much, Paul.

This was last published in April 2007

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close