You are viewing a plain text version of this content. The canonical link for it is here.
Posted to solr-user@lucene.apache.org by Jeremy Branham <jd...@hotmail.com> on 2012/07/07 18:05:54 UTC
Grouping and Averages
I’m sorry – I sent this email before I was confirmed in the group, so I don’t know if anyone sent a reply =\
__________________________________________
Hello -
I’m not sure If this is an appropriate use for Solr, but I want to stay away from a typical DB store for high availability reasons.
I am storing documents that may have a common value for a field we’ll call “category”.
In another field there will be an integer field we’ll call “rating”.
I would like to group the documents on the “category” field and display the average “rating” per group.
The stats component lets me get the avg rating, but when I collapse the results into groups it gives me the average for the entire collection, rather than for the specific group.
Am I going about this wrong?
Is it possible to get the desired outcome with a single query?
I’d appreciate any insight!
Thank you,
Jeremy Branham
Software Engineer
http://LinkedIn.com/in/JeremyBranham
http://jeremybranham.wordpress.com/
http://Zeroth.biz
Re: Grouping and Averages
Posted by Walter Underwood <wu...@wunderwood.org>.
That could work well.
Think of the Solr index as a big, flat view on your data. Index the fields you search on and store the fields you retrieve. Missing fields are OK.
Fields can be multi-valued, which is non-relational but handy. If you are in MySQL, check out GROUP_CONCAT for a way to think about mapping relational to multi-valued fields.
wunder
Walter Underwood
Search Guy, Chegg
On Jul 7, 2012, at 2:51 PM, Jeremy Branham wrote:
> Thanks.
> At this time, it looks like it may be best to use a DB as a backing store, then scheduling a task to store pre-aggregated data and other documents in Solr.
>
>
> Jeremy Branham
> Software Engineer
> http://LinkedIn.com/in/JeremyBranham
> http://jeremybranham.wordpress.com/
> http://Zeroth.biz
>
> -----Original Message----- From: Jason Rutherglen
> Sent: Saturday, July 07, 2012 4:39 PM
> To: solr-user@lucene.apache.org
> Subject: Re: Grouping and Averages
>
> I don't think aggregations in the Solr group by are completed yet. There's
> a Lucene or Solr issue implementing group by count that could be adapted to
> implement average for example.
>
> On Sat, Jul 7, 2012 at 4:37 PM, Jeremy Branham <jd...@hotmail.com>wrote:
>
>> Thanks for the replies.
>> I may be able to simplify my requirements.
>>
>> In my application, the number of documents per group indicate popularity.
>> If I could sort the groups descending by the document count, then using
>> the stats component + filter I could query each group to get avg value for
>> a field.
>>
>> Though I dont see how to sort the groups by document count.
>> I thought maybe a pseudo field with a functional query would return a
>> document element but my tests failed.
>>
>> Its a bit of a challenge to switch my thought process from SQL to Solr.
>>
>>
>> Jeremy Branham
>> Software Engineer
>> http://LinkedIn.com/in/**JeremyBranham<http://LinkedIn.com/in/JeremyBranham>
>> http://jeremybranham.**wordpress.com/<http://jeremybranham.wordpress.com/>
>> http://Zeroth.biz
>>
>> -----Original Message----- From: Jason Rutherglen
>> Sent: Saturday, July 07, 2012 2:45 PM
>>
>> To: solr-user@lucene.apache.org
>> Subject: Re: Grouping and Averages
>>
>> Average should be doable in Solr, maybe not today, not sure. Median is the
>> challenge :) Try Hive.
>>
>> On Sat, Jul 7, 2012 at 3:34 PM, Walter Underwood <wunder@wunderwood.org
>> >wrote:
>>
>> It sounds like you need a database for analytics, not a search engine.
>>>
>>> Solr cannot do aggregates like that. It can select and group, but to
>>> calculate averages you'll need to fetch all the results over the network
>>> and calculate them yourself.
>>>
>>> wunder
>>>
>>> On Jul 7, 2012, at 9:05 AM, Jeremy Branham wrote:
>>>
>>> > I’m sorry – I sent this email before I was confirmed in the group, so I
>>> don’t know if anyone sent a reply =\
>>> >
>>> > ______________________________**____________
>>> >
>>> > Hello -
>>> > I’m not sure If this is an appropriate use for Solr, but I want to stay
>>> away from a typical DB store for high availability reasons.
>>> >
>>> > I am storing documents that may have a common value for a field we’ll
>>> call “category”.
>>> > In another field there will be an integer field we’ll call “rating”.
>>> >
>>> > I would like to group the documents on the “category” field and display
>>> the average “rating” per group.
>>> >
>>> > The stats component lets me get the avg rating, but when I collapse the
>>> results into groups it gives me the average for the entire collection,
>>> rather than for the specific group.
>>> >
>>> > Am I going about this wrong?
>>> > Is it possible to get the desired outcome with a single query?
>>> >
>>> > I’d appreciate any insight!
>>> > Thank you,
>>> >
>>> >
>>> >
>>> > Jeremy Branham
>>> > Software Engineer
>>> > http://LinkedIn.com/in/**JeremyBranham<http://LinkedIn.com/in/JeremyBranham>
>>> > http://jeremybranham.**wordpress.com/<http://jeremybranham.wordpress.com/>
>>> > http://Zeroth.biz
>>>
>>>
>>>
>>>
>>>
>
--
Walter Underwood
wunder@wunderwood.org
Re: Grouping and Averages
Posted by Jeremy Branham <jd...@hotmail.com>.
Thanks.
At this time, it looks like it may be best to use a DB as a backing store,
then scheduling a task to store pre-aggregated data and other documents in
Solr.
Jeremy Branham
Software Engineer
http://LinkedIn.com/in/JeremyBranham
http://jeremybranham.wordpress.com/
http://Zeroth.biz
-----Original Message-----
From: Jason Rutherglen
Sent: Saturday, July 07, 2012 4:39 PM
To: solr-user@lucene.apache.org
Subject: Re: Grouping and Averages
I don't think aggregations in the Solr group by are completed yet. There's
a Lucene or Solr issue implementing group by count that could be adapted to
implement average for example.
On Sat, Jul 7, 2012 at 4:37 PM, Jeremy Branham <jd...@hotmail.com>wrote:
> Thanks for the replies.
> I may be able to simplify my requirements.
>
> In my application, the number of documents per group indicate popularity.
> If I could sort the groups descending by the document count, then using
> the stats component + filter I could query each group to get avg value for
> a field.
>
> Though I dont see how to sort the groups by document count.
> I thought maybe a pseudo field with a functional query would return a
> document element but my tests failed.
>
> Its a bit of a challenge to switch my thought process from SQL to Solr.
>
>
> Jeremy Branham
> Software Engineer
> http://LinkedIn.com/in/**JeremyBranham<http://LinkedIn.com/in/JeremyBranham>
> http://jeremybranham.**wordpress.com/<http://jeremybranham.wordpress.com/>
> http://Zeroth.biz
>
> -----Original Message----- From: Jason Rutherglen
> Sent: Saturday, July 07, 2012 2:45 PM
>
> To: solr-user@lucene.apache.org
> Subject: Re: Grouping and Averages
>
> Average should be doable in Solr, maybe not today, not sure. Median is
> the
> challenge :) Try Hive.
>
> On Sat, Jul 7, 2012 at 3:34 PM, Walter Underwood <wunder@wunderwood.org
> >wrote:
>
> It sounds like you need a database for analytics, not a search engine.
>>
>> Solr cannot do aggregates like that. It can select and group, but to
>> calculate averages you'll need to fetch all the results over the network
>> and calculate them yourself.
>>
>> wunder
>>
>> On Jul 7, 2012, at 9:05 AM, Jeremy Branham wrote:
>>
>> > I’m sorry – I sent this email before I was confirmed in the group, so I
>> don’t know if anyone sent a reply =\
>> >
>> > ______________________________**____________
>> >
>> > Hello -
>> > I’m not sure If this is an appropriate use for Solr, but I want to stay
>> away from a typical DB store for high availability reasons.
>> >
>> > I am storing documents that may have a common value for a field we’ll
>> call “category”.
>> > In another field there will be an integer field we’ll call “rating”.
>> >
>> > I would like to group the documents on the “category” field and display
>> the average “rating” per group.
>> >
>> > The stats component lets me get the avg rating, but when I collapse the
>> results into groups it gives me the average for the entire collection,
>> rather than for the specific group.
>> >
>> > Am I going about this wrong?
>> > Is it possible to get the desired outcome with a single query?
>> >
>> > I’d appreciate any insight!
>> > Thank you,
>> >
>> >
>> >
>> > Jeremy Branham
>> > Software Engineer
>> > http://LinkedIn.com/in/**JeremyBranham<http://LinkedIn.com/in/JeremyBranham>
>> > http://jeremybranham.**wordpress.com/<http://jeremybranham.wordpress.com/>
>> > http://Zeroth.biz
>>
>>
>>
>>
>>
>
Re: Grouping and Averages
Posted by Jason Rutherglen <ja...@gmail.com>.
I don't think aggregations in the Solr group by are completed yet. There's
a Lucene or Solr issue implementing group by count that could be adapted to
implement average for example.
On Sat, Jul 7, 2012 at 4:37 PM, Jeremy Branham <jd...@hotmail.com>wrote:
> Thanks for the replies.
> I may be able to simplify my requirements.
>
> In my application, the number of documents per group indicate popularity.
> If I could sort the groups descending by the document count, then using
> the stats component + filter I could query each group to get avg value for
> a field.
>
> Though I dont see how to sort the groups by document count.
> I thought maybe a pseudo field with a functional query would return a
> document element but my tests failed.
>
> Its a bit of a challenge to switch my thought process from SQL to Solr.
>
>
> Jeremy Branham
> Software Engineer
> http://LinkedIn.com/in/**JeremyBranham<http://LinkedIn.com/in/JeremyBranham>
> http://jeremybranham.**wordpress.com/<http://jeremybranham.wordpress.com/>
> http://Zeroth.biz
>
> -----Original Message----- From: Jason Rutherglen
> Sent: Saturday, July 07, 2012 2:45 PM
>
> To: solr-user@lucene.apache.org
> Subject: Re: Grouping and Averages
>
> Average should be doable in Solr, maybe not today, not sure. Median is the
> challenge :) Try Hive.
>
> On Sat, Jul 7, 2012 at 3:34 PM, Walter Underwood <wunder@wunderwood.org
> >wrote:
>
> It sounds like you need a database for analytics, not a search engine.
>>
>> Solr cannot do aggregates like that. It can select and group, but to
>> calculate averages you'll need to fetch all the results over the network
>> and calculate them yourself.
>>
>> wunder
>>
>> On Jul 7, 2012, at 9:05 AM, Jeremy Branham wrote:
>>
>> > I’m sorry – I sent this email before I was confirmed in the group, so I
>> don’t know if anyone sent a reply =\
>> >
>> > ______________________________**____________
>> >
>> > Hello -
>> > I’m not sure If this is an appropriate use for Solr, but I want to stay
>> away from a typical DB store for high availability reasons.
>> >
>> > I am storing documents that may have a common value for a field we’ll
>> call “category”.
>> > In another field there will be an integer field we’ll call “rating”.
>> >
>> > I would like to group the documents on the “category” field and display
>> the average “rating” per group.
>> >
>> > The stats component lets me get the avg rating, but when I collapse the
>> results into groups it gives me the average for the entire collection,
>> rather than for the specific group.
>> >
>> > Am I going about this wrong?
>> > Is it possible to get the desired outcome with a single query?
>> >
>> > I’d appreciate any insight!
>> > Thank you,
>> >
>> >
>> >
>> > Jeremy Branham
>> > Software Engineer
>> > http://LinkedIn.com/in/**JeremyBranham<http://LinkedIn.com/in/JeremyBranham>
>> > http://jeremybranham.**wordpress.com/<http://jeremybranham.wordpress.com/>
>> > http://Zeroth.biz
>>
>>
>>
>>
>>
>
Re: Grouping and Averages
Posted by Jeremy Branham <jd...@hotmail.com>.
Thanks for the replies.
I may be able to simplify my requirements.
In my application, the number of documents per group indicate popularity.
If I could sort the groups descending by the document count, then using the
stats component + filter I could query each group to get avg value for a
field.
Though I dont see how to sort the groups by document count.
I thought maybe a pseudo field with a functional query would return a
document element but my tests failed.
Its a bit of a challenge to switch my thought process from SQL to Solr.
Jeremy Branham
Software Engineer
http://LinkedIn.com/in/JeremyBranham
http://jeremybranham.wordpress.com/
http://Zeroth.biz
-----Original Message-----
From: Jason Rutherglen
Sent: Saturday, July 07, 2012 2:45 PM
To: solr-user@lucene.apache.org
Subject: Re: Grouping and Averages
Average should be doable in Solr, maybe not today, not sure. Median is the
challenge :) Try Hive.
On Sat, Jul 7, 2012 at 3:34 PM, Walter Underwood
<wu...@wunderwood.org>wrote:
> It sounds like you need a database for analytics, not a search engine.
>
> Solr cannot do aggregates like that. It can select and group, but to
> calculate averages you'll need to fetch all the results over the network
> and calculate them yourself.
>
> wunder
>
> On Jul 7, 2012, at 9:05 AM, Jeremy Branham wrote:
>
> > I’m sorry – I sent this email before I was confirmed in the group, so I
> don’t know if anyone sent a reply =\
> >
> > __________________________________________
> >
> > Hello -
> > I’m not sure If this is an appropriate use for Solr, but I want to stay
> away from a typical DB store for high availability reasons.
> >
> > I am storing documents that may have a common value for a field we’ll
> call “category”.
> > In another field there will be an integer field we’ll call “rating”.
> >
> > I would like to group the documents on the “category” field and display
> the average “rating” per group.
> >
> > The stats component lets me get the avg rating, but when I collapse the
> results into groups it gives me the average for the entire collection,
> rather than for the specific group.
> >
> > Am I going about this wrong?
> > Is it possible to get the desired outcome with a single query?
> >
> > I’d appreciate any insight!
> > Thank you,
> >
> >
> >
> > Jeremy Branham
> > Software Engineer
> > http://LinkedIn.com/in/JeremyBranham
> > http://jeremybranham.wordpress.com/
> > http://Zeroth.biz
>
>
>
>
Re: Grouping and Averages
Posted by Jason Rutherglen <ja...@gmail.com>.
Average should be doable in Solr, maybe not today, not sure. Median is the
challenge :) Try Hive.
On Sat, Jul 7, 2012 at 3:34 PM, Walter Underwood <wu...@wunderwood.org>wrote:
> It sounds like you need a database for analytics, not a search engine.
>
> Solr cannot do aggregates like that. It can select and group, but to
> calculate averages you'll need to fetch all the results over the network
> and calculate them yourself.
>
> wunder
>
> On Jul 7, 2012, at 9:05 AM, Jeremy Branham wrote:
>
> > I’m sorry – I sent this email before I was confirmed in the group, so I
> don’t know if anyone sent a reply =\
> >
> > __________________________________________
> >
> > Hello -
> > I’m not sure If this is an appropriate use for Solr, but I want to stay
> away from a typical DB store for high availability reasons.
> >
> > I am storing documents that may have a common value for a field we’ll
> call “category”.
> > In another field there will be an integer field we’ll call “rating”.
> >
> > I would like to group the documents on the “category” field and display
> the average “rating” per group.
> >
> > The stats component lets me get the avg rating, but when I collapse the
> results into groups it gives me the average for the entire collection,
> rather than for the specific group.
> >
> > Am I going about this wrong?
> > Is it possible to get the desired outcome with a single query?
> >
> > I’d appreciate any insight!
> > Thank you,
> >
> >
> >
> > Jeremy Branham
> > Software Engineer
> > http://LinkedIn.com/in/JeremyBranham
> > http://jeremybranham.wordpress.com/
> > http://Zeroth.biz
>
>
>
>
Re: Grouping and Averages
Posted by Walter Underwood <wu...@wunderwood.org>.
It sounds like you need a database for analytics, not a search engine.
Solr cannot do aggregates like that. It can select and group, but to calculate averages you'll need to fetch all the results over the network and calculate them yourself.
wunder
On Jul 7, 2012, at 9:05 AM, Jeremy Branham wrote:
> I’m sorry – I sent this email before I was confirmed in the group, so I don’t know if anyone sent a reply =\
>
> __________________________________________
>
> Hello -
> I’m not sure If this is an appropriate use for Solr, but I want to stay away from a typical DB store for high availability reasons.
>
> I am storing documents that may have a common value for a field we’ll call “category”.
> In another field there will be an integer field we’ll call “rating”.
>
> I would like to group the documents on the “category” field and display the average “rating” per group.
>
> The stats component lets me get the avg rating, but when I collapse the results into groups it gives me the average for the entire collection, rather than for the specific group.
>
> Am I going about this wrong?
> Is it possible to get the desired outcome with a single query?
>
> I’d appreciate any insight!
> Thank you,
>
>
>
> Jeremy Branham
> Software Engineer
> http://LinkedIn.com/in/JeremyBranham
> http://jeremybranham.wordpress.com/
> http://Zeroth.biz
Re: Grouping and Averages
Posted by Jeremy Branham <jd...@hotmail.com>.
Thanks Jack!
Jeremy Branham
Software Engineer
http://LinkedIn.com/in/JeremyBranham
http://jeremybranham.wordpress.com/
http://Zeroth.biz
-----Original Message-----
From: Jack Krupansky
Sent: Saturday, July 07, 2012 11:16 AM
To: solr-user@lucene.apache.org
Subject: Re: Grouping and Averages
You can always check the Lucene/Solr archives:
http://mail-archives.apache.org/mod_mbox/lucene-solr-user/
Your message is here:
http://mail-archives.apache.org/mod_mbox/lucene-solr-user/201207.mbox/%3CBAY170-DS274C673A7C82D716E7E000BAED0%40phx.gbl%3E
It does not yet appear to have any responses.
-- Jack Krupansky
-----Original Message-----
From: Jeremy Branham
Sent: Saturday, July 07, 2012 11:05 AM
To: solr-user@lucene.apache.org
Subject: Grouping and Averages
I’m sorry – I sent this email before I was confirmed in the group, so I don’t
know if anyone sent a reply =\
__________________________________________
Hello -
I’m not sure If this is an appropriate use for Solr, but I want to stay away
from a typical DB store for high availability reasons.
I am storing documents that may have a common value for a field we’ll call
“category”.
In another field there will be an integer field we’ll call “rating”.
I would like to group the documents on the “category” field and display the
average “rating” per group.
The stats component lets me get the avg rating, but when I collapse the
results into groups it gives me the average for the entire collection,
rather than for the specific group.
Am I going about this wrong?
Is it possible to get the desired outcome with a single query?
I’d appreciate any insight!
Thank you,
Jeremy Branham
Software Engineer
http://LinkedIn.com/in/JeremyBranham
http://jeremybranham.wordpress.com/
http://Zeroth.biz
Re: Grouping and Averages
Posted by Jack Krupansky <ja...@basetechnology.com>.
You can always check the Lucene/Solr archives:
http://mail-archives.apache.org/mod_mbox/lucene-solr-user/
Your message is here:
http://mail-archives.apache.org/mod_mbox/lucene-solr-user/201207.mbox/%3CBAY170-DS274C673A7C82D716E7E000BAED0%40phx.gbl%3E
It does not yet appear to have any responses.
-- Jack Krupansky
-----Original Message-----
From: Jeremy Branham
Sent: Saturday, July 07, 2012 11:05 AM
To: solr-user@lucene.apache.org
Subject: Grouping and Averages
I’m sorry – I sent this email before I was confirmed in the group, so I don’t
know if anyone sent a reply =\
__________________________________________
Hello -
I’m not sure If this is an appropriate use for Solr, but I want to stay away
from a typical DB store for high availability reasons.
I am storing documents that may have a common value for a field we’ll call
“category”.
In another field there will be an integer field we’ll call “rating”.
I would like to group the documents on the “category” field and display the
average “rating” per group.
The stats component lets me get the avg rating, but when I collapse the
results into groups it gives me the average for the entire collection,
rather than for the specific group.
Am I going about this wrong?
Is it possible to get the desired outcome with a single query?
I’d appreciate any insight!
Thank you,
Jeremy Branham
Software Engineer
http://LinkedIn.com/in/JeremyBranham
http://jeremybranham.wordpress.com/
http://Zeroth.biz