[
https://issues.apache.org/jira/browse/TRAFODION-2520?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15898725#comment-15898725
]
ASF GitHub Bot commented on TRAFODION-2520:
-------------------------------------------
Github user liuyu000 commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/990#discussion_r104586154
--- Diff: docs/sql_reference/src/asciidoc/_chapters/olap_functions.adoc ---
@@ -461,8 +487,110 @@ 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_`.
+
+`LEAD (expression, offset, default-value)`
+
+* `_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.
+
+* `_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.
+
+* `_default-value_`
++
+Specifies the value if the value returned by _expression_ is NULL.
+
+<<<
+[[examples_of_lead_window_function]]
+=== Examples of LEAD 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.
+
+```
+SELECT *,
+DATEADD(DAY, -1, LEAD(StartDate, 1,'01-Jan-2100')
+ OVER (PARTITION BY CustomerCode ORDER BY StartDate ASC)) AS EndDate
+FROM CustomerPlan
+```
+
+<<<
+[[lag_window_function]]
+== LAG Window Function
+
+The LAG window function can be used to access the previous rows from the
same result set without using self-joins. The LAG function takes the same three
parameters as the LEAD function with the exception that `_offset_` specifies
the offset to access a row that comes before the current row. The return type
of LAG is that of `_expression_`.
+
+`LAG (expression, offset, default-value)`
+
+* _expression_
++
+Specifies a scalar expression or a column whose value from the previous
row is to be returned. If the previous 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 before the current row. For example, a value of 1
accesses the previous row and a value of 3 accesses the third row before the
current row.
+
+* _default-value_
++
+Specifies the value if the value returned by `_expression_` is NULL.
+
+<<<
+[[examples_of_lag_window_function]]
+=== Examples of LAG Window Function
+
+The LAG function in the following query returns the plan code of a
previous plan and ’N/A’ for the first plan.
+
+```
+SELECT CustomerCode, PlanCode AS CurrentPlanCode,
+ LAG(PlanCode, 1, 'NA')
+ OVER (PARTITION BY CustomerCode ORDER BY StartDate ASC)
AS LastPlan
+FROM CustomerPlan;
+```
+
+<<<
+[[first_value_window_function]]
+== FIRST_VALUE Window Function
+
+The FIRST_VLAUE window function returns the first value from a sorted
partition.
+
+`FIRST_VALUE (expression [ RESPECT NULLS | IGNORE NULLS ] )`
+
+* _expression_
++
+Specifies a scalar expression or a column whose value from the first row
in the partition is to be returned.
+
+* IGNORE NULLS
++
+Specifies the function to return first non-null value.
+
+<<<
+[[examples_of_first_value_window_function]]
+=== Examples of FIRST_VALUE 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.
+
+```
+SELECT ename, deptno, sal,
+ sal - FIRST_VALUE( sal ) OVER ( PARTITION BY deptno
+ ORDER BY sal ) diff
+ FROM emp
+ORDER BY deptno, sal;
+```
+
+<<<
+[[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
+== MAX Window Function
--- End diff --
My pleasure, Ming. :)
> Add details for OLAP function + Add LEAD, LAG, FIRST_VALUE & LAST_VALUE
> window functions
> ----------------------------------------------------------------------------------------
>
> Key: TRAFODION-2520
> URL: https://issues.apache.org/jira/browse/TRAFODION-2520
> Project: Apache Trafodion
> Issue Type: Documentation
> Reporter: Liu Yu
> Assignee: Liu Yu
>
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
