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.fonville@xxxxxxxxx> wrote:
Or...
Do you mean to use windowing functions?http://www.postgresql.org/docs/9.3/static/tutorial-window.html
2014-08-04 19:43 GMT+02:00 Serge Fonville <serge.fonville@xxxxxxxxx>:
WITH NormCTE AS (Hi,Perhaps a CTE would help?
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 DESCHTH2014-08-03 13:20 GMT+02:00 Tim Smith <gb10hkzo-postgres@xxxxxxxxxxx>:
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
