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:11 UTC
[3/5] trafodion git commit: Add Examples for ROLLUP
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