From: | Tim Smith <gb10hkzo-postgres(at)yahoo(dot)co(dot)uk> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Help needed with postgres stats and math |
Date: | 2014-08-03 11:20:10 |
Message-ID: | 1407064810.33359.YahooMailNeo@web172805.mail.ir2.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2014-08-03 13:29:11 | Re: Reindex taking forever, and 99% CPU |
Previous Message | Adrian Klaver | 2014-08-03 04:08:45 | Re: Reindex taking forever, and 99% CPU |