Aggregating products

Sample Data

Simply define a table, data with only years, months and interest.

-- テーブル定義
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 @利息マスタ

Aggregating Products

The aggregation of the product can be obtained by aggregating "exponential function" and "logarithmic function". SQL looks like this:

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

This is a form that can be realized by the nature of "exponential function" and "logarithmic function".

First, the summary of the logarithmic function is "log x + log y = log(x * y)" by the formula. The exponential formula "e ^logx = x**" gives "e log(x*y) =^{x * y}" by applying the formula obtained by the sum of the logarithmic functions. (x * y * ... (The part of is the number of data)

Zero and negative correspondence

Because a logarithmic function cannot be less than or equal to 0, an error occurs if the aggregation target contains a number less than or equal to 0. To accommodate numbers below 0, use SQL as follows:

-- 集計(ゼロ、負対応)
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