Mailing-List: contact java-user-help@lucene.apache.org; run by ezmlm
Precedence: bulk
Reply-To: java-user@lucene.apache.org
Received-SPF: pass (nike.apache.org: domain of gsiasf@gmail.com designates
 74.125.83.176 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=sender:content-type:mime-version:subject:from:in-reply-to:date
         :content-transfer-encoding:message-id:references:to:x-mailer;
        b=mGi2VNQg1BcF/Uk0jyMFsRsLiqCYPSPcrL1J0aYVucCLlxOedOfNihlVDp9pS7tD5p
         AVrMisUmjo7mUlEFmKThfyenjzSjjh/di/5wUo8oaBBUH32/IqV1LMyS2LBDSmZXhQ19
         1XUy7Gvznux/1p/sl088RUw0tAFWBE1+pip0o=
Sender: Grant Ingersoll <gsiasf@gmail.com>
Content-Type: text/plain; charset=us-ascii
Mime-Version: 1.0 (Apple Message framework v1078)
Subject: Re: Lucene Challenge - sum, count, avg, etc.
From: Grant Ingersoll <gsingers@apache.org>
In-Reply-To: <j2n962c93f61003311817mb0916942r662787cab6b3af28@mail.gmail.com>
Date: Thu, 1 Apr 2010 11:46:03 -0400
Content-Transfer-Encoding: quoted-printable
Message-Id: <A1FE7DDC-1643-4E1C-B3AB-B1F01B2304C2@apache.org>
References: <j2n962c93f61003311817mb0916942r662787cab6b3af28@mail.gmail.com>
To: java-user@lucene.apache.org

Have you looked at Solr's StatsComponent?

On Mar 31, 2010, at 9:17 PM, Michel Nadeau wrote:

> Hi,
>=20
> We're currently in the process of switching many of our screens from =
MySQL
> to Lucene because MySQL simply dies because we have too much data and =
it's
> becoming too long to generate the stats we need.
>=20
> So here's one MySQL query that we use to find out our Top 10 =
Affiliates :
>=20
> SELECT SUM(sale_amount) AS total_sales, affialiate_id FROM sales WHERE
> sale_date>=3D'2010-03-01' AND sale_date<=3D'2010-03-31' GROUP BY =
affialiate_id
> ORDER BY total_sales DESC LIMIT 10;
>=20
> We currently have our "sales" index, containing all sales and all =
fields -
> and it's one big index (over 10M records). We could fetch all =
documents
> within the date range, loop them and add up the total_sales, but it =
would be
> just crazy to do this all the time (we have a high volume of search).
>=20
> We made several tests with Solr (Facets, and even the beta =
CollapseFields),
> but nothing is really helping us. We could pre-generate the =
total_sales for
> all possible date ranges... but that would be quite crazy too as the =
date
> range possibilities quickly become endless.
>=20
> So - is there any known way to efficiently do SUM(), COUNT() (and even =
AVG()
> ) using Lucene/Solr/others? I also checked Bobo Browse but it doesn't =
seem
> to offer what I need either.
>=20
> Thanks for any hints!!!
>=20
> - Mike
> akaris@gmail.com

--------------------------
Grant Ingersoll
http://www.lucidimagination.com/

Search the Lucene ecosystem using Solr/Lucene: =
http://www.lucidimagination.com/search


---------------------------------------------------------------------
To unsubscribe, e-mail: java-user-unsubscribe@lucene.apache.org
For additional commands, e-mail: java-user-help@lucene.apache.org