Home » SQL & PL/SQL » SQL & PL/SQL » Adding up graphs
Adding up graphs [message #669859] Fri, 18 May 2018 05:01 Go to next message
Buchas
Messages: 90
Registered: March 2006
Member
Hello,

I have some graphs, and I need to produce a sum for each time value. Graph has time on one axis and money on the other axis. Only the points where the value is changed are stored. For example:
   
A    null     10        null      20       null
B    100      null      null      110      null
C    50       60        null      null     null
   -05.07----05.08-----05.09-----05.10----05.11
sum  150      170       170       190      190

I have following script for this example:
create table test_vb 
(
id number not null primary key,
participant varchar2(5) not null,
report_date date not null,
value number not null
);
insert into test_vb(id,participant,report_date,value)values(1,'A',to_date('2018.05.08','yyyy.mm.dd'),10);
insert into test_vb(id,participant,report_date,value)values(2,'A',to_date('2018.05.10','yyyy.mm.dd'),20);
insert into test_vb(id,participant,report_date,value)values(3,'B',to_date('2018.05.07','yyyy.mm.dd'),100);
insert into test_vb(id,participant,report_date,value)values(4,'B',to_date('2018.05.10','yyyy.mm.dd'),110);
insert into test_vb(id,participant,report_date,value)values(5,'C',to_date('2018.05.07','yyyy.mm.dd'),50);
insert into test_vb(id,participant,report_date,value)values(6,'C',to_date('2018.05.08','yyyy.mm.dd'),60);
commit;
I need to produce a select, that would return 5 rows:
2018.05.07  150
2018.05.08  170
2018.05.09  170
2018.05.10  190
2018.05.11  190
Can you please help me with this?

[Updated on: Fri, 18 May 2018 05:01]

Report message to a moderator

Re: Adding up graphs [message #669860 is a reply to message #669859] Fri, 18 May 2018 07:22 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2745
Registered: January 2010
Location: Connecticut, USA
Senior Member
And where did 2018.05.11 come from? Anyway:

with t1 as (
            select  min(report_date) min_report_date,
                    max(report_date) max_report_date
              from  test_vb
           ),
     t2 as (
            select  min_report_date + level - 1 report_date
              from  t1
              connect by min_report_date + level - 1 <= max_report_date
           ),
     t3 as (
            select  t.id,
                    t.participant,
                    t2.report_date,
                    last_value(t.value ignore nulls) over(partition by t.participant order by t2.report_date) value
              from      test_vb t
                      partition by(t.participant)
                    right join
                        t2
                      on t.report_date = t2.report_date
           )
select  report_date,
        sum(value) value
  from  t3
  group by report_date
  order by report_date
/

REPORT_DA      VALUE
--------- ----------
07-MAY-18        150
08-MAY-18        170
09-MAY-18        170
10-MAY-18        190

SQL> 

SY.
Re: Adding up graphs [message #669870 is a reply to message #669860] Mon, 21 May 2018 03:04 Go to previous messageGo to next message
Buchas
Messages: 90
Registered: March 2006
Member
Wow, thanks, this is somewhat more complicated than I expected Smile
The "partition by" construct in
from      test_vb t
                      partition by(t.participant)
                    right join
                        t2
                      on t.report_date = t2.report_date
is totally new to me. Is it possible to rewrite it to a more common
from t partition by(t.participant), 
           t2
where t.report_date(+) = t2.report_date
? If I try to change to this, I get ORA-00905: Missing keyword.
Re: Adding up graphs [message #669871 is a reply to message #669870] Mon, 21 May 2018 03:25 Go to previous messageGo to next message
cookiemonster
Messages: 13120
Registered: September 2008
Location: Rainy Manchester
Senior Member
Probably not, and you would be better served by getting used to the ANSI outer-join syntax (LEFT/RIGHT/FULL (OUTER) JOIN) as it allows a lot things that the oracle syntax ((+)) doesn't.
Re: Adding up graphs [message #669875 is a reply to message #669870] Mon, 21 May 2018 08:06 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2745
Registered: January 2010
Location: Connecticut, USA
Senior Member
Buchas wrote on Mon, 21 May 2018 04:04
Wow, thanks, this is somewhat more complicated than I expected Smile
Is it possible to rewrite it to a more common
from t partition by(t.participant), 
           t2
where t.report_date(+) = t2.report_date
with t1 as (
            select  min(report_date) min_report_date,
                    max(report_date) max_report_date
              from  test_vb
           ),
     t2 as (
            select  min_report_date + level - 1 report_date
              from  t1
              connect by min_report_date + level - 1 <= max_report_date
           ),
     t3 as (
            select  distinct participant
              from  test_vb
           ),
     t4 as (
            select  participant,
                    report_date
              from  t2,
                    t3
           ),
     t5 as (
            select  t.id,
                    t4.participant,
                    t4.report_date,
                    last_value(t.value ignore nulls) over(partition by t4.participant order by t4.report_date) value
              from  test_vb t,
                    t4
              where t4.participant = t.participant(+)
                and t4.report_date = t.report_date(+)
           )
select  report_date,
        sum(value) value
  from  t5
  group by report_date
  order by report_date
/

REPORT_DA      VALUE
--------- ----------
07-MAY-18        150
08-MAY-18        170
09-MAY-18        170
10-MAY-18        190

SQL> 

SY.
Re: Adding up graphs [message #669930 is a reply to message #669875] Thu, 24 May 2018 14:22 Go to previous message
Barbara Boehmer
Messages: 8839
Registered: November 2002
Location: California, USA
Senior Member
Or, using inline views instead of with clauses and eliminating t4:

SCOTT@orcl_12.1.0.2.0> select report_date, sum (value) value
  2  from   (select t3.participant, t2.report_date,
  3  		    last_value (value ignore nulls) over (partition by t3.participant order by t2.report_date) value
  4  	     from   test_vb t,
  5  		    (select min_report_date + level - 1 report_date
  6  		     from   (select min (report_date) min_report_date, max (report_date) max_report_date
  7  			     from   test_vb)
  8  		     connect by level <= max_report_date - min_report_date + 1) t2,
  9  		    (select distinct participant
 10  		     from   test_vb) t3
 11  	     where  t3.participant = t.participant (+)
 12  	     and    t2.report_date = t.report_date (+))
 13  group  by report_date
 14  order  by report_date
 15  /

REPORT_DAT      VALUE
---------- ----------
2018.05.07        150
2018.05.08        170
2018.05.09        170
2018.05.10        190

4 rows selected.


Previous Topic: error while removing ' using regex
Next Topic: update huge table every n rows
Goto Forum:
  


Current Time: Sun May 27 16:36:22 CDT 2018