Mailing-List: contact solr-user-help@lucene.apache.org; run by ezmlm
Precedence: bulk
Reply-To: solr-user@lucene.apache.org
Date: Fri, 12 Jun 2015 07:30:32 -0700 (MST)
From: kingofhypocrites <kingofhypocrites@gmail.com>
To: solr-user@lucene.apache.org
Message-ID: <1434119432169-4211402.post@n3.nabble.com>
Subject: Division with Stats Component when Grouping in Solr
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

I am migrating a database from SQL Server to Cassandra. Currently I have a
setup as follows:

- Log data in Cassandra
- Summarize data in Spark and put into Cassandra summary tables
- Query data in Solr

Everything fits beautifully until I need to do stats on groups. I am hoping
to get this to work with Solr so I can stick to one database, but I am not
sure it's possible.

If I had it in SQL Server, I could do it like so:
SELECT
    site_id, 
    keyword, 
    SUM(visits) as visits, 
    CONVERT(DECIMAL(13, 3), SUM(bounces)) / SUM(visits) as bounce_rate, 
    SUM(pageviews) as pageviews,
    CONVERT(DECIMAL(13, 3), SUM(pageviews)) / SUM(visits) as
avg_pages_per_visit 
FROM 
    report_all_keywords_daily 
WHERE 
    site_id = 55 AND date_key >= '20150606' AND date_key <= '20150608'
GROUP BY 
    site_id, keyword 
ORDER BY visits DESC

Now I need to replicate this in Solr. The closest I could get to this is by
using the Stats component and then using field collapsing.
group=true&group.field=keyword&stats=true&stats.field=visits&stats.facet=keyword

And here are some results I get back: http://pastebin.com/raw.php?i=Fxhe2RA0

However, I need to do able to divide certain metrics. I tried including
functions in the stats.field such as div(sum(bounce_rate), (sum(visits)) but
it doesn't recognize the functions. Also it seems to ignoring the paging for
the stats results and returns all groups regardless.

Ultimately I'd like something like this which is what I would get in SQL: 
<http://lucene.472066.n3.nabble.com/file/n4211402/pic.png> 

Is this possible or do I have to give up on the prospect of using Solr? I
have to query this data dynamically so I can't pre-summarize all of it.

To clarify I having the following two problems:
- Paging is ignored for stats data
- I can't figure out how to divide two stats together to get a third stat.
Note: In some cases I would need to be able to sort on this combined stat


--
View this message in context: http://lucene.472066.n3.nabble.com/Division-with-Stats-Component-when-Grouping-in-Solr-tp4211402.html
Sent from the Solr - User mailing list archive at Nabble.com.