金額按分処理
SQL ServerはOracleに比べて分析関数の対応が少なく、実際にはROW_NUMBER()以外は用いることがほとんどない。それでも集計関数にもOVERが使えることを知っていると、ずいぶん楽になる。具体的には、金額配賦の計算があげられる。まとまったグループに対してきまった金額をその構成要素に割り振る計算で、実務上は避けて通れない。売上高按分だったり、面積按分だったり、ケースはさまざまだが、悩ましいのは金額だけに端数処理が必要になることだ。端数が出た場合は、一番配賦額の多いところで調整するのが一般的だからだ。(部署)支社 部署 売上高---------------------------東京 営業1部 100,123,520東京 営業2部 203,001,225東京 営業3部 92,254,210東京 営業4部 45,862,122東京 営業5部 145,225,656大阪 営業1部 85,255,456大阪 営業2部 54,225,781大阪 営業3部 23,248,892(費用)-----------------------------東京 人件費 250,000,000東京 賃料 23,500,000東京 光熱費 5,862,000大阪 人件費 130,000,000大阪 賃料 14,540,000大阪 光熱費 3,255,000発生した費用を各部署に売上高で按分するとした場合、SQL Server 2000では結構厄介だった。売上が同じだと二重に調整されてしまうから、ルールを決めて1部署だけを抜き出す必要があり、そのための連番を振る方法がSQL Server 2000ではIDENTITYしかなかった。○SQL Server 2000の場合DECLARE @配賦 TABLE([SEQ][int] IDENTITY,[支社][varchar](10),[費用][varchar](10), [金額][numeric](19,0),[部署][varchar](10),[配賦額][numeric](19,0))INSERT INTO @配賦(支社,費用,金額,部署,配賦額)SELECT b.支社,e.費用,e.金額,b.部署,ROUND(e.金額*b.売上高/t.支社売上高,0)FROM 部署 bINNER JOIN 費用 e ON e.支社=b.支社INNER JOIN (SELECT 支社,SUM(売上高) 支社売上高 FROM 部署 GROUP BY 支社) t ON t.支社=b.支社ORDER BY 1,2,5 DESCUPDATE @配賦SET 配賦額=配賦額-d.差額FROM @配賦 hINNER JOIN(SELECT 支社,費用,SUM(配賦額)-金額 差額 FROM @配賦 GROUP BY 支社,費用,金額) d ON d.支社=h.支社 AND d.費用=h.費用WHERE h.SEQ IN (SELECT MIN(SEQ) FROM @配賦 WHERE 支社=h.支社 AND 費用=h.費用) SELECT * FROM @配賦SQL Server 2005になって、ROW_NUMBERが使えるようになり、WITH句もサポートされたことで、テーブル変数がなくてもコンパクトに書けるようになった。○SQL Server 2005の場合SELECTROW_NUMBER() OVER (PARTITION BY b.支社,e.費用 ORDER BY b.売上高 DESC) SEQ,b.支社,e.費用,e.金額,b.部署,ROUND(e.金額*b.売上高/t.支社売上高,0) 配賦額FROM 部署 bINNER JOIN 費用 e ON e.支社=b.支社INNER JOIN (SELECT 支社,SUM(売上高) 支社売上高 FROM 部署 GROUP BY 支社) t ON t.支社=b.支社)SELECTh.支社,h.費用,h.金額,h.部署,h.配賦額-(CASE WHEN h.SEQ=1 THEN 差額 ELSE 0 END)FROM 配賦 hINNER JOIN(SELECT 支社,費用,SUM(配賦額)-金額 差額 FROM 配賦 GROUP BY 支社,費用,金額) d ON d.支社=h.支社 AND d.費用=h.費用これが集計関数のOVER句を利用すると純粋に1クエリで解決する。○SQL Server 2005の場合2SELECT支社,費用,金額,部署,配賦額-CASE WHEN SEQ=1 THEN SUM(配賦額) OVER (PARTITION BY 支社,費用)-金額 ELSE 0 ENDFROM(SELECTROW_NUMBER() OVER (PARTITION BY b.支社,e.費用 ORDER BY b.売上高 DESC) SEQ,b.支社,e.費用,e.金額,b.部署,ROUND(e.金額*b.売上高/SUM(b.売上高) OVER (PARTITION BY b.支社,e.費用),0) 配賦額FROM 部署 bINNER JOIN 費用 e ON e.支社=b.支社) tmp