Re: Help needed with postgres stats and math

Or...

Do you mean to use windowing functions?
http://www.postgresql.org/docs/9.3/static/tutorial-window.html

Or both of course...

Kind regards/met vriendelijke groet,

Serge Fonville

http://www.sergefonville.nl

2014-08-04 19:43 GMT+02:00 Serge Fonville <serge(dot)fonville(at)gmail(dot)com>:

> Hi,
>
> Perhaps a CTE would help?
>
> WITH NormCTE AS (
> SELECT
> delta - avg(delta))/stddev(delta) AS deltaNorm
> , (echo - avg(echo))/stddev(echo) AS echoNorm
> , (foxtrot - avg(foxtrot))/stddev(foxtrot) AS foxtrotNorm
> FROM t_subs
> )
> SELECT
> deltaNorm + echoNorm + foxtrotNorm AS normSum
> FROM NormCTE
> ORDER BY normSum DESC
>
> HTH
>
> Kind regards/met vriendelijke groet,
>
> Serge Fonville
>
> http://www.sergefonville.nl
>
>
> 2014-08-03 13:20 GMT+02:00 Tim Smith <gb10hkzo-postgres(at)yahoo(dot)co(dot)uk>:
>
> Hi,
>>
>> I'm on Postgres 9.3.5, however I think my knowledge of Postgres is not
>> deep enough to help me with this challenge, so here I am reaching out to
>> the community !
>>
>> Let's say I have a table as follows :
>>
>> create table t_subs (alpha text,bravo text,charlie numeric,delta
>> numeric,echo numeric,foxtrot numeric);
>>
>> And let's say I have a view that does some basic filtering on that table
>>
>> create view v_subs as select alpha,delta,echo,foxtrot from t_subs where
>> charlie>=5 and bravo not in ('this','that');
>>
>> What I need to do is order the output of the view based on normalised
>> output of delta,echo and foxtrot.
>>
>> So, what I need to do is :
>>
>> 1/ Calculate normalised values for each column and row....
>>
>> deltaNorm = (delta - avg(delta))/stddev(delta)
>> echoNorm = (echo - avg(echo))/stddev(echo)
>>
>> foxtrotNorm = (foxtrot - avg(foxtrot))/stddev(foxtrot)
>> normSum = deltaNorm + echoNorm + foxtrotNorm
>>
>> 2/ order desc on normSum
>>
>> The problem is I cannot seem to find a way to do this in one query.
>>
>> Thanks in advance for your help !
>>
>> Tim
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>