SQLでユーザ関数を活用 - 複雑なSQLをデチューンしてみよう

　元のSQLを今回は、OLAP関数が使えないという条件でデチューンしてみます。ソースコードはSQLServerで記述しています。

まずはベースになるSQLを考える。

　元のSQLが複雑と感じるか簡単と感じるかは人それぞれですが、ベースの部分は極めて単純です。重要な部分は最終欲しいデータ数が返ってくるSQL文（FROM句、WHERE句、GROUP BY句、HAVING句まで）がベースの部分になります。

SELECT 
　　 uh.請求書NO, uh.納品書NO, um.行NO, uh.売上日, uh.得意先ID
　　 , um.商品ID, p.商品区分, um.単価, um.数量
　　 , fn丸め(um.単価 * um.数量 * p.消費税率, t.丸め方法) /* + 誤差配賦額 */
 FROM
　　 売上 uh
　　 INNER JOIN 売上明細 um
　　　　 ON uh.売上ID = um.売上ID
　　 INNER JOIN 得意先 t
　　　　 ON uh.得意先ID = t.得意先ID
　　 INNER JOIN 商品 p
　　　　 ON um.商品ID = p.商品ID
 WHERE
　　 uh.売上日 BETWEEN @pFrom AND @pTo;

　後は、明細毎の誤差配賦額（または配賦後の消費税額）が分かりさえすれば良いわけです。つまり、欲しい情報はスカラー値ですから、スカラー値関数を作ればよい。と判断できますでしょうか。

　ここでもインターフェースとなる戻り値とパラメータをまず考えます。では、明細毎の誤差配賦額を計算するには何が分かれば計算できるでしょう。

　要件をよく読めば、以下の情報があれば計算できると分かるはずです。

　　・明細を特定するため
　　　　納品書NO
　　　　行NO

　　・消費税額計算のため
　　　　単価
　　　　数量
　　　　消費税率

　　・丸め計算の条件
　　　　丸め方法
　　　　t.丸め単位

　　・納品書単位の消費税計算のため
　　　　納品書NO

　　・請求書単位の消費税計算のため
　　　　請求書NO

　一部、重複していますが、すべてのパラメータはベースのSQLのSELECT句に含まれていますから、これらをパラメータにした、スカラー値関数を作ればよいわけです。パラメータを整理しスカラー値関数（fn誤差配賦消費税額）完成したとすると、ダミーのプロシージャは以下のようになります。実に読みやすいと思います。

CREATE PROCEDURE TEST_PROC
	(
	@pFrom datetime	-- 期間開始日
	, @pTo datetime	-- 期間終了日
)
AS

	SET NOCOUNT ON;

	SELECT 
		uh.請求書NO, uh.納品書NO, um.行NO, uh.売上日, uh.得意先ID
		, um.商品ID, p.商品区分, um.単価, um.数量
		, dbo.fn誤差配賦消費税額(
			uh.請求書NO, uh.納品書NO, um.行NO, t.丸め単位, t.丸め方法
			, um.単価, um.数量, p.消費税率) AS 消費税額
	FROM
		売上 uh
		INNER JOIN 売上明細 um
			ON uh.売上ID = um.売上ID
		INNER JOIN 得意先 t
			ON uh.得意先ID = t.得意先ID
		INNER JOIN 商品 p
			ON um.商品ID = p.商品ID
	WHERE
		uh.売上日 BETWEEN @pFrom AND @pTo;
GO

　肝心のスカラー値関数（fn誤差配賦消費税額）ですが、今回はOLAP関数が使えない条件ということで、かなり面倒なSQLになっています。以下の通りです。

CREATE FUNCTION [dbo].[fn誤差配賦消費税額]
(@請求書NO      int
	, @納品書NO int
	, @行NO     int
	, @丸め単位 int
	, @丸め方法 int
	, @単価     int
	, @数量     int
	, @消費税率 money
	)
RETURNS int 
AS
BEGIN

	DECLARE @retValue int;
	DECLARE @明細単位消費税額 int;
	DECLARE @伝票消費税額 int;
	DECLARE @差額 int;
	DECLARE @配賦額 int;
	
	-- @丸め単位 = ２，３以外は明細単位と見なす。
	SET @retValue = dbo.fn丸め(@単価 * @数量 * @消費税率, @丸め方法);

	IF  @丸め単位 = 2
	  BEGIN
		-- 納品書単位の消費税を計算する。
		SELECT 
			@明細単位消費税額 = SUM(dbo.fn丸め(um.単価 * um.数量 * @消費税率, @丸め方法))
			, @伝票消費税額 = dbo.fn丸め(SUM(um.単価 * um.数量) * @消費税率, @丸め方法)
		FROM 
			売上 uh
			INNER JOIN 売上明細 um
				ON uh.売上ID = um.売上ID
		WHERE 
			uh.納品書NO = @納品書NO;
		
		SET @差額 = @伝票消費税額 - @明細単位消費税額;
		
		-- 差額がないときは、明細単位の消費税額を返す。
		IF @差額 = 0
		  BEGIN 
			RETURN @retValue;
		END; 

		-- 差額があるときは、明細の小計から、対象の明細より補正対象か確認する。
		SELECT 
			@配賦額 = SIGN(@差額)
		FROM 
			売上 uh
			INNER JOIN 売上明細 um
				ON uh.売上ID = um.売上ID
		WHERE 
			uh.納品書NO = @納品書NO
			AND (um.単価 * um.数量 > @単価 * @数量
			OR (um.単価 * um.数量 = @単価 * @数量 AND um.行NO <= @行NO))
		HAVING COUNT(1) <= ABS(@差額);
		
		-- レコードがないときはNULLが返る。
		SET @retValue = @retValue + ISNULL(@配賦額, 0);
		
	END;
	ELSE IF  @丸め単位 = 3
	  BEGIN
		-- 請求書単位の消費税を計算する。
		SELECT 
			@明細単位消費税額 = SUM(dbo.fn丸め(um.単価 * um.数量 * @消費税率, @丸め方法))
			, @伝票消費税額 = dbo.fn丸め(SUM(um.単価 * um.数量) * @消費税率, @丸め方法)
		FROM 
			売上 uh
			INNER JOIN 売上明細 um
				ON uh.売上ID = um.売上ID
		WHERE 
			uh.請求書NO = @請求書NO;
		
		SET @差額 = @伝票消費税額 - @明細単位消費税額;
		
		IF @差額 = 0
		  BEGIN 
			RETURN @retValue;
		END; 
		
		-- 差額があるときは、明細の小計から、対象の明細より補正対象か確認する。
		SELECT 
			@配賦額 = SIGN(@差額)
		FROM 
			売上 uh
			INNER JOIN 売上明細 um
				ON uh.売上ID = um.売上ID
		WHERE 
			uh.請求書NO = @請求書NO
			AND (um.単価 * um.数量 > @単価 * @数量
			OR (um.単価 * um.数量 = @単価 * @数量 AND uh.納品書NO < @納品書NO) 
			OR (um.単価 * um.数量 = @単価 * @数量 AND uh.納品書NO = @納品書NO AND um.行NO <= @行NO))
		HAVING COUNT(1) <= ABS(@差額);

		-- レコードがないときはNULLが返る。
		SET @retValue = @retValue + ISNULL(@配賦額, 0);
		
	END;
	
	RETURN @retValue;

END
GO