DECLARE
@mu float -- 蠏
, @sigma float -- 譴ｸ谿
, @min float -- 豕螳
, @max float -- 豕螳
, @munit float -- (煙 蟆曙 豕 1企.)
, @target_rate float
SET @mu = 85;
SET @sigma = 5;
SET @min = 0;
SET @max = 100;
SET @munit = 1
SET @target_rate = 5;
--SELECT (1/(SQRT(2*PI())*@sigma)) * EXP(-(1/(2*POWER(@sigma,2))) * POWER((@x-@mu), 2)) 襯覦
WITH Dumy(x)
AS
(
SELECT @min x
UNION ALL
SELECT x + @munit FROM Dumy
WHERE x + @munit <= @max
)
--蠏覿 朱 谿場覲伎..
SELECT
MAX(CASE WHEN Val <= (100 - @target_rate) * 0.01 THEN x END) MinVal
, MIN(CASE WHEN Val >= (100 - @target_rate) * 0.01 THEN x END) MaxVal
FROM (
SELECT
B.x
, SUM((1/(SQRT(2*PI())*@sigma)) * EXP(-(1/(2*POWER(@sigma,2))) * POWER((A.x-@mu), 2))) Val
FROM Dumy A INNER JOIN Dumy B
ON A.x <= B.x
GROUP BY B.x
) T
OPTION (MAXRECURSION 0);
/* 蟆郁骸
MinVal MaxVal
92 93
--92~93 襷朱 .
*/
/*
襴
豌 襯 90 覦螻, 螻朱 豌 蠏 85企.
譴ｸ谿 5企. 蟲企 85 覦朱, 蟲願骸覈 豌 蠏 70企.
蟲願骸覈 譴ｸ谿 10企. 企 螻朱 煙 一螳?
*/
WITH Dumy(x)
AS
(
SELECT 0 x
UNION ALL
SELECT x + 1 FROM Dumy
WHERE x + 1 <= 100.0
)
SELECT
螻朱
, (1 - SUM((1/(SQRT(2*PI())*譴ｸ谿)) * EXP(-(1/(2*POWER(譴ｸ谿,2))) * POWER((x-蠏), 2)))) * 100 [%]
FROM (
SELECT '' 螻朱, 90.0 , 85.0 蠏, 5.0 譴ｸ谿
UNION ALL
SELECT '蟲' 螻朱, 85.0 , 70.0 蠏, 10.0 譴ｸ谿) A CROSS JOIN Dumy
WHERE x <=
GROUP BY 螻朱
OPTION (MAXRECURSION 0);
/*
蟆郁骸
螻朱 %
------- ----------------
蟲 6.04932387024276
13.5265829069615
蟲企 6.04% 願, 13.52%企襦 蟲伎 煙 一.
*/
/*
DECLARE
@x float -- 覲
, @mu float -- 蠏
, @sigma float -- 譴ｸ谿
SET @x = 90
SET @mu = 85
SET @sigma = 5;
--SELECT (1/(SQRT(2*PI())*@sigma)) * EXP(-(1/(2*POWER(@sigma,2))) * POWER((@x-@mu), 2)) 襯覦
WITH Dumy(x)
AS
(
SELECT 0 x
UNION ALL
SELECT x + 1 FROM Dumy
WHERE x + 1 <= 90.0
)
--蠏覿
SELECT SUM((1/(SQRT(2*PI())*@sigma)) * EXP(-(1/(2*POWER(@sigma,2))) * POWER((x-@mu), 2)))
FROM Dumy
OPTION (MAXRECURSION 0);
*/