Re: Analytic function Help
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> deReceived on Tue Feb 01 2011 - 12:10:53 CST