Re: Help needed with postgres stats and math - Mailing list pgsql-general

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@yahoo.co.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general