Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009
    Location
    Sydney
    Posts
    13

    Unanswered: Cumulative total in DB2

    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!

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  3. #3
    Join Date
    Jul 2009
    Location
    Sydney
    Posts
    13
    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!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •