Cumulative total in DB2

gamo · #1 (**permalink**) 07-29-09, 03:50

Hi,

have a table like:

RecordID | GroupID | RecordDate
------------------------------------------------
1 | 20 | 2009-05-17-12.18.00.000000
2 | 21 | 2009-05-16-15.30.00.000000
3 | 20 | 2009-06-02-01.40.00.000000
4 | 21 | 2008-12-23-10.05.00.000000
5 | 20 | 2007-04-11-09.55.00.000000
6 | 22 | 2008-05-16-23.36.00.000000
7 | 22 | 2004-10-30-05.49.00.000000
8 | 22 | 2009-07-20-17.22.00.000000

what I want to do is create a function so that if I enter one date, it will return the column recorddate in order, and a column which displays how many records fall prior to the date of the current record, and post the entered date, filtered by only some of the groupids...

that's a bit hard to describe, but basically what I want is a cumulative total time-series graph in jasperreports such as the below, for only group ids 21 & 22 and ignoring all other records (pardon the hyphens - wasn't preserving indentation with just spaces...kind of looks like a guitar tab now =P):

c |---------------------------------------------+--
u |-------------------------------------+----------
m |--------------------------------+---------------
l |----------------------------+-------------------
t |-------+----------------------------------------
o |-+----------------------------------------------
t |------------------------------------------------
min-time----------------------------------------max-time

anyway, I wrote a UDF as follows:

Code:

create function HOWMANYINRANGE ( beforedate TIMESTAMP, 
afterdate TIMESTAMP, group INTEGER ) 
returns integer 
return 
( select count (*) from mytable where RecordDate < beforedate 
and RecordDate > afterdate and GroupID = group )

and I am calling it as such:

Code:

select RecordDate, HOWMANYINRANGE( to_date('2005-05-05', 
'YYYY-MM-DD'), RecordDate, 21 ) from mytable where 
RecordDate > to_date('2005-05-05', 'YYYY-MM-DD') and GroupID = 21
order by RecordDate

where 2005-05-05 in the above example will represent the variable min-date which is a jasperreport parameter.

This all works OK...but it's REALLY slow on my quite big table and I was hoping someone might have a suggestion for increasing the efficiency - maybe even dropping the function and being able to do it within a single select would be possible? I just couldn't work it out...

Also, you can probably spot where I've hard coded the '21' into the above example...at the moment I am getting around this by creating a jasperreports variable which sums the two series, but it would be better if I could do it like 'in (21,22)' as this would be more flexible if I change the groups I want to look at, but I just didn't know how to pass a vector of integers as an argument into a function...

any suggestions would be greatly appreciated!

tonkuma · #2 (**permalink**) 07-29-09, 05:10

OLAP specification may be an answer for flexibility (and probably performance).

Code:

------------------------------ Commands Entered ------------------------------
WITH
 mytable AS (
SELECT RecordID
     , GroupID
     , TIMESTAMP(RecordDate) AS RecordDate
  FROM (VALUES
         (1, 20, '2009-05-17-12.18.00.000000')
        ,(2, 21, '2009-05-16-15.30.00.000000')
        ,(3, 20, '2009-06-02-01.40.00.000000')
        ,(4, 21, '2008-12-23-10.05.00.000000')
        ,(5, 20, '2007-04-11-09.55.00.000000')
        ,(6, 22, '2008-05-16-23.36.00.000000')
        ,(7, 22, '2004-10-30-05.49.00.000000')
        ,(8, 22, '2009-07-20-17.22.00.000000')
       ) AS s(RecordID, GroupID, RecordDate)
)
SELECT RecordID
     , GroupID
     , RecordDate
     , COUNT(*)
         OVER(ORDER BY RecordDate
              RANGE BETWEEN UNBOUNDED PRECEDING
                        AND CURRENT ROW ) AS howmany_in_range
  FROM mytable
 WHERE RecordDate > DATE('2005-05-05')
   AND GroupID IN (21, 22)
 ORDER BY
       RecordDate
;
------------------------------------------------------------------------------

RECORDID    GROUPID     RECORDDATE                 HOWMANY_IN_RANGE
----------- ----------- -------------------------- ----------------
          6          22 2008-05-16-23.36.00.000000                1
          4          21 2008-12-23-10.05.00.000000                2
          2          21 2009-05-16-15.30.00.000000                3
          8          22 2009-07-20-17.22.00.000000                4

  4 record(s) selected.

gamo · #3 (**permalink**) 07-29-09, 19:40

so great!

that works so so so so so so so so so much better...now that I've done a bit more reading on OVER I understand why it is so much faster, but I never would have found that one by myself...

thanks!!

Internet Services

Web Development

Digital Marketing

Consumer Tech