7-51 丸め誤差分の調整

SQLパズル

品物テーブル
コード  金額
------  ----
     A   400
     B   100
     C   100

コードごとの、総合計に対する比率を、
小数点第三位で四捨五入して出力する。

ただし、比率の総合計が100にならない場合は、
比率の最大値(複数存在したら任意の一つ)を調整して、
比率の総合計が100になるようにする。

出力結果
コード  金額   総合計  調整前の比率   調整後の比率
------  ----  ------  ------------  ------------
     A   400    600          66.67         66.66
     B   100    600          16.67         16.67
     C   100    600          16.67         16.67

データ作成スクリプト

create table 品物(
コード char(1),
金額   number(4));

insert into 品物 values('A',400);
insert into 品物 values('B',100);
insert into 品物 values('C',100);
commit;

SQL

col コード for a6

select コード,金額,総合計,比率 as 調整前の比率,
decode(Row_ID,調整対象RowID,比率+丸め誤差,比率) as 調整後の比率
from (select コード,Row_ID,金額,総合計,比率,
      Last_Value(Row_ID) over(order by 金額
      Rows between Unbounded Preceding and Unbounded Following) as 調整対象RowID,
      100-sum(比率) over() as 丸め誤差
      from (select コード,金額,RowID as Row_ID,
            sum(金額) over() as 総合計,
            round(Ratio_To_Report(金額) over() * 100,2) as 比率
            from 品物))
order by 総合計,コード;

解説

Last_Value関数で調整対象となるレコードのRowIDを取得してます。

---