Analytic function insight wanted

From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 2 Mar 2007 20:07:52 -0700
Message-ID: <45e8f498$1@news.victoria.tc.ca>

Hello

I am looking for some insight on solving this problem using analytic functions.

I already have another, non-analytic, solution, but I am not happy with it because it seems overly complicated. As a procedure I am sure I could just scan once thru the sorted data, so it seems "obvious" that I should be able to find an easy elegant solution to this in SQL, and I think that analytic functions would do that, but I just can't think how to use them here.

I have a table like this (simplified version shown here).

        year    category        value1
        ----    --------        ------

        2000    A1              1
        2000    A2              10
        2000    B1              2
        2000    B2              20
        2000    C               50

        2001    A1              3
        2001    B1              4

        2002    A2              60
        2002    B1              8
        2002    C               7

I show a kind of rollup with three categories, each being the sum of the two most recently available values for each year. A key issue being that the value for a year may be missing and so you need to use the most recent value from the past. A complication is that the final value for a year is actually the sum of two values, each of which may be from different previous years. (One column is not a sum so I show that just in case it makes a difference.)

(The "+" is just to illustrate the numbers involved, the actual value is the mathematic sum of the numbers.)

        year    sum(a1+a2)      sum(b1+b2)      C
        ----    ----------      ----------      -
        2000    1+10            2+20            50
        2001    3+10            4+20            50
        2002    3+60            8+20            7

Right now I use a partial cartesian join and a not-exists to provide an intermediate inline view that "fills in" the missing values. The final result is then just a simple group-by+decode of that intermediate view.

This just a simplified example of the real thing.

select year

, sum( CASE WHEN category IN ('A1','A2')

               THEN value1 ELSE NULL END ) AS sum_A


        , sum( CASE WHEN category IN ('B1','B2') 

               THEN value1 ELSE NULL END ) AS sum_B


        , sum( CASE WHEN category IN ('C'      ) 

               THEN value1 ELSE NULL END ) AS the_C

from (select year,category,value1 from big ugly join) my_intermediate_view group by year

I think I should be able to do the whole thing in the one select statement - if I figure out how to correctly use the correct set of analytic functions to provide the most recent values for the pairs of values inside each sum.

Can anyone provide insight on how to do this with analytic functions?

Thanks for any feedback. Received on Fri Mar 02 2007 - 21:07:52 CST