Re: Analytic function Help

From: Andreas Mosmann <mosmann_at_expires-28-02-2011.news-group.org>
Date: Tue, 01 Feb 2011 16:46:00 +0100
Message-ID: <1296575160.94_at_user.newsoffice.de>

Michel Cadot schrieb am 31.01.2011 in
<4d470cff$0$19676$426a74cc_at_news.free.fr>:

> "Andreas Mosmann" <mosmann_at_expires-31-01-2011.news-group.org> a écrit dans le 
> message de news: 1296500556.65_at_user.newsoffice.de...

>| Hi, there is a view like
>|
>|
>| ID SORT FK
>| 01 -5 N
>| 01 -4 N
>| 01 -3 Y
>| 01 -2 Y
>| 01 -1 N
>| 01 0 Y
>| 01 1 N
>| 01 2 N
>| 02 -2 N
>| 02 -1 Y
>| 02 0 Y
>| 02 1 Y
>| 03 -5 Y
>| 03 -4 N
>| 03 -3 Y
>| 03 -2 N
>| 03 -1 Y
>| 03 0 N
>| 03 1 Y
>|
>| I need the number of seperate groups of 'Y'
>| that means in this case:
>|
>| ID COUNT
>| 01 2
>| 02 1
>| 03 4
>|
>| ID 01: SORT (-2,-3) and (0) are a group ->2
>| ID 02: SORT (-1 .. +1) is a group ->1
>| ID 03: SORT (-5), (-3), (-1), (1) are groups ->4
>|
>| I sometimes use lead and lag, but in this case I do not have an idea,
>| but I feel it should be possible, isn't it?
>|
>| Thank you
>| Andreas

> Something like
> count(case when fk='Y' and nvl(lag(fk) over (partition by id order by 
> sort),'N')='N' then 1 end)
> with group by id

> Regards
> Michel

Thank you very much, you gave me the idea. I did it like that

decode(nvl(lag(fk) over (partition by id order by sort)),'N')||fk,'NY',1,0)

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

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de

Received on Tue Feb 01 2011 - 09:46:00 CST