Confused on how to get a percentage of total using OLAP functions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

James Conrad StJohn Foreman - 04 Apr 2006 11:21 GMT

[8.2.4 on 32-bit linux]
Have found
http://www-128.ibm.com/developerworks/db2/library/techarticle/lyle/0110lyle.html
which is quite helpful, but doesn't quite tell me what I want.

I have a table, advertising_spend with two columns, date and spend.

SELECT date, spend, sum(spend) over (order by date) as cumulative_spend
FROM advertising_spend
WHERE date BETWEEN '01.03.2006' AND '31.03.2006'

gives me cumulative spend quite happily.

But how do I go about getting either the percentage of spend that each
day is of the total, or the percentage that the cumulative spend is of
the total?

Guessed at
select date, cost, sum(cost) over (order by date) as cumulative_sum,
cost/sum(cost) over (partition by date) percentage
FROM advertising_spend
WHERE date BETWEEN '01.03.2006' AND '31.03.2006'

which gives me 1 for each percentage, and tried removing the 'over
(partition by date)' part, but that gave me a syntax error, predictably
enough.

Is the percentage computable in one query, or do I need a second query
to provide the total spend, and then divide by that?

Thanks

JCSJF

Reply to this Message

Brian Tkatch - 04 Apr 2006 15:33 GMT

I'm going to guess. But perhaps WITH would be useful? (I'm not too
familiar with WITH.)

WITH Total(Spend) AS (SELECT SUM(Spend) FROM Advertising_Spend WHERE
Date BETWEEN '01.03.2006' AND '31.03.2006')
SELECT Date, Spend, Total/Spend Percentage
FROM Advertising_Spend
WHERE date BETWEEN '01.03.2006' AND '31.03.2006'

B.

Reply to this Message

James Conrad StJohn Foreman - 04 Apr 2006 16:41 GMT

Thanks - I'm not great on WITH myself, but that looks to make sense.

Currently we're trying

SELECT Date, Spend, spend/total Percentage
FROM Advertising_Spend,
(SELECT SUM(Spend) total_spend FROM Advertising_Spend WHERE
Date BETWEEN '01.03.2006' AND '31.03.2006') as a
WHERE date BETWEEN '01.03.2006' AND '31.03.2006'

but yours has at least the virtue of readability. Will bash this
through the optimiser and see if it's also more efficiently

Reply to this Message

Serge Rielau - 04 Apr 2006 18:56 GMT

> [8.2.4 on 32-bit linux]
> Have found
[quoted text clipped - 25 lines]
> Is the percentage computable in one query, or do I need a second query
> to provide the total spend, and then divide by that?

Are you sure this query returns cumulative spent as is?
I think you need a clause to state that you the window includes all
preceeding, but only up to the current row.
Currently you get the total spent (and the ORDER BY is useless).

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

mirof007 - 04 Apr 2006 21:55 GMT

Serge, IIRC, the default window spec in the presence of the ORDER BY
clause is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so yes,
he's getting a cumulative sum. Without the ORDER BY in the over()
clause the default window spec is the entire window (BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING).

James, to get percentage of total spend, try the following (haven't
checked for syntax errors, but hopefully it'll give you the idea):

SELECT date, spend, cume_spend, total_spend,
spend/total_spend as daily_percent,
cume_spend/total_spend as cume_percent
FROM ( SELECT date, spend,
sum(spend) over (order by date rows unbounded
preceding and current row) as cume_spend,
sum(spend) over (order by date rows unbounded
preceding and unbounded following) as total_spend
FROM advertising_spend
WHERE date BETWEEN '01.03.2006' AND '31.03.2006') AS TMP;

Regards,
Miro

Reply to this Message

Serge Rielau - 04 Apr 2006 22:45 GMT

> Serge, IIRC, the default window spec in the presence of the ORDER BY
> clause is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so yes,
> he's getting a cumulative sum. Without the ORDER BY in the over()
> clause the default window spec is the entire window (BETWEEN UNBOUNDED
> PRECEDING AND UNBOUNDED FOLLOWING).

Interesting.... tx Miro

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message