Thread: A question for Postgres OLAP gurus ....
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
Tim Smith wrote > 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 I'd be willing to look if there was some actual data to play with and an expected result presented. WITH sampledata AS ( <put sample data here> ) SELECT * FROM sampledata; [Desired Result] <put your manually reasoned result using the sample data here> If you have any queries that even get you close you can incorporate them as part of the sample data query. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/A-question-for-Postgres-OLAP-gurus-tp5814258p5814263.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.