Monday, October 18, 2010

ROLLUP Analytical function in PostgreSQL.

Currently, there is no version of PG, which supports the rollup.

However, people look for this analytical function features.

ROLLUP queries result can be achieve using the UNION of Queries.
First Let's Understand what does rollup do:

If SQL Query has col1,col2,col3,aggregate(col4) then rollup

Processing would be something like this.
1. Show the aggregate of col4 as per the col1,col2,col3
2. Then rollup will do the subtotal and will show the result as per the as aggregate of based on col1,col2
3. Then it will show the aggregate/subtotal as per the col1.
4. And at end Total/Sum

In short, it creates progressively higher-level subtotals, moving from right to left through the list of grouping columns. Finally, it creates a grand total.

In PG, this can be achieve by writing a SubQueries and and UNION those.
So, if the rollup query is something like given below:
select col1,col2,col3,agg(col4) from relation group by rollup(col1,col2,col3) 
Then in PG above can be written as:
select col1, col2,col3 agg(col4) from relation group by col1,col2,col3
UNION
select col1,col2,NULL,agg(col4) from relation group by col1,col2
UNION
select col1,NULL,NULL,agg(col4) from relation group by col1
UNION
select NULL,NULL,NULL, agg(col4) from relation;

Following is an example:
In Oracle:
select manager_id,job_id,sum(salary) from hr.employees group by rollup(manager_id,job_id);
In PG:
select manager_id,job_id,sum(salary) from hr.employees group by manager_id,job_id
UNION
select manager_id , NULL,sum(salary) from hr.employees group by manager_id
UNION
select NULL,NULL,sum(salary) from hr.employees;

No comments:

Post a Comment