X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C13F7D1B248 for ; Mon, 28 Jun 2004 02:44:11 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 62250-04 for ; Mon, 28 Jun 2004 05:44:09 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 127C0D1B1F8 for ; Mon, 28 Jun 2004 02:44:07 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i5S5i6QC077021 for ; Mon, 28 Jun 2004 05:44:06 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i5S5Nr58069913 for pgsql-performance@postgresql.org; Mon, 28 Jun 2004 05:23:53 GMT From: "Mischa Sandberg" X-Newsgroups: comp.databases.postgresql.performance References: Subject: Re: Query performance Lines: 156 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_004C_01C45C95.6EC8EA80" X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 Message-ID: Date: Mon, 28 Jun 2004 05:23:53 GMT To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.8 tagged_above=0.0 required=5.0 tests=HTML_40_50, HTML_MESSAGE, PRIORITY_NO_NAME, WEIRD_QUOTING X-Spam-Level: * X-Archive-Number: 200406/332 X-Sequence-Number: 7390 This is a multi-part message in MIME format. ------=_NextPart_000_004C_01C45C95.6EC8EA80 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Usually, when you post a request like this, you should provide something a = little more concrete (the CREATE TABLE statement for that table, with=20 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=20 GROUP BY stock ORDER BY ratio DESC LIMIT 10; -- top 10 best-performing stocks. ""Bill"" wrote in message news:BILLSA1XvpFVjCRGryW= 00000002@bill.fefferman.org... Actually, I have some queries that are slow, however I was wondering if y= ou could help me write a query that is rather simple, but I, as a true data= base novice, can't seem to conjure. So we have stocks, as I have previousl= y said, and I have a huge table which contains all of the opening and closi= ng 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. The= n 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 f= ind 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? =20=20=20 ------=_NextPart_000_004C_01C45C95.6EC8EA80 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Usually, when you post a request like this, you should = provide=20 something a little more concrete (the CREATE TABLE statement for that table= ,=20 with

Since you didn't, I'll posit something that sounds like= what=20 you're using, and take a stab at your problem.

TABLE Prices (

stock =20 VARCHAR(9)

,asof =20 DATE,

,opening MONEY

,closing =20 MONEY

,PRIMARY KEY (stock, asof)

)

SELECT stock, AVG((closing-opening)/o= pening)=20 as ratio

FROM Prices

GROUP BY stock

ORDER BY ratio DESC LIMIT 10; -- top = 10=20 best-performing stocks.

""Bill"" <bill@math.uchicago.edu> wrote= in=20 message news:BILLSA1Xv= pFVjCRGryW00000002@bill.fefferman.org...

Actually, I have so= me=20 queries that are slow, however I was wondering if you could help me write= a=20 query that is rather simple, but I, as a true database novice, can't seem= to=20 conjure. So we have stocks, as I have previously said, and I have a= huge=20 table which contains all of the opening and closing prices of some stocks= from=20 each day. What I like to do, in English, for each stock in each day= is=20 find a ratio: abs(closing-opening)/opening. Then I would like to av= erage=20 all of the ratios of each day of each individual stock together to find a= =20 final ratio for each stock, then I would like to find the highest average= , to=20 find the best performing stock. So what query can I use, and (as is= =20 appropriate for this group), how can it be optimized to run the=20 fastest?

------=_NextPart_000_004C_01C45C95.6EC8EA80--