@Ken: yeah we thought about it - but we have a HUGE amount of data (sales, affiliates, etc.) - so pre-calculating everything isn't really an option. Plus I don't know how we would sort.. let's say I get the totals for affiliate X, loop totals from day 1 to X (range), sum up, great: I can do this for all affiliates and have the totals, but how will I sort by that total?
@Chris: we don't want to switch to a whole new platform - is it possible to use that DBSight module only? It doesn't seem to be an opensource project so I can't really consider it. - Mike aka...@gmail.com On Thu, Apr 1, 2010 at 5:00 AM, Chris Lu <chris...@gmail.com> wrote: > Hi, Michel, > > This has already been implemented in DBSight. Check it out! > http://www.dbsight.net > > You can get sum, avg for Facet searches. And count is included in Facet > search directly. > > -- > Chris Lu > ------------------------- > Instant Scalable Full-Text Search On Any Database/Application > site: http://www.dbsight.net > demo: http://search.dbsight.com > Lucene Database Search in 3 minutes: > http://wiki.dbsight.com/index.php?title=Create_Lucene_Database_Search_in_3_minutes > DBSight customer, a shopping comparison site, (anonymous per request) got > 2.6 Million Euro funding! > > > > > Michel Nadeau wrote: > >> Hi, >> >> 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. >> >> So here's one MySQL query that we use to find out our Top 10 Affiliates : >> >> SELECT SUM(sale_amount) AS total_sales, affialiate_id FROM sales WHERE >> sale_date>='2010-03-01' AND sale_date<='2010-03-31' GROUP BY affialiate_id >> ORDER BY total_sales DESC LIMIT 10; >> >> 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). >> >> 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. >> >> 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. >> >> Thanks for any hints!!! >> >> - Mike >> aka...@gmail.com >> >> >> > > --------------------------------------------------------------------- > To unsubscribe, e-mail: java-user-unsubscr...@lucene.apache.org > For additional commands, e-mail: java-user-h...@lucene.apache.org > >
