ROLLUP function in Vertica?

Moderator: NorbertKrupa

Post Reply
femibyte
Newbie
Newbie
Posts: 7
Joined: Thu Jun 14, 2012 4:59 pm

ROLLUP function in Vertica?

Post by femibyte » Thu Jun 14, 2012 5:02 pm

Hi, is there any equialent of Oracle's analytic ROLLUP function in Vertica? I need to obtain that functionality for some work I am doing in Vertica.

http://docs.oracle.com/cd/B28359_01/ser ... tm#autoId1
The ROLLUP function enables a SELECT statement to calculate multiple levels of subtotals across a specified group of dimensions, as well as a grand total. The ROLLUP function is a simple extension to the GROUP BY clause, so its syntax is extremely easy to use. The ROLLUP function is highly efficient, adding minimal overhead to a query.The action of the ROLLUP function is straightforward: it creates subtotals that roll up from the most detailed level to a grand total, following a grouping list specified in the ROLLUP function. ROLLUP takes as its argument an ordered list of grouping columns. First, it calculates the standard aggregate values specified in the GROUP BY clause. Then, it creates progressively higher-level subtotals, moving from right to left through the list of grouping columns. Finally, it creates a grand total.
Any help would be greatly appreciated.
Thanks

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: ROLLUP function in Vertica?

Post by JimKnicely » Thu Jun 14, 2012 6:29 pm

Hi, I do not think that Vertica has the ROLLUP function, but you can fake it so that a query's output would look like Oracle's output:

Example:

Code: Select all

dbadmin=> select * from region;
 region
--------
 east
 north
 south
 west
(4 rows)

dbadmin=> select * from sale;
 year | month | region | tot_sales
------+-------+--------+-----------
 2012 |     1 | north  |       100
 2012 |     1 | south  |       100
 2012 |     1 | east   |       110
 2012 |     1 | west   |       150
 2012 |     2 | north  |       200
 2012 |     2 | south  |       300
 2012 |     2 | east   |       150
 2012 |     2 | west   |       120
 2012 |     3 | north  |       100
 2012 |     3 | south  |       800
 2012 |     3 | east   |       950
 2012 |     3 | west   |       220
 2012 |     4 | north  |        50
 2012 |     4 | south  |        25
 2012 |     4 | east   |       340
 2012 |     4 | west   |       160
 2011 |     1 | north  |        50
 2011 |     1 | south  |       400
 2011 |     1 | east   |       100
 2011 |     1 | west   |       500
 2011 |     2 | north  |       530
 2011 |     2 | south  |       420
 2011 |     2 | east   |       400
 2011 |     2 | west   |       300
(24 rows)

dbadmin=> SELECT r.region,
dbadmin->        CAST(s.year AS VARCHAR) "year",
dbadmin->        SUM(s.tot_sales)
dbadmin->   FROM sale s
dbadmin->   JOIN region r
dbadmin->     ON r.region = s.region
dbadmin->  GROUP
dbadmin->     BY r.region,
dbadmin->        s.year
dbadmin-> UNION ALL
dbadmin-> SELECT r.region,
dbadmin->        NULL,
dbadmin->        SUM(s.tot_sales)
dbadmin->   FROM sale s
dbadmin->   JOIN region r
dbadmin->     ON s.region = r.region
dbadmin->  GROUP
dbadmin->     BY r.region
dbadmin-> UNION ALL
dbadmin-> SELECT NULL,
dbadmin->        NULL,
dbadmin->        SUM(s.tot_sales)
dbadmin->   FROM sale s
dbadmin->   JOIN region r
dbadmin->     ON s.region = r.region
dbadmin-> ORDER
dbadmin->     BY 1, 2;
 region | year | SUM
--------+------+------
 east   | 2011 |  500
 east   | 2012 | 1550
 east   |      | 2050
 north  | 2011 |  580
 north  | 2012 |  450
 north  |      | 1030
 south  | 2011 |  820
 south  | 2012 | 1225
 south  |      | 2045
 west   | 2011 |  800
 west   | 2012 |  650
 west   |      | 1450
        |      | 6575
(13 rows)
Although, I doubt this query would perform well with big data :(
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: ROLLUP function in Vertica?

Post by id10t » Thu Jun 14, 2012 6:56 pm

Hi femibyte!

In VerticaExtension package there are function GROUP_GENERATOR:
GROUP_GENERATOR - use to build ROLLUP, CUBE, GROUPING_SETS, MULTIPLE DISTINCT AGGREGATES in the NEW EE.
http://github.com/vertica/Vertica-Exten ... _functions

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: ROLLUP function in Vertica?

Post by JimKnicely » Thu Jun 14, 2012 7:03 pm

That's a much better suggestion :!:
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

femibyte
Newbie
Newbie
Posts: 7
Joined: Thu Jun 14, 2012 4:59 pm

Re: ROLLUP function in Vertica?

Post by femibyte » Thu Jun 14, 2012 8:20 pm

I'll take a look at the extension, thanks.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: ROLLUP function in Vertica?

Post by JimKnicely » Tue Jun 16, 2015 2:42 am

FYI... Vertica 7.1 SP2 now includes the ROLLUP Aggregate:

https://my.vertica.com/docs/7.1.x/HTML/ ... Clause.htm
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
usli06
Intermediate
Intermediate
Posts: 93
Joined: Wed Jan 25, 2012 4:53 am

Re: ROLLUP function in Vertica?

Post by usli06 » Tue Jun 16, 2015 3:03 am

That's cool! Thanks for the update!

Post Reply

Return to “Vertica SQL”