Re: Help with statistics windowing function

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

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