[Oracle] NTH_VALUE()

오라클 NTH_VALUE()

 

NTH_VALUE()

   -

 

NTH_VALUE(measure_expr, n) FROM [FIRST | LAST] [RESPECT | IGNORE] NULLS OVER (analytic_clause) 

의 형태

 

NTH_VALUE returns the measure_expr value of the nth row in the window defined by the analytic_clause. The returned value has the data type of the measure_expr.

 

• [RESPECT | IGNORE] NULLS determines whether null values of measure_expr are included in or eliminated from the calculation. The default is RESPECT NULLS.
• n dertimines the nth fow for which the measure value is to be returned. n can be a constant, bind variable, column, or an expression involving them, as long as it resovles to a positive integer. The function returns NULL if the data source window has fewer than n rows. If n is null, then the function returns an error.
• FROM [FIRST | LAST] determines whether the calculation begins at the first or last row of the window. The default is from FIRST.

If you omit the windowing-clause of the analytic_clause, it defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This default sometimes returns an unexprected value for NTH_VALUE... FROM LAST... , 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 RNAGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Alternatively, you can specify the windowing_clause as RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.

 

 

 

오라클 SH 계정으로 테스트 가능

 

SQL>

SELECT
    prod_id,
    channel_id,
    MIN(amount_sold),
    NTH_VALUE(MIN(amount_sold), 2) OVER (PARTITION BY prod_id ORDER BY channel_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) nv
FROM sales
WHERE prod_id BETWEEN 14 AND 16
GROUP BY prod_id, channel_id ;