EXPERT RESPONSE
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.
|
