Re: A question for Postgres OLAP gurus ....

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.