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.
