Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5

    Unanswered: use recursion or other?

    DB2 Z/OS V10.

    I have been asked to create a quarterly summarization table for some metrics off of a monthly summarization table. I think that's a bit of overkill, but they are concerned with performance. I was thinking of something along the lines of the below should meet their reqs and perform suitably well or is there a more suitable way of doing this via XMLAGG or CTE(LISTAGG not an option on z/os v10)? Each quarter is expected to have about 15K rows. Only have 5 years of data in a rolling partition scheme of 1-60. Thanks.

    Requirement:
    For the key columns of the table show the totals for the current quarter and the prior quarter.

    Code:
    select a.key_cols
           , sum(a.amts)
           , sum(b.amts)
        from table1 a
    inner join table1 b
    on a.key_cols = b.key_cols
    and b.qtr = case :cur-qtr
                        when 1 then 4
                        when 2 then 1
                        when 3 then 2
                        when 4 then 3 end
    and b.yr  = case :cur-qtr
                        when 1 then :cur-yr - 1 
                        else :cur-yr end
    where a.qtr = :cur-qtr
       and a.yr  = :cur-yr
    group by a.key_cols
    Dave

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    No suggestions one way or the other?

  3. #3
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by dav1mo View Post
    No suggestions one way or the other?
    Have you considered removing the self join and move that functionality to the sum part? Example:

    Code:
    select key_cols
           , sum( case when yr = ? and qtr = ? then amts end ) as ...
           , sum( case when yr = ? and qtr = ? then amts end ) as ...
    from table1
    where (yr, qtr) in ((?,?),(?,?))
    group by key_cols
    It is probably better to determine previous year/quarter outside the sql and just use constants

    Just a thought
    --
    Lennart

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is my idea.

    Example 1: Not tesed.
    Code:
    SELECT key_cols
         , sum_current_quarter
         , sum_prior_quarter
     FROM  (SELECT key_cols
                 , yr , qtr
                 , SUM(amts) AS sum_current_quarter
                   MAX( SUM(amts) )
                      OVER( PARTITION BY key_cols
                                ORDER BY yr , qtr
                            ROWS BETWEEN 1 PRECEDING
                                     AND 1 PRECEDING
                          ) AS sum_prior_quarter
                 , ROW_NUMBER()
                      OVER( PARTITION BY key_cols
                                ORDER BY yr , qtr
                          ) AS row_num
             FROM  table1
             WHERE :cur-qtr IN (2 , 3 , 4)
              AND  yr = :cur-yr
              AND  qtr BETWEEN :cur-qtr - 1
                           AND :cur-qtr
              OR   :cur-qtr = 1
              AND
              (    yr = :cur-yr     AND qtr = 1
               OR  yr = :cur-yr - 1 AND qtr = 4
              )
             GROUP BY
                   key_cols
                 , yr , qtr
           ) AS s
     ORDER BY
           key_cols
     WHERE row_num = 2
    ;
    Last edited by tonkuma; 10-10-13 at 16:12.

  5. #5
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by lelle12 View Post
    Have you considered removing the self join and move that functionality to the sum part? Example:

    Code:
    select key_cols
           , sum( case when yr = ? and qtr = ? then amts end ) as ...
           , sum( case when yr = ? and qtr = ? then amts end ) as ...
    from table1
    where (yr, qtr) in ((?,?),(?,?))
    group by key_cols
    It is probably better to determine previous year/quarter outside the sql and just use constants

    Just a thought
    If there's a risk that there are no rows for some quarters you might want to add an else clause like:

    sum( case when yr = ? and qtr = ? then amts else 0 end )
    --
    Lennart

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If there's a risk that there are no rows for some quarters you might want to add an else clause like:

    sum( case when yr = ? and qtr = ? then amts else 0 end )
    Yes! You are right.

    But, OP used INNER JOIN
    Quote Originally Posted by dav1mo View Post
    DB2 Z/OS V10.

    ...
    ...

    Code:
    select a.key_cols
           , sum(a.amts)
           , sum(b.amts)
        from table1 a
    inner join table1 b
    on a.key_cols = b.key_cols
    and b.qtr = case :cur-qtr
                        when 1 then 4
                        when 2 then 1
                        when 3 then 2
                        when 4 then 3 end
    and b.yr  = case :cur-qtr
                        when 1 then :cur-yr - 1 
                        else :cur-yr end
    where a.qtr = :cur-qtr
       and a.yr  = :cur-yr
    group by a.key_cols
    that lead me to the guesses like...
    (1) There are always all quarters.
    or
    (2) If some quarters were absent, it is not neccesary to report for the quarter nor for the next quarter.
    or
    (3) OP didn't noticed the conditions. So, the query should be modified by considering lelle12's issue.
    For example, use OUTER JOIN instead of INNER JOIN.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another my idea being inspired from lelle12's example was...

    Example 2:
    Code:
    SELECT key_cols
         , SUM( CASE
                WHEN qtr = :cur-qtr THEN
                     amts
                ELSE 0
                END
              ) AS sum_current_quarter
         , SUM( CASE
                WHEN :cur-qtr > 1
                 AND qtr = :cur-qtr - 1
                 OR  :cur-qtr = 1
                 AND qtr = 4        THEN
                     amts
                ELSE 0
                END
              ) AS sum_prior_quarter
     FROM  table1
     WHERE :cur-qtr > 1
      AND  yr = :cur-yr
      AND  qtr BETWEEN :cur-qtr - 1
                   AND :cur-qtr
      OR   :cur-qtr = 1
      AND
      (    yr = :cur-yr     AND qtr = 1
       OR  yr = :cur-yr - 1 AND qtr = 4
      )
     GROUP BY
           key_cols
    ;

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Play with SQL (and binary logic).

    Example 3: Assumed values of qtr and :cur-qtr were always IN (1, 2, 3, 4).
    Code:
    SELECT key_cols
         , SUM( INT(COS(qtr - :cur-qtr))
                  * amts
              ) AS sum_current_quarter
         , SUM( (  SIGN(:cur-qtr - 1)
                 * SIGN(:cur-qtr - qtr)
                 + SIGN(1 / :cur-qtr)
                 * SIGN(qtr / 4)
                ) * amts
              ) AS sum_prior_quarter
     FROM  table1
     WHERE :cur-qtr > 1
      AND  yr = :cur-yr
      AND  qtr BETWEEN :cur-qtr - 1
                   AND :cur-qtr
      OR   :cur-qtr = 1
      AND
      (    yr = :cur-yr     AND qtr = 1
       OR  yr = :cur-yr - 1 AND qtr = 4
      )
     GROUP BY
           key_cols
    ;
    Last edited by tonkuma; 10-12-13 at 06:26. Reason: Replace MAX with SUM.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I might thought too generally the issue, and made queries too complex.

    If utilized the fact that the value of quarter(qtr and :cur-qtr) was only IN (1, 2, 3, 4),
    the queries(Example 2 and Example 3) might be simplified.

    Example 4:
    Code:
    SELECT key_cols
         , SUM( CASE
                WHEN qtr =  cur_qtr THEN
                     amts
                ELSE 0
                END
              ) AS sum_current_quarter
         , SUM( CASE
                WHEN qtr <> cur_qtr THEN
                     amts
                ELSE 0
                END
              ) AS sum_prior_quarter
     FROM  table1
     WHERE yr  = cur_yr
       AND qtr BETWEEN cur_qtr - 1
                   AND cur_qtr
      OR   cur_qtr = 1
       AND yr  = cur_yr - 1
       AND qtr = 4
     GROUP BY
           key_cols
    ;

    Example 5:
    Code:
    SELECT key_cols
         , SUM(  INT(COS(qtr - cur_qtr))
               * amts
              ) AS sum_current_quarter
         , SUM(  ABS(SIGN(qtr - cur_qtr))
               * amts
              ) AS sum_prior_quarter
     FROM  table1
     WHERE yr  = cur_yr
       AND qtr BETWEEN cur_qtr - 1
                   AND cur_qtr
      OR   cur_qtr = 1
       AND yr  = cur_yr - 1
       AND qtr = 4
     GROUP BY
           key_cols
    ;
    Last edited by tonkuma; 10-12-13 at 06:32.

Posting Permissions

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