You are viewing a plain text version of this content. The canonical link for it is here.
Posted to codereview@trafodion.apache.org by liuyu000 <gi...@git.apache.org> on 2018/01/16 07:18:51 UTC
[GitHub] trafodion pull request #1399: [TRAFODION-2909] Add ROLLUP Function for *Traf...
GitHub user liuyu000 opened a pull request:
https://github.com/apache/trafodion/pull/1399
[TRAFODION-2909] Add ROLLUP Function for *Trafodion SQL Reference Manual*
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/liuyu000/trafodion ROLLUP
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/trafodion/pull/1399.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #1399
----
commit 233d096295d239ac6ecf6a70c247b5ec33007dfb
Author: liu.yu <yu...@...>
Date: 2018-01-16T07:17:00Z
Add ROLLUP Function for *Trafodion SQL Reference Manual*
----
---
[GitHub] trafodion pull request #1399: [TRAFODION-2909] Add ROLLUP Function for *Traf...
Posted by traflm <gi...@git.apache.org>.
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 !
---
[GitHub] trafodion pull request #1399: [TRAFODION-2909] Add ROLLUP Function for *Traf...
Posted by traflm <gi...@git.apache.org>.
Github user traflm commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1399#discussion_r162050572
--- 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]]
--- End diff --
I think this is not a function but a clause, so I suggest move this chapter into chapter 6?
I am not sure about this, @DaveBirdsall what do you think here?
---
[GitHub] trafodion pull request #1399: [TRAFODION-2909] Add ROLLUP Function for *Traf...
Posted by liuyu000 <gi...@git.apache.org>.
Github user liuyu000 commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1399#discussion_r162237087
--- 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.
--- End diff --
Thanks Dave, your comment has been incorporated :)
---
[GitHub] trafodion pull request #1399: [TRAFODION-2909] Add ROLLUP Function for *Traf...
Posted by liuyu000 <gi...@git.apache.org>.
Github user liuyu000 commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1399#discussion_r162237017
--- 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).
--- End diff --
Thanks Dave, your comment has been incorporated :)
---
[GitHub] trafodion pull request #1399: [TRAFODION-2909] Add ROLLUP Function for *Traf...
Posted by DaveBirdsall <gi...@git.apache.org>.
Github user DaveBirdsall commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1399#discussion_r162194005
--- 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
+
+--- 16 row(s) selected.
+```
+
+[[examples_of_using_rollup_with_the_column_order_reversed]]
+==== Examples of Using ROLLUP with the Column Order Reversed
+
+Suppose that we have the same _sale1_ table as shown in the <<examples_of_grouping_by_one_or_multiple_rollup_columns,Examples of Grouping By One or Multiple Rollup Columns>>.
--- End diff --
"_sale1_" should be "_sales1_"
---
[GitHub] trafodion pull request #1399: [TRAFODION-2909] Add ROLLUP Function for *Traf...
Posted by DaveBirdsall <gi...@git.apache.org>.
Github user DaveBirdsall commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1399#discussion_r162192952
--- 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.
--- End diff --
Possible wordsmith: "the usual aggregate values as calculated..."
---
[GitHub] trafodion pull request #1399: [TRAFODION-2909] Add ROLLUP Function for *Traf...
Posted by liuyu000 <gi...@git.apache.org>.
Github user liuyu000 commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1399#discussion_r162238258
--- 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
--- End diff --
Thanks Dave, your comment has been incorporated :)
---
[GitHub] trafodion pull request #1399: [TRAFODION-2909] Add ROLLUP Function for *Traf...
Posted by DaveBirdsall <gi...@git.apache.org>.
Github user DaveBirdsall commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1399#discussion_r162193263
--- 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.
--- End diff --
Possible wordsmith: "In super-aggregate rows representing subtotals or the grand total, lower level grouping columns are replaced by NULLs."
---
[GitHub] trafodion pull request #1399: [TRAFODION-2909] Add ROLLUP Function for *Traf...
Posted by DaveBirdsall <gi...@git.apache.org>.
Github user DaveBirdsall commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1399#discussion_r162193692
--- 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
--- End diff --
Suggested wordsmith: "... and then rolls up those aggregations ..."
---
[GitHub] trafodion pull request #1399: [TRAFODION-2909] Add ROLLUP Function for *Traf...
Posted by DaveBirdsall <gi...@git.apache.org>.
Github user DaveBirdsall commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1399#discussion_r162192732
--- 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).
--- End diff --
Suggested wordsmith "ROLLUP generates n+1 levels of subtotals, including a grand total, ..."
---
[GitHub] trafodion pull request #1399: [TRAFODION-2909] Add ROLLUP Function for *Traf...
Posted by liuyu000 <gi...@git.apache.org>.
Github user liuyu000 commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1399#discussion_r162238124
--- 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.
--- End diff --
Thanks Dave, your comment has been incorporated :)
---
[GitHub] trafodion pull request #1399: [TRAFODION-2909] Add ROLLUP Function for *Traf...
Posted by liuyu000 <gi...@git.apache.org>.
Github user liuyu000 commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1399#discussion_r162236344
--- 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.
--- End diff --
Thanks Dave, your comment has been incorporated :)
---
[GitHub] trafodion pull request #1399: [TRAFODION-2909] Add ROLLUP Function for *Traf...
Posted by asfgit <gi...@git.apache.org>.
Github user asfgit closed the pull request at:
https://github.com/apache/trafodion/pull/1399
---
[GitHub] trafodion pull request #1399: [TRAFODION-2909] Add ROLLUP Function for *Traf...
Posted by liuyu000 <gi...@git.apache.org>.
Github user liuyu000 commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1399#discussion_r162238300
--- 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
+
+--- 16 row(s) selected.
+```
+
+[[examples_of_using_rollup_with_the_column_order_reversed]]
+==== Examples of Using ROLLUP with the Column Order Reversed
+
+Suppose that we have the same _sale1_ table as shown in the <<examples_of_grouping_by_one_or_multiple_rollup_columns,Examples of Grouping By One or Multiple Rollup Columns>>.
--- End diff --
Thanks Dave, your comment has been incorporated :)
---
[GitHub] trafodion pull request #1399: [TRAFODION-2909] Add ROLLUP Function for *Traf...
Posted by liuyu000 <gi...@git.apache.org>.
Github user liuyu000 commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1399#discussion_r162231374
--- 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 --
Thanks Ming :)
---
[GitHub] trafodion pull request #1399: [TRAFODION-2909] Add ROLLUP Function for *Traf...
Posted by DaveBirdsall <gi...@git.apache.org>.
Github user DaveBirdsall commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1399#discussion_r162192522
--- 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.
--- End diff --
There is no "ORDER BY ROLLUP" syntax. But it looks like there are functions such as GROUPING that can refer to whether a column is used as a grouping column in a rollup result row, so one can order the detail vs. the summary rows. There's also a small typo here "...is a an..." Possible wordsmith for the last sentence: "It is an extension to the 'GROUP BY' clause. Related features such as the GROUPING function can be used with 'ORDER BY' to control the placement of summary results."
---