Home » SQL & PL/SQL » SQL & PL/SQL » Adding up graphs
Adding up graphs [message #669859] |
Fri, 18 May 2018 05:01 |
Buchas
Messages: 101 Registered: March 2006
|
Senior 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 |
Solomon Yakobson
Messages: 3275 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 #669871 is a reply to message #669870] |
Mon, 21 May 2018 03:25 |
cookiemonster
Messages: 13925 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 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Buchas wrote on Mon, 21 May 2018 04:04Wow, thanks, this is somewhat more complicated than I expected
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 |
|
Barbara Boehmer
Messages: 9097 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.
|
|
|
Goto Forum:
Current Time: Fri Jun 28 10:50:37 CDT 2024
|