Re: Analytic function Help

From: Andreas Mosmann <mosmann_at_expires-28-02-2011.news-group.org>
Date: Tue, 01 Feb 2011 19:10:53 +0100
Message-ID: <1296583853.59_at_user.newsoffice.de>



Lennart Jonsson schrieb am 01.02.2011 in <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

Thank you for answering,

my real problem is more complex. I tried your version shortly, but I could not get my results. Probably my mistake, I will try tomorrow again.

Andreas

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de
Received on Tue Feb 01 2011 - 12:10:53 CST

Original text of this message