You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by db...@apache.org on 2018/01/18 18:47:09 UTC
[1/5] trafodion git commit: Add ROLLUP Function for *Trafodion SQL
Reference Manual*
Repository: trafodion
Updated Branches:
refs/heads/master 03f705b0f -> 13840cee2
Add ROLLUP Function for *Trafodion SQL Reference Manual*
Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/233d0962
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/233d0962
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/233d0962
Branch: refs/heads/master
Commit: 233d096295d239ac6ecf6a70c247b5ec33007dfb
Parents: 27897ca
Author: liu.yu <yu...@esgyn.cn>
Authored: Tue Jan 16 15:17:00 2018 +0800
Committer: liu.yu <yu...@esgyn.cn>
Committed: Tue Jan 16 15:17:00 2018 +0800
----------------------------------------------------------------------
.../sql_functions_and_expressions.adoc | 21 ++++++++++++++++++++
1 file changed, 21 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/trafodion/blob/233d0962/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
index 364211a..8f5ab1b 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
@@ -6338,6 +6338,27 @@ 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.
+
+<<<
[[round_function]]
== ROUND Function
[5/5] trafodion git commit: Merge [TRAFODION-2909] PR 1399 Add ROLLUP
to SQL Reference Manual
Posted by db...@apache.org.
Merge [TRAFODION-2909] PR 1399 Add ROLLUP to SQL Reference Manual
Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/13840cee
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/13840cee
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/13840cee
Branch: refs/heads/master
Commit: 13840cee22c7e9297884854e49384489f5f20923
Parents: 03f705b 82f1c4a
Author: Dave Birdsall <db...@apache.org>
Authored: Thu Jan 18 18:46:04 2018 +0000
Committer: Dave Birdsall <db...@apache.org>
Committed: Thu Jan 18 18:46:04 2018 +0000
----------------------------------------------------------------------
.../sql_functions_and_expressions.adoc | 296 +++++++++++++++++++
.../images/grouping-by-three-rollup-columns.jpg | Bin 0 -> 135453 bytes
2 files changed, 296 insertions(+)
----------------------------------------------------------------------
[3/5] trafodion git commit: Add Examples for ROLLUP
Posted by db...@apache.org.
Add Examples for ROLLUP
Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/b7fa86a9
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/b7fa86a9
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/b7fa86a9
Branch: refs/heads/master
Commit: b7fa86a9ccc608398aa9361fc03a715c0f64c33b
Parents: ab26bec
Author: liu.yu <yu...@esgyn.cn>
Authored: Tue Jan 16 16:39:50 2018 +0800
Committer: liu.yu <yu...@esgyn.cn>
Committed: Tue Jan 16 16:39:50 2018 +0800
----------------------------------------------------------------------
.../sql_functions_and_expressions.adoc | 248 +++++++++++++++++++
.../images/grouping-by-three-rollup-columns.jpg | Bin 0 -> 135453 bytes
2 files changed, 248 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/trafodion/blob/b7fa86a9/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
index 764c749..92c9d9a 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
@@ -6383,6 +6383,254 @@ ROLLUP removes the right-most column at each step, therefore the result sets var
| 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>>.
+
+* The column order of the example below is _delivery_year_, _region_ and _product_.
+
++
+```
+SELECT delivery_year, region, product, SUM (revenue) AS total_revenue
+FROM sales1
+GROUP BY ROLLUP (delivery_year, region, product);
+```
+
++
+```
+DELIVERY_YEAR REGION PRODUCT TOTAL_REVENUE
+------------- ------ -------------------------------- --------------------
+ 2016 A Dress 300
+ 2016 A Pullover 300
+ 2016 A NULL 600
+ 2016 B Dress 400
+ 2016 B NULL 400
+ 2016 NULL NULL 1000
+ 2017 A Pullover 500
+ 2017 A NULL 500
+ 2017 B Dress 600
+ 2017 B Pullover 1500
+ 2017 B NULL 2100
+ 2017 NULL NULL 2600
+ NULL NULL NULL 3600
+
+--- 13 row(s) selected.
+```
+
+* The column order of the example below is _product_, _region_ and _delivery_year_, the output is different than the result sets above.
+
++
+```
+SELECT product, region, delivery_year, SUM (revenue) AS total_revenue
+FROM sales1
+GROUP BY ROLLUP (product, region, delivery_year);
+```
+
++
+```
+PRODUCT REGION DELIVERY_YEAR TOTAL_REVENUE
+-------------------------------- ------ ------------- --------------------
+Dress A 2016 300
+Dress A NULL 300
+Dress B 2016 400
+Dress B 2017 600
+Dress B NULL 1000
+Dress NULL NULL 1300
+Pullover A 2016 300
+Pullover A 2017 500
+Pullover A NULL 800
+Pullover B 2017 1500
+Pullover B NULL 1500
+Pullover NULL NULL 2300
+NULL NULL NULL 3600
+
+--- 13 row(s) selected.
+```
+
+[[examples_of_using_rollup_with_order_by]]
+==== Examples of Using ROLLUP with ORDER BY
+
+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>>.
+
+This example uses ROLLUP with the ORDER BY clause to sort the results.
+
+```
+SELECT delivery_year, product, SUM (revenue) AS total_revenue
+FROM sales1
+GROUP BY ROLLUP (delivery_year, product)
+ORDER BY total_revenue;
+```
+
+```
+DELIVERY_YEAR PRODUCT TOTAL_REVENUE
+------------- -------------------------------- --------------------
+ 2016 Pullover 300
+ 2017 Dress 600
+ 2016 Dress 700
+ 2016 NULL 1000
+ 2017 Pullover 2000
+ 2017 NULL 2600
+ NULL NULL 3600
+
+--- 7 row(s) selected.
+```
+
<<<
[[round_function]]
== ROUND Function
http://git-wip-us.apache.org/repos/asf/trafodion/blob/b7fa86a9/docs/sql_reference/src/images/grouping-by-three-rollup-columns.jpg
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/images/grouping-by-three-rollup-columns.jpg b/docs/sql_reference/src/images/grouping-by-three-rollup-columns.jpg
new file mode 100644
index 0000000..a3ea166
Binary files /dev/null and b/docs/sql_reference/src/images/grouping-by-three-rollup-columns.jpg differ
[4/5] trafodion git commit: Incorporate Comments 1
Posted by db...@apache.org.
Incorporate Comments 1
Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/82f1c4ae
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/82f1c4ae
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/82f1c4ae
Branch: refs/heads/master
Commit: 82f1c4ae41ad1b4456a2c7a9e1581d5219eee602
Parents: b7fa86a
Author: liu.yu <yu...@esgyn.cn>
Authored: Thu Jan 18 11:00:33 2018 +0800
Committer: liu.yu <yu...@esgyn.cn>
Committed: Thu Jan 18 11:00:33 2018 +0800
----------------------------------------------------------------------
.../_chapters/sql_functions_and_expressions.adoc | 14 ++++++++------
1 file changed, 8 insertions(+), 6 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/trafodion/blob/82f1c4ae/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
index 92c9d9a..0d8dd6c 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
@@ -6341,17 +6341,19 @@ 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.
+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.
+
+ROLLUP 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.
```
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).
+ROLLUP generates n+1 levels of subtotals, including a 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.
+* First-level: the usual aggregate values as 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.
@@ -6364,7 +6366,7 @@ NOTE: Trafodion does not support CUBE function which works slightly differently
[[null_in_result_sets]]
==== NULL in Result Sets
-* The NULLs in each super-aggregate row represent subtotals and grand total.
+* In super-aggregate rows representing subtotals or the grand total, lower level grouping columns are replaced by NULLs.
* The NULLs in selected columns are considered equal and sorted into one NULL group in result sets.
[[using_rollup_with_the_column_order_reversed]]
@@ -6429,7 +6431,7 @@ DELIVERY_YEAR TOTAL_REVENUE
* 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
+ROLLUP firstly aggregates at the lowest level (_region_) and then rolls up those aggregations to the next
level (_delivery_year_), finally it produces a grand total across these two levels.
+
@@ -6541,7 +6543,7 @@ DELIVERY_YEAR REGION PRODUCT TOTAL_REVENUE
[[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>>.
+Suppose that we have the same _sales1_ table as shown in the <<examples_of_grouping_by_one_or_multiple_rollup_columns,Examples of Grouping By One or Multiple Rollup Columns>>.
* The column order of the example below is _delivery_year_, _region_ and _product_.
[2/5] trafodion git commit: Add Considerations for ROLLUP
Posted by db...@apache.org.
Add Considerations for ROLLUP
Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/ab26bec4
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/ab26bec4
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/ab26bec4
Branch: refs/heads/master
Commit: ab26bec4d6a41c6f9217c6719f6736e4c3d4701f
Parents: 233d096
Author: liu.yu <yu...@esgyn.cn>
Authored: Tue Jan 16 15:58:38 2018 +0800
Committer: liu.yu <yu...@esgyn.cn>
Committed: Tue Jan 16 15:58:38 2018 +0800
----------------------------------------------------------------------
.../sql_functions_and_expressions.adoc | 25 ++++++++++++++++++++
1 file changed, 25 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/trafodion/blob/ab26bec4/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
index 8f5ab1b..764c749 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
@@ -6358,6 +6358,31 @@ For example, a query that contains three rollup columns returns the following ro
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
+|===
+
<<<
[[round_function]]
== ROUND Function