[2/3] incubator-trafodion git commit: OLAP Functions 2

[hz...@apache.org]

OLAP Functions 2


Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/db18be9f
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/db18be9f
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/db18be9f

Branch: refs/heads/release2.1
Commit: db18be9f3742f26ab25f3c6ce2cb5cc5bba23ac4
Parents: 39251ff
Author: liu.yu <yu...@esgyn.cn>
Authored: Tue Mar 7 14:34:50 2017 +0800
Committer: Hans Zeller <ha...@esgyn.com>
Committed: Wed Mar 8 01:45:18 2017 +0000

----------------------------------------------------------------------
 .../src/asciidoc/_chapters/olap_functions.adoc  | 99 +++++++++-----------
 1 file changed, 45 insertions(+), 54 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/db18be9f/docs/sql_reference/src/asciidoc/_chapters/olap_functions.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/olap_functions.adoc b/docs/sql_reference/src/asciidoc/_chapters/olap_functions.adoc
index 71817aa..26b5647 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/olap_functions.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/olap_functions.adoc
@@ -26,22 +26,15 @@
 [[olap_functions]]
 = OLAP Functions
 
-On-line Analytical Process (OLAP) functions provide the capability to partition source data into groups of rows, order the rows in each group, compute aggregates within a window frame for a row. Typically, an OLAP function in SQL is expressed in two components <FUNCTION> and <OVER>: <FUNCTION> specifies the function and the arguments, and <OVER> specifies the partitioning, the ordering and the window frames. <OVER> is sometimes called the window. OLAP functions are used to perform analytic tasks against data sets stored in or computed from a set of tables, such as moving average and ranks.
+On-line Analytical Process (OLAP) functions provide the capability to partition source data into groups of rows, order the rows in each group and compute aggregates within a window frame for a row.
 
-Trafodion also supports a set of sequence functions which are SQL extensions and have to be used together with a SEQUENCE BY clause. These sequence functions can be used to compute a running quantity of a column up to and include the current row as follows.
-
-* `RUNNINGAVG (column-expression)`: returns the average of non-null values. For more information, see <<runningavg_function,RUNNINGAVG Function>>.
-* `RUNNINGCOUNT{(*) | (column-expression)}`: returns the number of rows up to and including the current row. For more information, see <<runningcount_function,RUNNINGCOUNT Function>>.
-* `RUNNINGMAX(column-expression)`: returns the maximum of values of a column expression up to and including the current row. For more information, see <<runningmax_function,RUNNINGMAX Function>>.
-* `RUNNINGMIN(column-expression)`: returns the minimum of values of a column expression up to and including the current row. For more information, see <<runningmin_function,RUNNINGMIN Function>>.
-* `RUNNINGRANK(expression)`: Returns the rank of the given value of an intermediate result table ordered by a SEQUENCE BY clause in a SELECT statement. For more information, see <<runningrank_function,RUNNINGRANK Function>>.
-* `RUNNINGSTDDEV(column-expression)`: returns the standard deviation of non-null values of a column expression up to and including the current row. For more information, see <<runningstddev_function,RUNNINGSTDDEV Function>>.
-* `RUNNINGSUM(column-expression)`: returns the sum of non-null values of a column expression up to and including the current row. For more information, see <<runningsum_function,RUNNINGSUM Function>>.
-* `RUNNINGVARIANCE(column-expression)`: returns the variance of non-null values of a column expression up to and including the current row. For more information, see <<runningvariance_function,RUNNINGVARIANCE Function>>.
+Typically, an OLAP function in SQL is expressed in two components <FUNCTION> and <OVER>: <FUNCTION> specifies the function and the arguments, and <OVER> specifies the partitioning, the ordering and the window frames. <OVER> is sometimes called the window. OLAP functions are used to perform analytic tasks against data sets stored in or computed from a set of tables, such as moving average and ranks.
 
 This section describes the syntax and semantics of the OLAP window functions. The OLAP window functions
 are ANSI compliant.
 
+Trafodion OLAP functions have very similar semantics as Trafodion sequence functions which are SQL extensions and have to be used together with a SEQUENCE BY clause. For more information, see <<sequence_functions,Sequence Functions>>.
+
 [[considerations_for_window_functions]]
 == Considerations for Window Functions
 
@@ -145,7 +138,7 @@ GROUP BY num, workgroupnum, annualsalary;
 [[limitations_for_window_functions]]
 == Limitations for Window Functions
 
-Trafodion supports many ANSI compliant OLAP window functions. These functions include AVG, COUNT, DENSE_RANK, MAX, MIN, RANK, ROW_NUMBER, STDDEV, SUM, and VARIANCE. However, Trafodion imposes certain limitations on all window functions.
+Trafodion supports many ANSI compliant OLAP window functions. These functions include AVG, COUNT, DENSE_RANK, FIRST_VALUE, LAG, LAST_VALUE, LEAD, MAX, MIN, RANK, ROW_NUMBER, STDDEV, SUM, and VARIANCE. However, Trafodion imposes certain limitations on all window functions.
 
 * The ANSI _window-clause_ is not supported by {project-name}. Only the
 _inline-window-specification_ is supported. An attempt to use an ANSI
@@ -487,36 +480,31 @@ FROM persnl.employee;
 ```
 
 <<<
-[[lead_window_function]]
-== LEAD Window Function
-
-The LEAD window function can be used to access the subsequent rows (or columns from the subsequence rows) from the current rows, without using self-joins. The return type of LEAD is that of `_expression_`.
+[[first_value_window_function]]
+== FIRST_VALUE Window Function
 
-`LEAD (expression, offset, default-value)`
+The FIRST_VLAUE window function returns the first value from a sorted partition.
 
-* `_expression_`
-+
-Specifies a scalar expression or a column whose value from the subsequent row is to be returned. If the subsequent row does not exist in the current group, `_expression_` returns NULL.
+`FIRST_VALUE (expression)`
 
-* `_offset_`
+* _expression_
 +
-Specifies an expression that can be evaluated to a positive integer indicating the offset after the current row. For example, a value of 1 accesses the next row and a value of 3 accesses the third row from the current row.
+Specifies a scalar expression or a column whose value from the first row in the partition is to be returned.
 
-* `_default-value_`
-+
-Specifies the value if the value returned by _expression_ is NULL.
+NOTE: Trafodion only supports the function to return first value including null, and it is the default behavior.
 
 <<<
-[[examples_of_lead_window_function]]
-=== Examples of LEAD Window Function
+[[examples_of_first_value_window_function]]
+=== Examples of FIRST_VALUE Window Function
 
-The LEAD function in the following query returns the start date of next plan and DATEADD function subtracts one day from that date as the end date of the current plan.
+Each row returned by the following query contains the employee name, the department number, the salary and the pay difference over the lowest salary in the department.
 
 ```
-SELECT *,
-DATEADD(DAY, -1, LEAD(StartDate, 1,'01-Jan-2100')
-     OVER (PARTITION BY CustomerCode ORDER BY StartDate ASC)) AS EndDate
-FROM CustomerPlan
+SELECT ename, deptno, sal,
+       sal - FIRST_VALUE( sal ) OVER ( PARTITION BY  deptno
+  ORDER BY sal ) diff
+  FROM emp
+ORDER BY deptno, sal;
 ```
 
 <<<
@@ -553,42 +541,45 @@ FROM   CustomerPlan;
 ```
 
 <<<
-[[first_value_window_function]]
-== FIRST_VALUE Window Function
+[[last_value_window_function]]
+== LAST_VALUE Window Function
 
-The FIRST_VLAUE window function returns the first value from a sorted partition.
+The LAST_VALUE window function is similar to the FIRST_VALUE function with the exception that the value from the last row in a sorted partition is returned.
 
-`FIRST_VALUE (expression [ RESPECT NULLS | IGNORE NULLS ] )`
+<<<
+[[lead_window_function]]
+== LEAD Window Function
 
-* _expression_
+The LEAD window function can be used to access the subsequent rows (or columns from the subsequence rows) from the current rows, without using self-joins. The return type of LEAD is that of `_expression_`.
+
+`LEAD (expression, offset, default-value)`
+
+* `_expression_`
 +
-Specifies a scalar expression or a column whose value from the first row in the partition is to be returned.
+Specifies a scalar expression or a column whose value from the subsequent row is to be returned. If the subsequent row does not exist in the current group, `_expression_` returns NULL.
+
+* `_offset_`
++
+Specifies an expression that can be evaluated to a positive integer indicating the offset after the current row. For example, a value of 1 accesses the next row and a value of 3 accesses the third row from the current row.
 
-* IGNORE NULLS
+* `_default-value_`
 +
-Specifies the function to return first non-null value.
+Specifies the value if the value returned by _expression_ is NULL.
 
 <<<
-[[examples_of_first_value_window_function]]
-=== Examples of FIRST_VALUE Window Function
+[[examples_of_lead_window_function]]
+=== Examples of LEAD Window Function
 
-Each row returned by the following query contains the employee name, the department number, the salary and the pay difference over the lowest salary in the department.
+The LEAD function in the following query returns the start date of next plan and DATEADD function subtracts one day from that date as the end date of the current plan.
 
 ```
-SELECT ename, deptno, sal,
-       sal - FIRST_VALUE( sal ) OVER ( PARTITION BY  deptno
-  ORDER BY sal ) diff
-  FROM emp
-ORDER BY deptno, sal;
+SELECT *,
+DATEADD(DAY, -1, LEAD(StartDate, 1,'01-Jan-2100')
+     OVER (PARTITION BY CustomerCode ORDER BY StartDate ASC)) AS EndDate
+FROM CustomerPlan
 ```
 
 <<<
-[[last_value_window_function]]
-== LAST_VALUE Window Function
-
-The LAST_VALUE window function is similar to the FIRST_VALUE function with the exception that the value from the last row in a sorted partition is returned.
-
-<<<
 [[max_window_function]]
 == MAX Window Function