Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sunny.

Asked: June 26, 2016 - 8:15 am UTC

Last updated: June 30, 2016 - 1:33 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi I want THE QUERY to sum the number based on the given time interval(I.E. 120 MINUTES)


Type TIME Quantity
----- ---- --------
A 2:00:01 10
A 3:10:20 05
B 4:00:00 01
A 6:10:11 20


HERE IN THE 120 MINUTES I.E. 2 HRS FOR 1ST ROW, WE HAVE 2 A AND 1 B SO SUM IS 15 AND 01. FOR 2ND ROW, IN NEXT TWO HOURS WE HAVE 1 A AND 1 B SO SUM_A IS 05 AND SUM_B IS 01, SHOWN BELOW.

Type TIME Quantity SUM_A SUM_B
----- ---- -------- ------- -----
A 2:00:01 10 15 01
A 3:10:20 05 05 01
B 4:00:00 01 00 01
A 6:10:11 20 20 00

and Chris said...

There's a few ways you could do this. Here's two.

Both use analytics to calculate a rolling sum, split by type.

The first does this and pivots the data. The second computes separate functions for A and B:

create table t (
Type varchar2(1),TIME varchar2(10),Quantity int
);
insert into t values ('A','2:00:01',10); 
insert into t values ('A','3:10:20',05);
insert into t values ('B','4:00:00',01);
insert into t values ('A','6:10:11',20); 

select time, nvl(a_q, 0) sum_a, nvl(b_q, 0) sum_b from (
select type, time, 
       sum(quantity) over (partition by type order by time) q
from   t
order  by 2
) pivot (
  sum(q) as q for (type) in ('A' as a, 'B' as b)
)
;

TIME            SUM_A      SUM_B
---------- ---------- ----------
2:00:01            10          0
3:10:20            15          0
4:00:00             0          1
6:10:11            35          0

select type, time, quantity,
       nvl(sum(case when type = 'A' then quantity end) 
         over (partition by type order by time), 0) sum_a,
       nvl(sum(case when type = 'B' then quantity end) 
         over (partition by type order by time), 0) sum_b
from   t
order  by time;

T TIME         QUANTITY      SUM_A      SUM_B
- ---------- ---------- ---------- ----------
A 2:00:01            10         10          0
A 3:10:20             5         15          0
B 4:00:00             1          0          1
A 6:10:11            20         35          0


I don't understand why sum_b is 1 in your desired output for the first two rows. Surely this should be zero?

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

order by RANGE in Analytics

Rajeshwaran, Jeyabal, June 29, 2016 - 9:01 am UTC

I hope we need to store the Time interval in INTERVAL datatype not in Varchars.

drop table t purge;
create table t (
Type varchar2(1),TIME interval day to second,Quantity int
);
insert into t values ('A',to_dsinterval('0 2:00:01'),10); 
insert into t values ('A',to_dsinterval('0 3:10:20'),05);
insert into t values ('B',to_dsinterval('0 4:00:00'),01);
insert into t values ('A',to_dsinterval('0 6:10:11'),20); 



rajesh@ORA11G> select t.*,
  2    sum( decode(type,'A',quantity)) over(order by time
  3      range between current row and interval '2' hour following) sum_a,
  4    sum( decode(type,'B',quantity)) over(order by time
  5      range between current row and interval '2' hour following) sum_b
  6  from t     ;

T TIME                   QUANTITY      SUM_A      SUM_B
- -------------------- ---------- ---------- ----------
A +00 02:00:01.000000          10         15          1
A +00 03:10:20.000000           5          5          1
B +00 04:00:00.000000           1                     1
A +00 06:10:11.000000          20         20

4 rows selected.

rajesh@ORA11G>

Chris Saxon
June 30, 2016 - 1:33 am UTC

Sadly INTERVAL datatypes (in my experience) are rarely seen in applications. People still like to use strings, numbers for time :-(

ELSE part of Decode

Rajeshwaran, Jeyabal, June 29, 2016 - 10:16 am UTC

Sorry, that decode part should be like this.

rajesh@ORA11G> select t.*,
  2    sum( decode(type,'A',quantity,0)) over(order by time
  3      range between current row and interval '2' hour following) sum_a,
  4    sum( decode(type,'B',quantity,0)) over(order by time
  5      range between current row and interval '2' hour following) sum_b
  6  from t;

T TIME                   QUANTITY      SUM_A      SUM_B
- -------------------- ---------- ---------- ----------
A +00 02:00:01.000000          10         15          1
A +00 03:10:20.000000           5          5          1
B +00 04:00:00.000000           1          0          1
A +00 06:10:11.000000          20         20          0

4 rows selected.

rajesh@ORA11G>

Two things !

Rajeshwaran, Jeyabal, June 30, 2016 - 2:42 am UTC

1) Have the "time" values in the "Interval" datatype helps us to sort them nicely.
2) Using the "Order by RANGE" option in Analytic (aka - logical offset of windowing clause) we are able to get the output requested in this question.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.