Aggregate the product
Sample data
Simply define the data, a table with only years 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 @利息マスタ
Summary of the product
The sum of the product can be calculated by the aggregate of "exponential function" and "logarithmic function". SQL looks like this:
-- 集計
select exp(sum(log([利息]))) from @利息マスタ
-- 結果:1.73968081491318
This can be achieved by the nature of exponential functions and logarithmic functions.
First, the log function summary is officially"log x + log y = log(x *y)". The formula of the exponential function,elogx = x**, is"elog(x*y) = x * y"when the formula determined by the sum of the logarithmic functions is applied. (x* y * ... will be the number of data)
Zero and negative responses
Because logarithmic functions cannot be less than or equal to 0, an error occurs if the aggregate disaggregated includes a number less than or equal to 0. To accommodate a number less than or equal to 0, follow 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