agora inbox for pgsql-performance@postgresql.org
help / color / mirror / Atom feedFrom: Mischa Sandberg <mischa_sandberg@telus.net>
To: pgsql-performance@postgresql.org
Subject: Re: Query performance
Date: Mon, 28 Jun 2004 05:23:53 GMT
Message-ID: <JXNDc.27548$_5.7680@clgrps13> (raw)
References: <BILLSA1XvpFVjCRGryW00000002@bill.fefferman.org>
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 (
stock VARCHAR(9)
,asof DATE,
,opening MONEY
,closing MONEY
,PRIMARY KEY (stock, asof)
)
SELECT stock, AVG((closing-opening)/opening) as ratio
FROM Prices
GROUP BY stock
ORDER BY ratio DESC LIMIT 10; -- top 10 best-performing stocks.
""Bill"" <bill@math.uchicago.edu> wrote in message news:BILLSA1XvpFVjCRGryW00000002@bill.fefferman.org...
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?
view thread (48+ messages) latest in thread
Message-ID: <JXNDc.27548$_5.7680@clgrps13>
Permalink: ../JXNDc.27548$_5.7680@clgrps13/
Also on: postgresql.org/message-id/JXNDc.27548$_5.7680@clgrps13
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mischa_sandberg@telus.net
Subject: Re: Query performance
In-Reply-To: <JXNDc.27548$_5.7680@clgrps13>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox