Re: Help with statistics windowing function

Perfect Harel! Thanks a lot!

*--*
*Att*

*Luis Santos*

Em dom, 25 de nov de 2018 às 18:04, Harel Safra <harel.safra_at_gmail.com> escreveu:

> You normalize the values to a [0..n] range and them divide by the range
> size.
> This should work, the trunc is to make the output more readable.
>
> SELECT
> val,
> trunc((val - MIN(val) OVER())/ ( MAX(val) OVER() - MIN(val) OVER()
> ),3) norm
> FROM
> test order by val;
>
> Harel Safra
>
> On Sun, Nov 25, 2018 at 9:47 PM Luis Santos <lsantos_at_pobox.com> wrote:
>
>> Thanks for all replies. I forgot to mention that I really could use an
>> inline view or a with subfactoring view. But, to be frank, I was playing
>> with this because I want a way to normalize the data, creating a rank.
>>
>> I discover the PERCENT_RANK windowing function. And applied a minus 0.5
>> to it to get this results.
>>
>> U71013576_at_P00MDS.brux0333 [11g]> col pct_rank format 9990D999
>>> U71013576_at_P00MDS.brux0333 [11g]>
>>> U71013576_at_P00MDS.brux0333 [11g]> select val
>>> 2 , avg(val) over ()
>>> 3 , val - avg(val) over ()
>>> 4 , min(val) over ()
>>> 5 , max(val) over ()
>>> 6 , PERCENT_RANK() over (order by val)-0.5 PCT_RANK
>>> 7 from test
>>> 8 /
>>> VAL AVG(VAL)OVER() VAL-AVG(VAL)OVER() MIN(VAL)OVER()
>>> MAX(VAL)OVER() PCT_RANK
>>> ---------- -------------- ------------------ --------------
>>> -------------- ---------
>>> 8 44,25 -36,25 8
>>> 91 -0,500
>>> 12 44,25 -32,25 8
>>> 91 -0,447
>>> 14 44,25 -30,25 8
>>> 91 -0,395
>>> 14 44,25 -30,25 8
>>> 91 -0,395
>>> 20 44,25 -24,25 8
>>> 91 -0,289
>>> 25 44,25 -19,25 8
>>> 91 -0,237
>>> 26 44,25 -18,25 8
>>> 91 -0,184
>>> 32 44,25 -12,25 8
>>> 91 -0,132
>>> 34 44,25 -10,25 8
>>> 91 -0,079
>>> 35 44,25 -9,25 8
>>> 91 -0,026
>>> 35 44,25 -9,25 8
>>> 91 -0,026
>>> 49 44,25 4,75 8
>>> 91 0,079
>>> 64 44,25 19,75 8
>>> 91 0,132
>>> 65 44,25 20,75 8
>>> 91 0,184
>>> 65 44,25 20,75 8
>>> 91 0,184
>>> 70 44,25 25,75 8
>>> 91 0,289
>>> 73 44,25 28,75 8
>>> 91 0,342
>>> 73 44,25 28,75 8
>>> 91 0,342
>>> 80 44,25 35,75 8
>>> 91 0,447
>>> 91 44,25 46,75 8
>>> 91 0,500
>>> 20 rows selected.
>>
>>
>> But the "normalized" scale on PCT_RANK column is positional only. If I
>> change the MAX value to a real big one the value for PCT_RANK don't change.
>>
>> U71013576_at_P00MDS.brux0333 [11g]> update test set val=9100 where val=91;
>>> 1 row updated.
>>> U71013576_at_P00MDS.brux0333 [11g]> col pct_rank format 9990D999
>>> U71013576_at_P00MDS.brux0333 [11g]>
>>> U71013576_at_P00MDS.brux0333 [11g]> select val
>>> 2 , avg(val) over ()
>>> 3 , val - avg(val) over ()
>>> 4 , min(val) over ()
>>> 5 , max(val) over ()
>>> 6 , PERCENT_RANK() over (order by val)-0.5 PCT_RANK
>>> 7 from test
>>> 8 /
>>> VAL AVG(VAL)OVER() VAL-AVG(VAL)OVER() MIN(VAL)OVER()
>>> MAX(VAL)OVER() PCT_RANK
>>> ---------- -------------- ------------------ --------------
>>> -------------- ---------
>>> 8 494,7 -486,7 8
>>> 9100 -0,500
>>> 12 494,7 -482,7 8
>>> 9100 -0,447
>>> 14 494,7 -480,7 8
>>> 9100 -0,395
>>> 14 494,7 -480,7 8
>>> 9100 -0,395
>>> 20 494,7 -474,7 8
>>> 9100 -0,289
>>> 25 494,7 -469,7 8
>>> 9100 -0,237
>>> 26 494,7 -468,7 8
>>> 9100 -0,184
>>> 32 494,7 -462,7 8
>>> 9100 -0,132
>>> 34 494,7 -460,7 8
>>> 9100 -0,079
>>> 35 494,7 -459,7 8
>>> 9100 -0,026
>>> 35 494,7 -459,7 8
>>> 9100 -0,026
>>> 49 494,7 -445,7 8
>>> 9100 0,079
>>> 64 494,7 -430,7 8
>>> 9100 0,132
>>> 65 494,7 -429,7 8
>>> 9100 0,184
>>> 65 494,7 -429,7 8
>>> 9100 0,184
>>> 70 494,7 -424,7 8
>>> 9100 0,289
>>> 73 494,7 -421,7 8
>>> 9100 0,342
>>> 73 494,7 -421,7 8
>>> 9100 0,342
>>> 80 494,7 -414,7 8
>>> 9100 0,447
>>> 9100 494,7 8605,3 8
>>> 9100 0,500
>>> 20 rows selected.
>>
>>
>> Is there a better way to normalize a list of values, using a windowing
>> function, to a [0..1] scale?
>>
>> *--*
>> *Att*
>>
>>
>> *Luis Santos*
>>
>>
>> Em dom, 25 de nov de 2018 às 17:21, Harel Safra <harel.safra_at_gmail.com>
>> escreveu:
>>
>>> SELECT
>>> t.*,
>>> MIN(dtm) OVER(),
>>> MAX(dtm) OVER()
>>> FROM
>>> (
>>> SELECT
>>> val,
>>> AVG(val) OVER() avg,
>>> val - AVG(val) OVER() dtm,
>>> MIN(val) OVER() min,
>>> MAX(val) OVER() max
>>> FROM
>>> test
>>> ) t;
>>>
>>> Harel
>>>
>>> On Sun, Nov 25, 2018 at 8:59 PM Luis Santos <lsantos_at_pobox.com> wrote:
>>>
>>>> I have the following table, with 20 random values.
>>>>
>>>> U71013576_at_P00MDS.brux0333 [11g]> select val from test;
>>>>> VAL
>>>>> ----------
>>>>> 65
>>>>> 70
>>>>> 12
>>>>> 65
>>>>> 91
>>>>> 25
>>>>> 8
>>>>> 73
>>>>> 35
>>>>> 20
>>>>> 26
>>>>> 14
>>>>> 73
>>>>> 35
>>>>> 49
>>>>> 80
>>>>> 34
>>>>> 14
>>>>> 32
>>>>> 64
>>>>> 20 rows selected.
>>>>
>>>>
>>>> With the following query I can get the average, the min and max value,
>>>> and a calculated distance from mean.
>>>>
>>>> U71013576_at_P00MDS.brux0333 [11g]> select val
>>>>> 2 , avg(val) over () AVG
>>>>> 3 , val - avg(val) over () DTM
>>>>> 4 , min(val) over () MIN
>>>>> 5 , max(val) over () MAX
>>>>> 6 from test;
>>>>>
>>>>
>>>>
>>>>> VAL AVG DTM MIN MAX
>>>>> ---------- ---------- ---------- ---------- ----------
>>>>> 65 44,25 20,75 8 91
>>>>> 70 44,25 25,75 8 91
>>>>> 12 44,25 -32,25 8 91
>>>>> 65 44,25 20,75 8 91
>>>>> 91 44,25 46,75 8 91
>>>>> 25 44,25 -19,25 8 91
>>>>> 8 44,25 -36,25 8 91
>>>>> 73 44,25 28,75 8 91
>>>>> 35 44,25 -9,25 8 91
>>>>> 20 44,25 -24,25 8 91
>>>>> 26 44,25 -18,25 8 91
>>>>> 14 44,25 -30,25 8 91
>>>>> 73 44,25 28,75 8 91
>>>>> 35 44,25 -9,25 8 91
>>>>> 49 44,25 4,75 8 91
>>>>> 80 44,25 35,75 8 91
>>>>> 34 44,25 -10,25 8 91
>>>>> 14 44,25 -30,25 8 91
>>>>> 32 44,25 -12,25 8 91
>>>>> 64 44,25 19,75 8 91
>>>>> 20 rows selected.
>>>>
>>>>
>>>>
>>>> I want to get, in also windowing fashion, the MIN/MAX values from the
>>>> DTM column. But...
>>>>
>>>> select val
>>>>> , avg(val) over ()
>>>>> , val - avg(val) over ()
>>>>> , min(val) over ()
>>>>> , max(val) over ()
>>>>> , min(val - avg(val) over ()) over()
>>>>> , max(val - avg(val) over ()) over()
>>>>> from test
>>>>> /
>>>>
>>>>
>>>>
>>>>> , min(val - avg(val) over ()) over()
>>>>> *
>>>>> ERROR at line 6:
>>>>> ORA-30483: window functions are not allowed here
>>>>
>>>>
>>>>
>>>>
>>>> *--*
>>>> *Att*
>>>>
>>>>
>>>> *Luis Santos*
>>>>
>>>>

--
http://www.freelists.org/webpage/oracle-l