trafodion-codereview mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From liuyu000 <...@git.apache.org>
Subject [GitHub] trafodion pull request #1399: [TRAFODION-2909] Add ROLLUP Function for *Traf...
Date Thu, 18 Jan 2018 02:55:45 GMT
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 :)


---

Mime
View raw message