SQL で累計計算を行う

Siden oppdatert :
ページ作成日 :

現在表示しているページは選択中の表示言語には対応しておりません。

概要

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

動作確認バージョン

対応 SQL Server バージョン

  • 2005
  • 2008
  • 2008 R2

確認 SQL Server バージョン

  • 2005
  • 2008
  • 2008 R2

動作確認 Windows バージョン

  • 2008
  • 2008 R2

内容

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

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

年月 金額 金額累計 年度単位金額累計
201004 100 null null
201005 200 null null
201006 150 null null
201007 300 null null
201008 50 null null
201009 220 null null
201010 240 null null
201011 280 null null
201012 400 null null
201101 450 null null
201102 520 null null
201103 120 null null
201104 140 null null
201105 450 null null
201106 270 null null
201107 640 null null
201108 340 null null
201109 230 null null
201110 280 null null
201111 320 null null
201112 350 null null
201201 230 null null
201202 270 null null
201203 350 null null

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 を実行すると「金額累計」に累計値が入ります。

年月 金額 金額累計 年度単位金額累計
201004 100 100 null
201005 200 300 null
201006 150 450 null
201007 300 750 null
201008 50 800 null
201009 220 1020 null
201010 240 1260 null
201011 280 1540 null
201012 400 1940 null
201101 450 2390 null
201102 520 2910 null
201103 120 3030 null
201104 140 3170 null
201105 450 3620 null
201106 270 3890 null
201107 640 4530 null
201108 340 4870 null
201109 230 5100 null
201110 280 5380 null
201111 320 5700 null
201112 350 6050 null
201201 230 6280 null
201202 270 6550 null
201203 350 6900 null

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

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 を実行すると「年度単位金額累計」に累計値が入ります。

年月 金額 金額累計 年度単位金額累計
201004 100 100 100
201005 200 300 300
201006 150 450 450
201007 300 750 750
201008 50 800 800
201009 220 1020 1020
201010 240 1260 1260
201011 280 1540 1540
201012 400 1940 1940
201101 450 2390 2390
201102 520 2910 2910
201103 120 3030 3030
201104 140 3170 140
201105 450 3620 590
201106 270 3890 860
201107 640 4530 1500
201108 340 4870 1840
201109 230 5100 2070
201110 280 5380 2350
201111 320 5700 2670
201112 350 6050 3020
201201 230 6280 3250
201202 270 6550 3520
201203 350 6900 3870