Re: Help needed with postgres stats and math

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
>