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

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

"Andreas Mosmann" <mosmann (AT) expires-31-01-2011 (DOT) news-group.org> a écrit dans le message de news: 1296500556.65 (AT) user (DOT) newsoffice.de...

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

Michel Cadot schrieb am 31.01.2011 in
<4d470cff$0$19676$426a74cc (AT) news (DOT) free.fr>:


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

On 2011-02-01 16:46, Andreas Mosmann wrote:
[...]
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

Lennart Jonsson schrieb am 01.02.2011 in
<ii9e0n$3uq$1 (AT) news (DOT) eternal-september.org>:


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

Andreas Mosmann schrieb am 31.01.2011 in
<1296500556.65 (AT) user (DOT) newsoffice.de>:


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

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

On 2011-02-01 19:31, Andreas Mosmann wrote:
Perhaps best to start without using any analytic functions etc. Here's a
sketch (at the moment I don't have an Oracle dbms to try with, so you
might have to do some adjustments):

create table T (
id char(2) not null,
start int not null,
stop int not null,
cnt int not null,

primary key (id, start)
);

insert into T (id, start, stop, cnt)
values
('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,1000,2);

For every tuple we would like to find the max stop such that no other
cnt lies between our start and that stop:

select id, start, cnt, (select max(stop) as stop
from T T2 where (T1.id, T1.cnt)
= (T2.id, T2.cnt)
and not exists (
select 1 from T T3
where T2.id = T3.id
and T2.cnt <> T3.cnt
and T3.start between T1.stop
and T2.start
)
)
from T T1

ID START CNT STOP
-- ----------- ----------- -----------
01 0 2 150
01 100 2 150
01 150 4 375
01 375 1 700
01 500 1 700
01 700 2 1000
01 900 2 1000

What remain is to find the smallest start per id, cnt and stop:

select id, min(start) as start, stop, cnt
from (
select id, start, cnt,
(select max(stop) as stop
from T T2
where (T1.id, T1.cnt) = (T2.id, T2.cnt)
and not exists (
select 1 from T T3
where T2.id = T3.id
and T2.cnt <> T3.cnt
and T3.start between T1.stop and T2.start
)
)
from T T1
) group by id, stop, cnt order by stop

ID START STOP CNT
-- ----------- ----------- -----------
01 0 150 2
01 150 375 4
01 375 700 1
01 700 1000 2


Using a similar technique as in my previous example:

select id, start, stop, cnt,
row_number() over (partition by id order by start) -
row_number() over (partition by id, cnt order by start) as diff
from T
order by start

ID START STOP CNT DIFF
-- ----------- ----------- ----------- --------------------
01 0 100 2 0
01 100 150 2 0
01 150 375 4 2
01 375 500 1 3
01 500 700 1 3
01 700 900 2 3
01 900 1000 2 3

min(start) and max(stop) per id, diff gives:

select id, min(start) as start, max(stop) as stop, cnt, diff
from (
select id, start, stop, cnt,
row_number() over (partition by id order by start) -
row_number() over (partition by id, cnt
order by start) as diff
from T
)
group by id, cnt, diff
order by start

ID START STOP CNT DIFF
-- ----------- ----------- ----------- --------------------
01 0 150 2 0
01 150 375 4 2
01 375 700 1 3
01 700 1000 2 3


I'll have to stop here, if I come up with some other way I might post
that later.


/Lennart

Lennart Jonsson schrieb am 01.02.2011 in
<ii9rah$538$1 (AT) news (DOT) eternal-september.org>:

Sorry, I was involved in other work.


Thank you very much, that was the idea I looked for.

The difference between the row number without criteria and the row
number within this criteria devides into groups, as I want and after
this min/max will work. Fine Idea



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