積の集計を行う

概要

積の集計の例として、ある金額から月ごとに利息を積み上げていった場合、1年で元の金額から何パーセント増えたのかを集計したい場合などがあります (あくまでも例ですので実際の運用、定義とは異なります)。この場合、月ごとのパーセンテージをかけ合わせていけば最終的に何パーセントになるかはわかリます。

しかし、SQL Server では合計を求める「SUM」や平均を求める「AVG」などの集計関数はありますが、積の合計を求める関数はありません。ここでは積の合計を求める集計方法について説明します。

動作環境

SQL Server
  • SQL Server 2005
  • SQL Server 2008
  • SQL Server 2008 R2
  • SQL Server 2012
  • SQL Server 2012 R2
  • SQL Server 2014

動作確認環境

SQL Server
  • SQL Server 2008
  • SQL Server 2012 R2

内容

サンプルデータ

単純に年月と利息だけを持つテーブル、データを定義します。

-- テーブル定義
declare @利息マスタ table 
(
  年月 nvarchar(6)
 ,利息 decimal(4, 2)
)

-- サンプルデータ挿入
insert @利息マスタ
          select N'201501', 1.05
union all select N'201502', 1.03
union all select N'201503', 1.02
union all select N'201504', 1.10
union all select N'201505', 1.02
union all select N'201506', 1.06
union all select N'201507', 1.07
union all select N'201508', 1.04
union all select N'201509', 1.06
union all select N'201510', 1.07
union all select N'201511', 1.03
union all select N'201512', 1.02

-- 確認
select * from @利息マスタ

積の集計

積の集計は「指数関数」と「対数関数」の集計で求めることができます。SQL は以下のようになります。

-- 集計
select exp(sum(log([利息]))) from @利息マスタ
-- 結果:1.73968081491318

これは「指数関数」「対数関数」の性質によって実現できる形になっています。

まず対数関数のサマリーは公式により「log x + log y = log(x * y) 」となります。指数関数の公式「elogx = x」により、対数関数の合計で求めた公式を当てはめると「elog(x*y) = x * y」となります。(x * y * ... の部分はデータの数になる)

ゼロと負の対応

対数関数には 0 以下を指定できないため、集計対象に 0 以下の数値が含まれるとエラーになってしまいます。0 以下の数値にも対応させるには SQL を以下のようにします。

-- 集計(ゼロ、負対応)
select
  case
    when min(abs([利息])) = 0 then 0
    else
       exp(sum(log(abs(nullif([利息], 0)))))
     * round(0.5 - count(nullif(sign(sign([利息]) + 0.5), 1)) % 2, 0)
  end as [利息合計]
from @利息マスタ
-- 結果:1.73968081491318