對一系列數字進行四舍五入，以獲得盡可能多的"圓形"數字，并將其相加為100。

結果令人滿意，我甚至為常見的分數整合了一些特殊的數值（200/3、100/3、100/6、100/12......），這樣3x33.33就不會出現33.35、33.35和33.30，不那么 "漂亮/圓"，但更公平。請注意，只有一個回圈用于最后的調整。性能是可以接受的：100000行的3.5秒。

下面的SO 問題和所包含的Wikipedia 文章幫助我了解可能的偏差及其利弊。

以下是代碼，供感興趣的人參考：

CREATE OR REPLACE FUNCTION public. normalizeshares_weight(share INT) RETURNS INT AS >。 $BODY$ SELECT CASE BODY$ WHEN share % 10000 = 0 THEN 40 WHEN share % 1000 = 0 THEN 30 WHEN share % 100 = 0 THEN 20 WHEN share % 50 =0 OR (share = any('{6666, 3333,1666,833,416}')) THEN 15) WHEN (share % 10 = 0) THEN10 WHEN share % 5 = 0 THEN 5 ELSE 0 END; $BODY$ LANGUAGE SQL IMMUTABLE; CREATE OR REPLACE FUNCTION public. normalizeshares(shares NUMERIC[]) RETURNS NUMERIC(5, 2）[] AS $BODY$ DECLARE[/span intshares INT[]。 adjshares INT[]。 重量 INT[]。 result NUMERIC[]; nb0 INT = 0; nb5 INT = 0; nb10 INT = 0; nb15 INT = 0; nb20 INT = 0; nb30 INT = 0; nb40 INT = 0; initot INT = 0; tot INT = 0; nb INT = 0; w INT = 0; diff INT; each INT; 最佳重量INT。 BEGIN FOR i IN 1..ARRAY_LENGTH( shares,1) LOOP intshares[i] : = FLOOR(COALESCE( shares[i], 0)*100）。) weight[i] := normalizeshares_weight(intshares[i])。 bestweight := weight[i]; adjshares[i] := intshares[i]; 如果normalizeshares_weight(intshares[i] 1) > bestweight THEN adjshares[i] := intshares[i] 1; bestweight : = normalizeshares_weight(intshares[i] 1); END IF; IF normalizeshares_weight(intshares[i] 2) > bestweight THEN adjshares[i] := intshares[i] 2; bestweight : = normalizeshares_weight(intshares[i] 2); END IF; IF normalizeshares_weight(intshares[i] 3) > bestweight THEN adjshares[i] := intshares[i] 2; bestweight : = normalizeshares_weight(intshares[i] 3); END IF; 如果normalizeshares_weight(intshares[i]-1) >/span> bestweight THEN adjshares[i] : = intshares[i]-1; bestweight := normalizeshares_weight(intshares[i]-1) ; END IF; 如果normalizeshares_weight(intshares[i]-2) >/span> bestweight THEN adjshares[i] : = intshares[i]-2; bestweight := normalizeshares_weight(intshares[i]-2); END if; 如果normalizeshares_weight(intshares[i]-3) >/span> bestweight THEN adjshares[i] : = intshares[i]-2; bestweight := normalizeshares_weight（intshares[i]-3）; END IF; tot := tot adjshares[i]; initot := initot intshares[i]; weight[i] := bestweight; -- normalizeshares_weight(addshares[i]); 已計算。 IF weight[i]=0 THEN nb0 : = nb0 1; ELSIF weight[i]=5 THEN nb5 : = nb5 1; ELSIF weight[i]=10 THEN nb10 : = nb10 1; ELSIF weight[i]=15 THEN nb15 : = nb15 1; ELSIF weight[i]=20 THEN nb20 : = nb20 1; ELSIF weight[i]=30 THEN nb30 : = nb30 1; ELSIF weight[i]=40 THEN nb40 : = nb40 1; END IF; result[i] :=（intshares[i]::NUMERIC / 100）: :NUMERIC(5,2) 。 END LOOP; IF tot=10000 THEN --RAISE NOTICE 'adjtot=100.00 : %',adjshares:: numeric[]; for i in 1..ARRAY_LENGTH(shares,1) LOOP result[i] :=（addshares[i]::NUMERIC / 100）。 :NUMERIC(5,2) 。 END LOOP。 ELSIF (initot=10000) OR (ABS(10000-tot)>。 90) THEN> -- RAISE NOTICE 'No adj needed, initot=%, tot=%',initot,tot; ELSE IF nb0 > 0 THEN nb : = nb0; w := 0; ELSIF nb5 > 0 THEN nb : = nb5; w := 5; ELSIF nb10 > 0 THEN nb : = nb10; w := 10; ELSIF nb15 > 0 THEN nb : = nb15; w := 15; ELSIF nb20 > 0 THEN nb : = nb20; w := 20; ELSIF nb30 > 0 THEN nb : = nb30; w := 30; ELSIF nb40 > 0 THEN nb : = nb40; w := 40; END IF; diff := 10000 - tot; each : = diff/nb diff/abs（diff）; -- RAISE NOTICE 'nb=%, w=%, diff=%, tot=%, adj=%',nb,w,diff,tot,addshares:: numeric[]; for i in 1..ARRAY_LENGTH(shares,1) LOOP IF weight[i]=w THEN. IF diff=0THEN ELSIF nb=1THEN adjshares[i] := adjshares[i] diff。 ELSIF nb>1 THEN adjshares[i] := adjshares[i] each; diff := diff - each; END IF; nb := nb -1。 END IF; result[i] :=（addshares[i]::NUMERIC / 100）: :NUMERIC(5,2) 。 END LOOP; END IF; RETURN result; END; $BODY$ LANGUAGE plpgsql IMMUTABLE;

還有一些結果 :

% select normalizeshares('{49.99,9.99,40.01}'/span>) 。
   正常化股票   
---------------------
 {50.00,10.00, 40.00}。

% select normalizeshares('{33.33,16.66,8.33,8.33,9.99,12.5,10.83}'/span>) 。
              正常化股票              
-------------------------------------------
 {33.33,16.66,8.33, 8. 33,10.00,12.50,10.85}。

標籤：

上一篇：在一個陣列的范圍內找到最大和最小元素之間的差異

下一篇：MySqlDocker容器拒絕連接到MicrosoftAzure（Mac）。