Help with statistics windowing function

Discussion:

Luis Santos

2018-11-25 18:51:01 UTC

Permalink

*--*
*Att*

*Luis Santos*

Luis Santos

2018-11-25 18:57:47 UTC

Permalink

I have the following table, with 20 random values.

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.

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()
*
ORA-30483: window functions are not allowed here

*--*
*Att*

*Luis Santos*

Harel Safra

2018-11-25 19:21:17 UTC

Permalink

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

Post by Luis Santos
I have the following table, with 20 random values.

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.

I want to get, in also windowing fashion, the MIN/MAX values from the DTM
column. But...
select val

*--*
*Att*
*Luis Santos*

Luis Santos

2018-11-25 19:46:35 UTC

Permalink

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.

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.

1 row updated.
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*

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

Post by Luis Santos
I have the following table, with 20 random values.

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.

I want to get, in also windowing fashion, the MIN/MAX values from the DTM
column. But...
select val

*--*
*Att*
*Luis Santos*

Harel Safra

2018-11-25 20:03:52 UTC

Permalink

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

Post by Luis Santos
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.

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.

1 row updated.
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*

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

Post by Luis Santos
I have the following table, with 20 random values.

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.

I want to get, in also windowing fashion, the MIN/MAX values from the
DTM column. But...
select val

*--*
*Att*
*Luis Santos*

Luis Santos

2018-11-25 20:22:10 UTC

Permalink

Perfect Harel! Thanks a lot!

*--*
*Att*

*Luis Santos*

Post by Harel Safra
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