Home » SQL & PL/SQL » SQL & PL/SQL » Instead of UNBOUNDED PRECEDING: Is it possible to use a value like 0 for beginning?
Instead of UNBOUNDED PRECEDING: Is it possible to use a value like 0 for beginning? [message #400516] |
Tue, 28 April 2009 09:45  |
TimB83
Messages: 5 Registered: April 2009
|
Junior Member |
|
|
Hello,
I am searching for a solutions since two days... but I don't know what I should do - maybe it isn't possible to do this with oracle...!?
I have a table with many rows. The attributes of the table are code, month and value.
For each code there are 12 months and 12 values.
No I want to add the gaps between the months...
Is it possible to count the following gaps between the different rows...?
For example:
Original table1:
code, month, value
1,1,20
1,2,0
1,3,30
1,4,0
1,5,40
1,6,0
1,7,0
1,8,20
1,9,0
1,10,10
1,11,0
1,12,0
5,1,0
5,2,20
5,3,10
...
description:
january value = 20
february value = 0 (=>count this gap => new value 1 )
march value = 30
april value = 0 (=>count this gap => new value 1 )
may value = 40
june value = 0
july value = 0 (=>count this two following gaps => new value 2 )
agust value = 20
september value = 0 (=>count this gap => new value 1 )
october value = 10
november value = 0
december value = 0 (=>count this two following gaps => new value 2 )
New target table:
code, month, value
1,1,20
1,2,1
1,3,30
1,4,1
1,5,40
1,6,0
1,7,2
1,8,20
1,9,1
1,10,10
1,11,0
1,12,2
5,1,1
5,2,20
5,3,10
...
I tried this code:
select code, month
sum(value) over (
order by month
rows between unbounded preceding and current row
) as final_value
from table1 order by month;
This adds all following fields cumulative from beginning to current_row. But I need this adding only for the following gaps... then start with countin by 0.
I need only the following like in the example on top. Maybe is there an other function like decode to count only the following gaps...!?
A function instead of unbounded preceding....like "Last field with value=0" or something... ?
Best regards,
Tim
|
|
|
|
|
Re: Instead of UNBOUNDED PRECEDING: Is it possible to use a value like 0 for beginning? [message #400555 is a reply to message #400516] |
Tue, 28 April 2009 13:39   |
TimB83
Messages: 5 Registered: April 2009
|
Junior Member |
|
|
Hello,
sorry if my post was in wrong code or wrong formatting. I hope you can help me?
This is the sql-code for my problem:
Table t1:
create table t1 (
code number(2) not null,
month number(2) not null,
value number(4) not null
);
insert into t1 values(1,1,20);
insert into t1 values(1,2,0);
insert into t1 values(1,3,30);
insert into t1 values(1,4,0);
insert into t1 values(1,5,40);
insert into t1 values(1,6,0);
insert into t1 values(1,7,0);
insert into t1 values(1,8,20);
insert into t1 values(1,9,0);
insert into t1 values(1,10,10);
insert into t1 values(1,11,0);
insert into t1 values(1,12,0);
insert into t1 values(5,1,0);
insert into t1 values(5,2,20);
insert into t1 values(5,3,10);
No I want to select the data of this table like this statement:
select code, month
sum(value) over (
order by month
rows between unbounded preceding and current row
) as final_value
from table1 order by month;
But this is wrong. This select adds every value of each line to the next line...cumulative. But I need only the adding of the gaps (value=0). If a value=0, then look to the next value.
If this is also 0, then count, if this isn't 0, then insert 1 to the first value=0.
It is very complicated to describe the problem, but the result should be like this table:
New target table:
code, month, value
1,1,20
1,2,1 (because this value was 0 and there is no value=0 directly before)1,3,30
1,4,1 (because this value was 0 and there is no value=0 directly before)1,5,40
1,6,0
1,7,2 (because this and the value before were 0)
1,8,20
1,9,1 (because this value was 0 and there is no value=0 directly before)
1,10,10
1,11,0
1,12,2 (because this and the value before were 0)
5,1,1 (because this value was 0 and there is no value=0 directly before)
5,2,20
5,3,10
Best regards,
Tim
|
|
|
Re: Instead of UNBOUNDED PRECEDING: Is it possible to use a value like 0 for beginning? [message #400557 is a reply to message #400555] |
Tue, 28 April 2009 13:51   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Why 1,6,0 does not give 1,6,1 like 1,2,0 gives 1,2,1?
What happens if 1,8,20 is 1,8,0?
Regards
Michel
[Updated on: Tue, 28 April 2009 13:52] Report message to a moderator
|
|
|
|
Re: Instead of UNBOUNDED PRECEDING: Is it possible to use a value like 0 for beginning? [message #400559 is a reply to message #400557] |
Tue, 28 April 2009 14:14   |
TimB83
Messages: 5 Registered: April 2009
|
Junior Member |
|
|
Hello Michael Cadot,
you are right. I thought it would be easier to write it in this way, but the correct result of my select should be the following:
1,1,0 (because it wasn't a value=0)
1,2,1 (because this value was 0 and there is no value=0 directly before)
1,3,0 (because it wasn't a value=0)
1,4,1 (because this value was 0 and there is no value=0 directly before this value)
1,5,0 (because it wasn't a value=0)
1,6,0 (because it was a value=0, but after this there is another value=0)
1,7,2 (because this and the value before were 0)
1,8,0 (because it wasn't a value=0)
1,9,1 (because this value was 0 and there is no value=0 directly before)
1,10,0 (because it wasn't a value=0)
1,11,0 (because it was a value=0, but after this there is another value=0)
1,12,2 (because this and the value before were 0)
5,1,1 (because this value was 0 and there is no value=0 directly before)
5,2,0 (because it wasn't a value=0)
5,3,0 (because it wasn't a value=0)
Maybe see the attached picture - i think this should show the problem. The green line is the result of the select.
Best regards,
Tim
|
|
|
|
Re: Instead of UNBOUNDED PRECEDING: Is it possible to use a value like 0 for beginning? [message #400563 is a reply to message #400559] |
Tue, 28 April 2009 15:09   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
SQL> with
2 data as (
3 select code, month, value,
4 case when nvl(lag(value) over (partition by code order by month),1) != 0
5 then row_number() over (partition by code order by month)
6 end flag
7 from t1
8 ),
9 grouped as (
10 select code, month, value,
11 max(flag) over (partition by code order by month) grp
12 from data
13 )
14 select code, month,
15 case
16 when value != 0 or lead(value) over (partition by code order by month) = 0 then 0
17 else count(*) over (partition by code, grp order by month)
18 end value
19 from grouped
20 order by code, month
21 /
CODE MONTH VALUE
---------- ---------- ----------
1 1 0
1 2 1
1 3 0
1 4 1
1 5 0
1 6 0
1 7 2
1 8 0
1 9 1
1 10 0
1 11 0
1 12 2
5 1 1
5 2 0
5 3 0
15 rows selected.
Regards
Michel
|
|
|
Re: Instead of UNBOUNDED PRECEDING: Is it possible to use a value like 0 for beginning? [message #400578 is a reply to message #400563] |
Tue, 28 April 2009 22:46   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Here's another possibility that uses just 3 analytic functions
SELECT code
, month
, value
, nvl2(is_not_zero, row_number() over (partition by code, grp order by month), 0) AS num
FROM (
SELECT code
, month
, value
, is_not_zero
, row_number() over (partition by code order by is_not_zero, month)
-
row_number() over (partition by code order by month) as grp
FROM (
SELECT code
, month
, decode(value, 0, 0, NULL) AS is_not_zero
, value
FROM t1
)
)
order by code, month
CODE MONTH VALUE NUM
---------- ---------- ---------- ----------
1 1 20 0
1 2 0 1
1 3 30 0
1 4 0 1
1 5 40 0
1 6 0 1
1 7 0 2
1 8 20 0
1 9 0 1
1 10 10 0
1 11 0 1
1 12 0 2
5 1 0 1
5 2 20 0
5 3 10 0
Ross Leishman
|
|
|
Re: Instead of UNBOUNDED PRECEDING: Is it possible to use a value like 0 for beginning? [message #400609 is a reply to message #400563] |
Wed, 29 April 2009 01:56  |
TimB83
Messages: 5 Registered: April 2009
|
Junior Member |
|
|
Hello,
thanks a lot for your postings!!!!
Its great to see what is possible with oracle!
Its working absolutely perfectly!
I never thought that this problem will solved on anytime - wow!
Thanks so much!!!
I will use Michaels example because its only counting the highest gap-counts. But rleishmans example is also working - with every gap-count.
Its unbelievable to see what is possible with some where-clauses and functions... I will try to expand this example to a much bigger function/ select... now I am understanding whats to do.
Best regards,
Tim
[Updated on: Wed, 29 April 2009 02:08] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Tue Feb 11 19:31:56 CST 2025
|