SQL で累計計算を行う

概要

年月単位での金額の累計値を SQL を使って計算しています。

動作確認バージョン

対応 SQL Server バージョン

• 2005
• 2008
• 2008 R2

確認 SQL Server バージョン

• 2005
• 2008
• 2008 R2

動作確認 Windows バージョン

• 2008
• 2008 R2

内容

通常データベースを使っている場合指定した年月に対して金額の計算を行うことが多いですが、金額や在庫など前月のデータをもとに今月の金額を加算していくこともあります。ストアドやプログラムで累計値を計算することもできますが、ここでは SQL のみで計算を行いたいと思います。

以下のテーブルがあり、年月ごとに金額が設定されています。

SQL で一括更新するには「前月の累計金額」に「今月の金額」たすという方法ではなく、対象年月から過去の金額を集計するという条件をつけることによって累計値を計算することができます。

update 金額集計Test
  set 金額累計 = 累計
from
(
  select
    A.年月,
    sum(B.金額) as 累計
  from 金額集計Test A
  left outer join 金額集計Test B
    on B.年月 <= A.年月
  group by A.年月
) as C
where 金額集計Test.年月 = C.年月

上記の SQL を実行すると「金額累計」に累計値が入ります。

また、年度ごとに累計値をリセットしたい場合などは条件式を変えることによって計算することができます。

update 金額集計Test
  set 年度単位金額累計 = 累計
from
(
select
  A.年月,
  sum(B.金額) as 累計
from 金額集計Test A
left outer join 金額集計Test B
  on B.年月 <= A.年月
  and B.年月 >= case
                  when cast(right(A.年月, 2) as int) >= 4 then left(A.年月, 4) + N'04'
                  else cast(cast(left(A.年月, 4) as int) - 1 as nvarchar(4)) + N'04'
                end
group by A.年月
) as C
where 金額集計Test.年月 = C.年月

上記の SQL を実行すると「年度単位金額累計」に累計値が入ります。