Weekly/monthly/quarterly grouping in query

Question

Lets say I have table with following columns

1. Client - string.
2. Profit - integer.
3. Deal_Date - date.

I need query that will retrieve sum of profit breakdown by week/month/quater etc.

Expected output for weeks

1 row, sum (profit) of all deals that registered from (03.19.2012 - 03.12.2012).
2 row, sum (profit) of all deals that registered from (03.12.2012 - 03.05.2012).
...
n row, sum (profit) of all deals that registered from (05.17.2011 - 05.10.2011).

NOTE (dates set just for example)

The same for month, years, etc.

Could someone help me with such query?

Btw performance is very important.

Solution

This query uses simple date formats to extract the various elements you want to track and analytics to get the sums.

select client
       , yr
       , qtr
       , wk
       , sum ( profit ) over ( partition by client, yr) as yr_profit
       , sum ( profit ) over ( partition by client, yr, qtr) as qtr_profit
       , sum ( profit ) over ( partition by client, yr, wk) as wk_profit
from ( 
         select client
                , profit
                , to_char(deal_date, 'yyyy') as yr
                , to_char(deal_date, 'q') as qt
                , to_char(deal_date, 'ww') as wk
          from your_table )
/

This will produce one row for each row in the current table. So you probebly will want to wrap it in a further outer query which only returns only distinct rows.

A variant would be to use rollup instead. I'm not sure how well that works when the grouping criteria aren't perfectly hierarchical (weeks don't fit neatly into quarters).

select client
       , yr
       , qtr
       , wk
       , sum ( profit ) as profit
from ( 
         select client
                , profit
                , to_char(deal_date, 'yyyy') as yr
                , to_char(deal_date, 'q') as qt
                , to_char(deal_date, 'ww') as wk
          from your_table )
group by rollup ( client, yr, qtr, wk )
/

Comments

[APC]

This would have been a bad answer in 1997 but Row By Agonising Row is unacceptable now. Oracle has really moved on in the last fifteen years and has lots of different, better performing options.