Oracle LAST_VALUE()

오라클 LAST_VALUE()

 

LAST_VALUE()

   - 

 

LAST_VALUE(expr) OVER (analytic_clause) 

LAST_VALUE(expr RESPECT|IGNORE NULL) OVER (analytic_clause) 

의 형태

 

LAST_VALUE is an analytic function that is useful for data densification. It returns the last value in an ordered set of values.

{RESPECT | IGNORE} NULLS determine whether null values of expr are included in or eliminated from the calculation. the default is RESPECT NULLS. If the last value in the set is null, then the function returns NULL unless you specify IGNORE NULLS, then LAST_VALUE returns thea last non-null value in the set, or NULL if all values are null. 

 

You cannot nest analytic functions by using LAST_VALUE or any other analytic function for expr. However, you can use other built-in function expressions for expr.

 

If you omit the windowing_clause of the analytic_clause, it defaults to RANGE BETWEEN UNBOUNDED PRECDEING AND CURRENT ROW. This default sometimes returns an unexpected value, because the last value in the window is at the bottom of the window, which is not fixed. It keeps changing as the current row changes. For expected results, specify the windowing_clause as RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Alternatively, you can specify the windowing_clause as RANGE BETWEEN CURRNET ROW UNBOUNDED FOLLOWING.

 

SQL >
SELECT
    last_name,
    salary,
    hire_date,
    LAST_VALUE(hire_date) OVER(ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lv
FROM (SELECT * FROM employees where department_id=90 order by hire_date)