りーんが思う。あれそれ。　 [Oracle] 按分比率計算結果更新

Ads by Google

--.--.--(--) --:--

上記の広告は１ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

[Oracle] 按分比率計算結果更新

2008.02.06(水) 03:36

■したい事
　・按分比率計算結果を更新

■条件
　・按分比率の合計値が1になるための差分を求め、
　　按分比率の最大値から0.000000001づつ、加減算する

A	B	C	D
------------------------
A	a1	61.5	(NULL)
A	a2	32.0	(NULL)
A	a3	23.0	(NULL)
B	b1	122.0	(NULL)
B	b2	11.5	(NULL)
B	b3	9.5	(NULL)
C	c1	0.3	(NULL)
C	c2	0.3	(NULL)
C	c3	0.3	(NULL)

UPDATE HOGE A SET
    A.D = ( WITH
            T1 AS ( SELECT
                        ROWID AS R1,
                        A     AS V1,
                        B     AS V2,
                        C     AS V3
                    FROM HOGE
                  ),
            T2 AS ( SELECT
                        T1.V1      AS V1,
                        SUM(T1.V3) AS V2
                     FROM T1
                    GROUP BY T1.V1
                  ),
            T3 AS ( SELECT
                        T1.R1                   AS R1,
                        T1.V1                   AS V1,
                        T1.V2                   AS V2,
                        ROUND(T1.V3 / T2.V2, 9) AS V3
                      FROM T1
                           INNER JOIN T2 ON T1.V1 = T2.V1
                  ),
            T4 AS ( SELECT
                        T3.V1          AS V1,
                        1 - SUM(T3.V3) AS V2
                      FROM T3
                    GROUP BY T3.V1
                  ),
            T5 AS ( SELECT
                        T3.R1                       AS R1,
                        T3.V1                       AS V1,
                        T3.V2                       AS V2,
                        T3.V3                       AS V3,
                        '0.000000001' * SIGN(T4.V2) AS V4,
                        ABS(T4.V2) AS V5
                      FROM T3
                           INNER JOIN T4 ON T3.V1 = T4.V1
                  ),
            T6 AS ( SELECT
                        T5.R1 AS R1,
                        T5.V3 AS V1,
                        T5.V4 AS V2,
                        T5.V5 AS V3,
                        SUM(ABS(T5.V4)) OVER(PARTITION BY T5.V1
                                                 ORDER BY T5.V1,
                                                          T5.V3 DESC,
                                                          T5.V2
                                            ) AS V4
                      FROM T5
                   ),
            T7 AS  ( SELECT
                        T6.R1 AS R1,
                        CASE WHEN T6.V4 > T6.V3 THEN
                                  T6.V1
                             ELSE
                                  T6.V1 +  T6.V2
                        END AS V1
                       FROM T6
                     )
            SELECT V1 FROM T7
             WHERE A.ROWID = T7.R1
          )

■更新イメージ

R1	V1	V2	V3
------------------------
AAAA	A	a1	61.5
AAAB	A	a2	32.0
AAAC	A	a3	23.0
AAAD	B	b1	122.0
AAAE	B	b2	11.5
AAAF	B	b3	9.5
AAAG	C	c1	0.3
AAAH	C	c2	0.3
AAAI	C	c3	0.3

・ROWIDと必要な列を抽出

V1	V2
------------
A	116.5
B	143.0
C	0.9

・総合計の算出

R1	V1	V2	V3
--------------------------------
AAAC	A	a3	0.197424893
AAAB	A	a2	0.274678112
AAAA	A	a1	0.527896996
AAAF	B	b3	0.066433566
AAAE	B	b2	0.080419580
AAAD	B	b1	0.853146853
AAAI	C	c3	0.333333333
AAAH	C	c2	0.333333333
AAAG	C	c1	0.333333333

・各値を総合計値で除算、小数点第10桁で四捨五入

V1	V2
--------------------
A	-0.000000001
B	0.000000001
C	0.000000001

・除算結果を合計し、合計が１になるための差分を算出

R1	V1	V2	V3	V4	V5
----------------------------------------------------------------
AAAC	A	a3	0.197424893	-0.000000001	0.000000001
AAAB	A	a2	0.274678112	-0.000000001	0.000000001
AAAA	A	a1	0.527896996	-0.000000001	0.000000001
AAAF	B	b3	0.066433566	0.000000001	0.000000001
AAAE	B	b2	0.080419580	0.000000001	0.000000001
AAAD	B	b1	0.853146853	0.000000001	0.000000001
AAAI	C	c3	0.333333333	0.000000001	0.000000001
AAAH	C	c2	0.333333333	0.000000001	0.000000001
AAAG	C	c1	0.333333333	0.000000001	0.000000001

・差分の絶対値を算出

R1	V1	V2	V3	V4
------------------------------------------------------------------------
AAAA	0.527896996	-0.000000001	0.000000001	0.000000001
AAAB	0.274678112	-0.000000001	0.000000001	0.000000002
AAAC	0.197424893	-0.000000001	0.000000001	0.000000003
AAAD	0.853146853	0.000000001	0.000000001	0.000000001
AAAE	0.080419580	0.000000001	0.000000001	0.000000002
AAAF	0.066433566	0.000000001	0.000000001	0.000000003
AAAG	0.333333333	0.000000001	0.000000001	0.000000001
AAAH	0.333333333	0.000000001	0.000000001	0.000000002
AAAI	0.333333333	0.000000001	0.000000001	0.000000003

・HOGA.A毎の絶対値加減算用数値を前レコードから足していく

R1	V1
------------------------
AAAA	0.527896995
AAAB	0.274678112
AAAC	0.197424893
AAAD	0.853146854
AAAE	0.080419580
AAAF	0.066433566
AAAG	0.333333334
AAAH	0.333333333
AAAI	0.333333333

・按分比率の最大値から加減算する

■実行結果

A	B	C	D
----------------------------
A	a1	61.5	0.527896995
A	a2	32.0	0.274678112
A	a3	23.0	0.197424893
B	b1	122.0	0.853146854
B	b2	11.5	0.080419580
B	b3	9.5	0.066433566
C	c1	0.3	0.333333334
C	c2	0.3	0.333333333
C	c3	0.3	0.333333333

VIEW UPDATE を使いたかった... orz

Page Top ▲

コメントの投稿

トラックバック

この記事にトラックバックする(FC2ブログユーザー)

Page Top ▲

日	月	火	水	木	金	土
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30	-	-	-	-	-