[
https://issues.apache.org/jira/browse/TRAFODION-2909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16328773#comment-16328773
]
ASF GitHub Bot commented on TRAFODION-2909:
-------------------------------------------
Github user traflm commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1399#discussion_r162052172
--- Diff:
docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc ---
@@ -6337,6 +6337,300 @@ UPDATE persnl.job
SET jobdesc = RIGHT (jobdesc, 12);
```
+<<<
+[[rollup_function]]
+== ROLLUP Function
+
+The ROLLUP function calculates multiple levels of subtotals aggregating
from right to left through the comma-separated list of columns, and provides a
grand total. It is a an extension to the `GROUP BY` clause and can be used with
`ORDER BY` to sort the results.
+
+```
+SELECT…GROUP BY ROLLUP (column 1, [column 2,]…[column n])
+```
+
+ROLLUP generates n+1 levels of subtotals and grand total, where n is the
number of the selected column(s).
+
+For example, a query that contains three rollup columns returns the
following rows:
+
+* First-level: stand aggregate values calculated by GROUP BY clause
without using ROLLUP.
+* Second-level: subtotals aggregating across column 3 for each combination
of column 1 and column 2.
+* Third-level: subtotals aggregating across column 2 and column 3 for each
column 1.
+* Fourth-level: the grand total row.
+
+NOTE: Trafodion does not support CUBE function which works slightly
differently from ROLLUP.
+
+[[considerations_for_rollup]]
+=== Considerations for ROLLUP
+
+[[null_in_result_sets]]
+==== NULL in Result Sets
+
+* The NULLs in each super-aggregate row represent subtotals and grand
total.
+* The NULLs in selected columns are considered equal and sorted into one
NULL group in result sets.
+
+[[using_rollup_with_the_column_order_reversed]]
+==== Using ROLLUP with the Column Order Reversed
+
+ROLLUP removes the right-most column at each step, therefore the result
sets vary with the column order specified in the comma-separated list.
+
+[cols="50%,50%"]
+|===
+| If the column order is _country_, _state_, _city_ and _name_, ROLLUP
returns following groupings.
+| If the column order is _name_, _city_, _state_ and _country_, ROLLUP
returns following groupings.
+| _country_, _state_, _city_ and _name_ | _name_, _city_, _state_ and
_country_
+| _country_, _state_ and _city_ | _name_, _city_ and _state_
+| _country_ and _state_ | _name_ and _city_
+| _country_ | _name_
+| grand total | grand total
+|===
+
+[[examples_of_rollup]]
+=== Examples of ROLLUP
+
+[[examples_of_grouping_by_one_or_multiple_rollup_columns]]
+==== Examples of Grouping By One or Multiple Rollup Columns
+
+Suppose that we have a _sales1_ table like this:
+
+```
+SELECT * FROM sales1;
+
+DELIVERY_YEAR REGION PRODUCT REVENUE
+------------- ------ -------------------------------- -----------
+ 2016 A Dress 100
+ 2016 A Dress 200
+ 2016 A Pullover 300
+ 2016 B Dress 400
+ 2017 A Pullover 500
+ 2017 B Dress 600
+ 2017 B Pullover 700
+ 2017 B Pullover 800
+
+--- 8 row(s) selected.
+```
+
+* This is an example of grouping by one rollup column.
++
+```
+SELECT delivery_year, SUM (revenue) AS total_revenue
+FROM sales1
+GROUP BY ROLLUP (delivery_year);
+```
+
++
+```
+DELIVERY_YEAR TOTAL_REVENUE
+------------- --------------------
+ 2016 1000
+ 2017 2600
+ NULL 3600
+
+--- 3 row(s) selected.
+```
+
+* This is an example of grouping by two rollup columns.
++
+ROLLUP firstly aggregates at the lowest level (_region_) and then rollup
those aggregations to the next
+level (_delivery_year_), finally it produces a grand total across these
two levels.
+
++
+```
+SELECT delivery_year, region, SUM (revenue) AS total_revenue
+FROM sales1
+GROUP BY ROLLUP (delivery_year, region);
+```
+
++
+```
+DELIVERY_YEAR REGION TOTAL_REVENUE
+------------- ------ --------------------
+ 2016 A 600
+ 2016 B 400
+ 2016 NULL 1000
+ 2017 A 500
+ 2017 B 2100
+ 2017 NULL 2600
+ NULL NULL 3600
+
+--- 7 row(s) selected.
+```
++
+
+* This is an example of grouping by three rollup columns.
++
+```
+SELECT delivery_year, region, product, SUM (revenue) AS total_revenue
+FROM sales1
+GROUP BY ROLLUP (delivery_year, region, product);
+```
+
++
+.Grouping By Three Rollup Columns
+image::grouping-by-three-rollup-columns.jpg[700,700]
+
++
+** First-level: the rows marked in *blue* are the total revenue for each
year (_2016_ and _2017_), each region (_A_ and _B_) and each product (_Dress_
and _Pullover_), they are caculated by GROUP BY instead of ROLLUP.
+
++
+** Second-level: the rows marked in *red* provide the total revenue for
the given _delivery_year_ and _region_ by _product_.
++
+These rows have the _product_ columns set to NULL.
+
++
+** Third-level: the rows marked in *yellow* show the total revenue in each
year (_2016_ and _2017_).
++
+These rows have the _region_ and _product_ columns set to NULL.
+
++
+** Fourth-level: the row marked in *purple* aggregates over all rows in
the _delivery_year_, _region_ and _product_ columns.
++
+This row has the _delivery_year_, _region_ and _product_ columns set to
NULL.
+
+[[examples_of_null]]
+=== Examples of NULL
+
+The example below demonstrates how ROLLUP treats NULLs in the selected
columns and generates NULLs for super-aggregate rows.
+
+Suppose that we have a _sales2_ table like this:
+
+```
+SELECT * FROM sales2;
+
+DELIVERY_YEAR REGION PRODUCT REVENUE
+------------- ------ -------------------------------- -----------
+ NULL A Dress 100
+ NULL A Dress 200
+ 2016 A Pullover 300
+ 2016 B Dress 400
+ 2017 A Pullover 500
+ 2017 B Dress 600
+ NULL B Pullover 700
+ NULL B Pullover 800
+
+--- 8 row(s) selected.
+```
+
+```
+SELECT delivery_year, region, product, SUM (revenue) AS total_revenue
+FROM sales2
+GROUP BY ROLLUP (delivery_year, region, product);
+```
+
+```
+DELIVERY_YEAR REGION PRODUCT TOTAL_REVENUE
+------------- ------ -------------------------------- --------------------
+ 2016 A Pullover 300
+ 2016 A NULL 300
+ 2016 B Dress 400
+ 2016 B NULL 400
+ 2016 NULL NULL 700
+ 2017 A Pullover 500
+ 2017 A NULL 500
+ 2017 B Dress 600
+ 2017 B NULL 600
+ 2017 NULL NULL 1100
+ NULL A Dress 300
+ NULL A NULL 300
+ NULL B Pullover 1500
+ NULL B NULL 1500
+ NULL NULL NULL 1800
+ NULL NULL NULL 3600
+
--- End diff --
this is great example !
> Add ROLLUP Function for *Trafodion SQL Reference Manual*
> ---------------------------------------------------------
>
> Key: TRAFODION-2909
> URL: https://issues.apache.org/jira/browse/TRAFODION-2909
> Project: Apache Trafodion
> Issue Type: Documentation
> Reporter: Liu Yu
> Assignee: Liu Yu
> Priority: Major
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
