aggregate functions in Solr?

[Esteban Donato <es...@gmail.com>]

Hello guys,

  I need to implement a functionality which requires something similar
to aggregate functions in SQL.  My Solr schema looks like this:

-doc_id: integer
-date: date
-value1: integer
-value2: integer

  Basically the index contains some numerical values (value1, value2,
etc) per doc and date.  Given a date range query, I need to return
some stats consolidated by docs for that given date range.  I typical
response could be something like this:

doc_id, sum(value1),  avg(value2),  sum(value1)/sum(value2).

  I checked StatsComponent using stats.facet=doc_id but it seems it
doesn't cover my needs (especially for complex stats like
sum(value1)/sum(value2)).  Also checked FieldCollapsing but I couldn't
find a way to configure an aggregate function there.

  Is there any way to implement this, or I will have to resolve it out of Solr?

Regards,
Esteban

RE: aggregate functions in Solr?

[Steve McKay <sm...@gcefederal.com>]

> -----Original Message-----
> From: Esteban Donato [mailto:esteban.donato@gmail.com]
> Sent: Monday, September 26, 2011 2:08 PM
> To: solr-user@lucene.apache.org
> Subject: aggregate functions in Solr?
> 
> Hello guys,
> 
>   I need to implement a functionality which requires something similar
> to aggregate functions in SQL.  My Solr schema looks like this:
> 
> -doc_id: integer
> -date: date
> -value1: integer
> -value2: integer
> 
>   Basically the index contains some numerical values (value1, value2,
> etc) per doc and date.  Given a date range query, I need to return
> some stats consolidated by docs for that given date range.  I typical
> response could be something like this:
> 
> doc_id, sum(value1),  avg(value2),  sum(value1)/sum(value2).
> 
>   I checked StatsComponent using stats.facet=doc_id but it seems it
> doesn't cover my needs (especially for complex stats like
> sum(value1)/sum(value2)).  Also checked FieldCollapsing but I couldn't
> find a way to configure an aggregate function there.
> 
>   Is there any way to implement this, or I will have to resolve it out of
> Solr?
> 
> Regards,
> Esteban

To use your example, you could query stats.field=value1&stats.field=value2&stats.facet=doc_id and calculate sum(value1)/sum(value2) in a Velocity template. I'm not sure it's a *good* solution, but that's a way you could get the results you want in the response from Solr.

Steve