構文
用途
LAST_VALUE
はデータの稠密化に役立つ分析ファンクションです。これは、順序付けられた値の集合にある最後の値を戻します。
注意: この構文の2つの書式は、同じ動作になります。上のブランチはANSI書式です。こちらを使用することをお薦めします。下のブランチは非推奨ですが、下位互換性を保つためにサポートされています。 |
{RESPECT
| IGNORE
} NULLS
は、expr
のNULL値を計算に含めるか除外するかを指定します。デフォルトはRESPECT
NULLS
です。集合内の最後の値がNULLの場合、IGNORE
NULLS
を指定していないかぎり、ファンクションはNULL
を戻します。IGNORE NULLS
を指定すると、LAST_VALUE
は集合内の最後のNULLではない値を戻します。すべての値がNULLの場合はNULL
を戻します。データの稠密化の例は、「パーティション化された外部結合の使用例:」を参照してください。
expr
には、LAST_VALUE
または他の分析ファンクションを使用して分析ファンクションをネストできません。ただし、他の組込みファンクション式をexpr
で使用できます。expr
の書式の詳細は、「SQL式」を参照してください。
analytic_clause
のwindowing_clause
を省略した場合、デフォルトでRANGE
BETWEEN
UNBOUNDED
PRECEDING
AND
CURRENT
ROW
になります。ウィンドウの最後の値はウィンドウの終端にあり、固定されていないため、このデフォルトは予期しない値を戻す場合があります。これは、現行の行が変化するに伴って変化します。正しい結果を得るには、windowing_clause
をRANGE
BETWEEN
UNBOUNDED
PRECEDING
AND
UNBOUNDED
FOLLOWING
として指定します。または、windowing_clause
をRANGE
BETWEEN
CURRENT
ROW
AND
UNBOUNDED
FOLLOWING
として指定することもできます。
例
次の例では、給与が一番高い従業員の雇用開始日を各行に戻します。
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); LAST_NAME SALARY HIRE_DATE LV --------------- ---------- --------- --------- De Haan 17000 13-JAN-01 17-JUN-03 Kochhar 17000 21-SEP-05 17-JUN-03 King 24000 17-JUN-03 17-JUN-03
この例では、LAST_VALUE
ファンクションの非決定的な性質を示しています。KochharとDe Haanの給与は同じであるため、Kochharの次の行にDe Haanがあります。Kochharが最初に表示されているのは、副問合せの行がhire_date
で順序付けられているためです。ただし、行がhire_date
で降順に順序付けられている場合は、次の例に示すとおり、ファンクションは異なる値を戻します。
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 DESC);
LAST_NAME SALARY HIRE_DATE LV
--------------- ---------- --------- ---------
Kochhar 17000 21-SEP-05 17-JUN-03
De Haan 17000 13-JAN-01 17-JUN-03
King 24000 17-JUN-03 17-JUN-03
次の2つの例では、一意キーで順序付けることによって、LAST_VALUE
ファンクションを決定的にする方法を示しています。salary
およびhire_date
でファンクション内を順序付けると、副問合せの順序付けにかかわらず、同じ結果が戻されます。
SELECT last_name, salary, hire_date, LAST_VALUE(hire_date) OVER (ORDER BY salary, hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lv FROM (SELECT * FROM employees WHERE department_id = 90 ORDER BY hire_date) ORDER BY last_name, salary, hire_date; LAST_NAME SALARY HIRE_DATE LV --------------- ---------- --------- --------- De Haan 17000 13-JAN-01 17-JUN-03 King 24000 17-JUN-03 17-JUN-03 Kochhar 17000 21-SEP-05 17-JUN-03 SELECT last_name, salary, hire_date, LAST_VALUE(hire_date) OVER (ORDER BY salary, hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lv FROM (SELECT * FROM employees WHERE department_id = 90 ORDER BY hire_date DESC) ORDER BY last_name, salary, hire_date; LAST_NAME SALARY HIRE_DATE LV --------------- ---------- --------- --------- De Haan 17000 13-JAN-01 17-JUN-03 King 24000 17-JUN-03 17-JUN-03 Kochhar 17000 21-SEP-05 17-JUN-03
論理オフセット(ROWS
のかわりにRANGE
)を使用すると、ファンクションは決定的になります。ORDER
BY
式に重複値がある場合は、LAST_VALUE
がexpr
の最大値になります。
SELECT last_name, salary, hire_date, LAST_VALUE(hire_date) OVER (ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lv FROM (SELECT * FROM employees WHERE department_id = 90 ORDER BY hire_date DESC); LAST_NAME SALARY HIRE_DATE LV ------------------------- ---------- --------- --------- De Haan 17000 13-JAN-01 17-JUN-03 Kochhar 17000 21-SEP-05 17-JUN-03 King 24000 17-JUN-03 17-JUN-03