Re: Analytic function Help

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

Andreas Mosmann schrieb am 31.01.2011 in <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

I solved the question above with your help. Now I have another question to the same subject.

Imagine the result a little more complex. Instead of

> ID COUNT
> 01 2
> 02 1
> 03 4

there is maybe:

ID    START    STOP     COUNT
01        0     100         2
01      100     150         2
01      150     375         4
01      375     500         1
01      500     700         1
01      700     900         2
01      900    1015         2

I now want to melt records with the same count, but only that, that follow each other.

wished result

ID    START    STOP     COUNT
01        0     150         2
01      150     375         4
01      375     700         1
01      700    1015         2

I former got an answer to a similar question by Mr. Maxim Demenko and I tried to change some hours, but I could not resolve my new problem. It is, that f.e. COUNT=2 is twice in the result. So I can not use MAX- Function to find out end of the block.

Maxims idea (that fitted for the other question very fine) was Inner query:
select
  ID, START,
  --to find out the first
  nvl(decode(lag(START) over (partition by ID, COUNT order by VST),NULL,1,0) FIRST_LINE,
  --to find out "Stop"- Value
  max(STOP) over (partition by ID, COUNT),   COUNT
from MyResult

Complete query (simplified):
select
  ID,START,MAX_STOP,COUNT
from (
  select
    ID, START,
    --to find out the first
    nvl(decode(lag(START) over (partition by ID, COUNT order by VST),NULL,1,0) IS_FIRST_LINE,
    --to find out "Stop"- Value
    max(STOP) over (partition by ID, COUNT) MAX_STOP,     COUNT
  from MyResult
) WHERE IS_FIRST_LINE=1 It works for COUNT 1 and 4, but of course not for COUNT 2.

Any ideas?

Thanks in advance
Andreas

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

Received on Tue Feb 01 2011 - 12:31:45 CST