Received: from mps2.plala.or.jp (mpsb-nat02.plala.or.jp [202.212.114.145]) by postgresql.org (Postfix) with ESMTP id 791CB475C9F for ; Tue, 2 Apr 2002 10:42:18 -0500 (EST) Received: from vip333 ([218.47.116.26]) by mps2.plala.or.jp with ESMTP id <20020402154221.BJPO24668.mps2.plala.or.jp@vip333>; Wed, 3 Apr 2002 00:42:21 +0900 Date: Wed, 03 Apr 2002 00:44:23 +0900 From: Masaru Sugawara To: Mathieu Arnold Subject: Re: optimyzing Cc: pgsql-sql@postgresql.org In-Reply-To: <295475750.1017688497@sauron> References: <295475750.1017688497@sauron> Errors-To: rk73@sea.plala.or.jp Message-Id: <20020403003510.CEE3.RK73@sea.plala.or.jp> MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Mailer: Becky! ver. 2.00.08 X-Archive-Number: 200204/13 X-Sequence-Number: 7278 On Mon, 01 Apr 2002 19:14:57 +0200 Mathieu Arnold wrote: > and I can't get a query to have the average of that without having to do > this subselect. > > avg > ---------------- > 886.6585292820 > It seems to me that the query can be rewritten without a sub-query, even though the number of sampling per day is not always kept at a constant value. SELECT SUM(traffic)::float / COUNT(DISTINCT date(time))::float / 1024.0 / 86400.0 AS avg FROM stats WHERE ip = '212.43.217.66' AND date(time) < date('now') Regards, Masaru Sugawara