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 Go to next message
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 #400517 is a reply to message #400516] Tue, 28 April 2009 09:47 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/m/400513/136107/#msg_400513

Do not cross/multi-post

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: Instead of UNBOUNDED PRECEDING: Is it possible to use a value like 0 for beginning? [message #400530 is a reply to message #400516] Tue, 28 April 2009 10:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #400558 is a reply to message #400516] Tue, 28 April 2009 13:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>select adds every value of each line to the next line
Rows in a table have NO inherent order.
Rows in a table are like balls in a bn which asket.
Let's assume you picked a RED ball out of the basket.
Which ball is the "next" ball?
The order which rows get returned by SELECT are unrelated to the order in which they were inserted.

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 Go to previous messageGo to next message
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 #400560 is a reply to message #400516] Tue, 28 April 2009 14:16 Go to previous messageGo to next message
TimB83
Messages: 5
Registered: April 2009
Junior Member
Hello Black Swan,

you are right. The select MUST use "order by code, month".
This is an exactly ordering - so every value is exactly in the same order (first code, then all months for this code ordered by month).


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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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

Previous Topic: Procedure
Next Topic: sql server procedure into oracle
Goto Forum:
  


Current Time: Tue Feb 11 19:31:56 CST 2025