sql oracle 引当処理 分析関数

参考文献

7-54 引き当て処理  
First_ValueとLast_Valueとnth_Value  

テストデータの準備

参考文献のfirst_valueの使い方が素敵すぎた。。すごいなー。

別名 日本語
rn 処理順
fpiq 初回引当指示数
sq 在庫数
rsq 引当済数
esq 引当可能在庫数
cpiq 今回引当繰越数
fpsq 引落後在庫数
dpsq 引当済在庫数
コード表示

WITH sub AS(
SELECT
	LEVEL AS rn
	,:piq AS piq
	, LEVEL * 10 AS sq
	, LEVEL * 2 AS rsq
	, LEVEL * 10 - LEVEL * 2 AS esq
	, SUM(LEVEL * 10 - LEVEL * 2) OVER( ORDER BY LEVEL ) AS cume_esq
FROM
	dual
CONNECT BY
	LEVEL <= 10
)
SELECT
	rn
	, piq
	, sq
	, rsq
	, esq
	, cume_esq
	, nvl(FIRST_VALUE(CASE WHEN cume_esq > piq THEN rn END IGNORE NULLS) OVER (ORDER BY rn),rn) AS tarrn
	, CASE
		WHEN cume_esq <= piq THEN ABS(cume_esq - piq)
		ELSE 0
	END AS cpiq
	, CASE
		WHEN cume_esq <= piq THEN 0
		WHEN rn = nvl(FIRST_VALUE(CASE WHEN cume_esq > piq THEN rn END IGNORE NULLS) OVER (ORDER BY rn),rn) THEN cume_esq - piq
		ELSE esq
	END AS fpsq
	, CASE
		WHEN cume_esq <= piq THEN esq
		WHEN rn = nvl(FIRST_VALUE(CASE WHEN cume_esq > piq THEN rn END IGNORE NULLS) OVER (ORDER BY rn),rn) THEN piq - LAG(cume_esq) OVER (ORDER BY rn) 
		ELSE 0
	END AS dpsq
FROM
	sub
;

piqに100を入れた場合