A question for Postgres OLAP gurus ....

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: A question for Postgres OLAP gurus ....
Date: 2014-08-08 21:14:56
Message-ID: 1407532496.94713.YahooMailNeo@web172802.mail.ir2.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a table as follows :

Year (numeric)
Factor (text)
Test_1 (numeric)
Test_2 (numeric)
Test_3 (numeric)
Test_4 (numeric)
unique index(year,factor) (i.e. each factor only appears once per year)

What I need to achieve is an ordered ranking of the factors for each test, e.g. let's say I've got a factor called "Time".  I need to count and order by the number of times the factor has ranked first,second,third etc.  for a given test. 

So if factor "time" in year 1 it came first in an order desc, and year 2 & 3 it came second in an order desc, the totals for "Time" would be first once and second twice.

I've been staring at my screen too long today and am messing up my ranks with my partitions with my order by's .... I'm just in an SQL syntax mess....so hopefully maybe somebody here has solved a similar conundrum before.

For extra kudos points, you might also wish to show me how the solution to the above can be done in conjunction with  normalised zScoring of the tests (i.e. select Test_1-avg(Test_1)/stddev(Test_1)  as Test_1Norm).  ;-)

Thanks !

Tim

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrus 2014-08-08 21:19:57 How to use recursive clause in one with query
Previous Message Deb Brooks 2014-08-08 20:45:34 Re: Where can I download PostgreSQL 9.4 for AIX 6?