Re: Help needed with postgres stats and math

Hi Serge,

A million apologies for the delayed acknowledgement of your email.   The Yahoo webmail is doing weird things with conversations (your email was hiding in my sent box instead of inbox, tagged onto the end of my original email !).

But I digress.  I will take a look at your suggestions and get back to you over the next day or so.

Thank you

Tim

On Monday, 4 August 2014, 18:46, Serge Fonville <serge(dot)fonville(at)gmail(dot)com> wrote:

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
>>
>