Re: Analytic function Help

From: Lennart Jonsson <erik.lennart.jonsson_at_gmail.com>
Date: Tue, 01 Feb 2011 17:56:54 +0100
Message-ID: <ii9e0n$3uq$1_at_news.eternal-september.org>

On 2011-02-01 16:46, Andreas Mosmann wrote: [...]
> to find out, whether this is the beginning ob a new block and after this
> I made a sum() and group by
>
> select ID,sum(IS_MATCH) MyCount from
> (
> select
> ID,SORT,
> decode(
> nvl(
> lag(fk)
> over (partition by id order by sort)
> ),'N')||fk
> ,'NY',1,0) IS_MATCH
> from MyView
> )
> group by
> ID
>

For the fun of it, another approach is to enumerate each id group in two ways, per id, and per id and fk:

select id, sort, fk,

       row_number() over
	 (partition by id order by sort) as total_group_order, 	
       row_number() over
	 (partition by id, fk order by sort) as partial_group_order

from MyView

If the difference between total_group_order and partial_group_order is the same it means that they are adjacent, i.e. belongs to the same group. Therefore the number of separate groups of 'Y' is the same as the number of distinct differences:

select id, count(distinct total_group_order - partial_group_order) as MyCount
from (

	select id, sort, fk,
	  row_number() over (partition by id order by sort)
		as total_group_order,
	  row_number() over (partition by id, fk order by sort)
		as partial_group_order
	from MyView

)
where fk = 'Y'
group by id

My guess is that your current solution is more efficient than this.

/Lennart Received on Tue Feb 01 2011 - 10:56:54 CST