Usually, when you post a request like this, you should provide something a little more concrete (the CREATE TABLE statement for that table, with
Since you didn't, I'll posit something that sounds like what you're using, and take a stab at your problem.
TABLE Prices (
,PRIMARY KEY (stock, asof)
SELECT stock, AVG((closing-opening)/opening) as ratio
GROUP BY stock
ORDER BY ratio DESC LIMIT 10; -- top 10 best-performing stocks.
""Bill"" <bill(at)math(dot)uchicago(dot)edu> wrote in message news:BILLSA1XvpFVjCRGryW00000002(at)bill(dot)fefferman(dot)org(dot)(dot)(dot)
Actually, I have some queries that are slow, however I was wondering if you could help me write a query that is rather simple, but I, as a true database novice, can't seem to conjure. So we have stocks, as I have previously said, and I have a huge table which contains all of the opening and closing prices of some stocks from each day. What I like to do, in English, for each stock in each day is find a ratio: abs(closing-opening)/opening. Then I would like to average all of the ratios of each day of each individual stock together to find a final ratio for each stock, then I would like to find the highest average, to find the best performing stock. So what query can I use, and (as is appropriate for this group), how can it be optimized to run the fastest?
In response to
pgsql-performance by date
|Next:||From: Klint Gore||Date: 2004-06-28 05:29:57|
|Subject: Re: SQL stupid query plan... terrible performance ! |
|Previous:||From: Tom Lane||Date: 2004-06-28 05:19:42|
|Subject: Re: postgres 7.4 at 100% |