| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Query Quandry.
On 13 nov, 13:45, "Preston" <dontwant..._at_nowhere.invalid> wrote:
> Anyone got a neat solution for this in 10.2.0.1? There's a table which
> looks something like this:
>
> hid t1 t2 t3 t4 mpc id
> 51 1 10 1
> 51 6 20 2
> 51 4 70 3
> 53 1 34 3 4
> 53 1 1 22 5
> 53 6 34 20 6
> 53 4 34 35 7
> 53 4 841 20 8
>
> It's the result of merging two sets of data (hid 51 & hid 53). The t1 -
> t4 columns are numbers which can be null, & the mpc column is a
> percentage.
>
> The basic rules for the data are that hid53 rows will always have more
> of the 't' columns populated than the hid51 rows. So the hid51 rows
> could have data in t1, t2 & t3, in which case the hid53 rows will have
> data in all 4 of the 't' columns. Alternatively the hid51 may just have
> data in t1, & the hid53 could have data in t1 & t2, or t1, t2 & t3, or
> t1, t2, t3 & t4.
>
> Whatever the combination, if a particular hid(n)/t(n) field has a
> value, all the other fields for that hid(n)/t(n) will also have values.
> I.e. if there was a number in one of the hid53 t3 fields in the example
> above, every hid53 row would have a value in the t3 column.
>
> Also, any numbers in any of the hid51 rows will be repeated in the same
> column in at least one hid53 row. So the fact that there's a 1, 6 & 4
> in the hid51 t1 column means there must also be a 1, 6 & 4 in the t1
> column with a hid of 53. Similarly if there was a hid51 row with a t1
> of 1 & a t2 of 8, there would also be at least one hid53 row with a t1
> of 1 & a t2 of 8.
>
> If you've followed this so far, congratulations - I'm impressed.
>
> The result I need to get (which I'll stick in an array of pl/sql
> table), assuming the above example, is:
>
> hid t1 t2 t3 t4 mpc
> 53 1 34 1.2
> 53 1 1 8.8
> 53 6 34 20
> 53 4 34 44.55
> 53 4 841 25.45
>
> So basically it's combining the hid51 & hid53 rows together. However
> the percentage (the mpc column) needs to be recalculated to retain the
> same allocation found in the hid51 rows. So the row at id 1 in the
> example gets merged with the rows at id 4 & 5, as the value in the t1
> column is the same. However the mpc for row 1 is 10, so the two mpc
> values for rows 4 & 5 need to be re-apportioned to total 10, i.e.
> (3/(3+22))*10=1.2 for row 4, & (22/(3+22))*10=8.8 for row 5.
>
> I'm sure there must be a way to do this using analytics, but I've yet
> to delve into those, & unlikely to have the time to do so anytime soon.
>
> --
> Preston.
SQL> create table mytable (hid,t1,t2,t3,t4,mpc,id) 2 as
3 select 51, 1, null, cast(null as number(3)), cast(null as number(3)), 10, 1 from dual union all 4 select 51, 6, null, null, null, 20, 2 from dual union all 5 select 51, 4, null, null, null, 70, 3 from dual union all 6 select 53, 1, 34, null, null, 3, 4 from dual union all 7 select 53, 1, 1, null, null, 22, 5 from dual union all 8 select 53, 6, 34, null, null, 20, 6 from dual union all 9 select 53, 4, 34, null, null, 35, 7 from dual union all 10 select 53, 4, 841, null, null, 20, 8 from dual11 /
Tabel is aangemaakt.
SQL> select hid
2 , t1 3 , t2 4 , t3 5 , t4 6 , value51 * ratio_to_report(mpc) over (partition byhid,t1,nvl2(maxt2,t2,1),nvl2(maxt3,t3,1)) mpc 7 from ( select t.*
8 , first_value(mpc) over (partition by t1,nvl2(maxt2,t2,1),nvl2(maxt3,t3,1) order by hid) value51 9 from ( select t.* 10 , max(decode(hid,51,t2)) over () maxt2 11 , max(decode(hid,51,t3)) over () maxt3 12 from mytable t 13 ) t 14 )
HID T1 T2 T3 T4 MPC
----- ----- ----- ----- ----- -----
53 1 34 1,2 53 1 1 8,8 53 6 34 20 53 4 34 44,55 53 4 841 25,45
5 rijen zijn geselecteerd.
Regards,
Rob.
Received on Tue Nov 13 2007 - 08:47:42 CST
 |
 |