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 Mohamed Azharuddin <az...@eitworks.com> on 2020/01/08 11:07:35 UTC
support need in solr for min and max
Hi team,
We are migrating from mysql to apache solr since solr is fast in searching.
Thank you. We had a scenario to
> *find 1) difference (max-min)*
* 2) with group by date(timeStamp)*
Given below is our mysql table :
[image: Untitled.png]
And mysql query is,
*SELECT Date(eventTimeStamp), MAX(field) - MIN(field) AS Energy FROM
PowerTable GROUP BY DATE(eventTimeStamp);*
will results,
[image: Untitled2.png]
So we have to calculate difference per day, where date column is in
datetime format where we are using result grouping as
*group=true&group.query=eventTimeStamp:[2019-12-11T00:00:00Z TO
2019-12-11T23:59:59Z]&group.query=eventTimeStamp:[2019-12-12T00:00:00Z TO
2019-12-12T23:59:59Z]*
Using Apache solr statistics option, we are able to calculate max and min
for whole result, But we need max and min value per day basis.
[image: Untitled31.png]
When we try to get max and min value per day basis, we are able to fetch
either min or max using following query.
*&group.sort=event1 desc or &group.sort=event1 asc*
[image: Untitled6.png]
But we need both min and max in single query.
So kindly help us to go ahead.
--
Regards,
Azar@EJ
Re: support need in solr for min and max
Posted by Mel Mason <me...@bodleian.ox.ac.uk>.
Try looking at range JSON facets:
https://lucene.apache.org/solr/guide/8_2/json-facet-api.html#range-facet.
If you facet over the eventTimeStamp with a gap of 1 day, you should
then be able to use a sub facet to return a min and max value
(https://lucene.apache.org/solr/guide/8_2/json-facet-api.html#stat-facet-functions)
for each day bucket.
On 08/01/2020 11:07, Mohamed Azharuddin wrote:
> Hi team,
>
> We are migrating from mysql to apache solr since solr is fast in
> searching. Thank you. We had a scenario to
>
> *find 1) difference (max-min)*
>
> * 2) with group by date(timeStamp)*
>
> Given below is our mysql table :
> Untitled.png
>
> And mysql query is,
> */SELECT Date(eventTimeStamp), MAX(field) - MIN(field) AS Energy FROM
> PowerTable GROUP BY DATE(eventTimeStamp);/*
>
> will results,
> Untitled2.png
>
> So we have to calculate difference per day, where date column is in
> datetime format where we are using result grouping as
> */group=true&group.query=eventTimeStamp:[2019-12-11T00:00:00Z TO
> 2019-12-11T23:59:59Z]&group.query=eventTimeStamp:[2019-12-12T00:00:00Z
> TO 2019-12-12T23:59:59Z]/*
>
> Using Apache solr statistics option, we are able to calculate max and
> min for whole result, But we need max and min value per day basis.
> Untitled31.png
>
> When we try to get max and min value per day basis, we are able to
> fetch either min or max using following query.
> */&group.sort=event1 desc or &group.sort=event1 asc/*
> */
> /*
> Untitled6.png
>
> But we need both min and max in single query.
>
> So kindly help us to go ahead.
>
> --
>
> Regards,
> Azar@EJ
>
Re: support need in solr for min and max
Posted by Walter Underwood <wu...@wunderwood.org>.
I hope you do not plan to use Solr as a primary repository. Solr is NOT a database. If you use Solr as a database, you will lose data at some point.
The Solr feature set is very different from MySQL. There is no guarantee that a SQL query can be translated into a Solr query.
wunder
Walter Underwood
wunder@wunderwood.org
http://observer.wunderwood.org/ (my blog)
> On Jan 8, 2020, at 3:07 AM, Mohamed Azharuddin <az...@eitworks.com> wrote:
>
> Hi team,
>
> We are migrating from mysql to apache solr since solr is fast in searching. Thank you. We had a scenario to
>
> find 1) difference (max-min)
> 2) with group by date(timeStamp)
>
> Given below is our mysql table :
>
>
> And mysql query is,
> SELECT Date(eventTimeStamp), MAX(field) - MIN(field) AS Energy FROM PowerTable GROUP BY DATE(eventTimeStamp);
>
> will results,
>
>
> So we have to calculate difference per day, where date column is in datetime format where we are using result grouping as
> group=true&group.query=eventTimeStamp:[2019-12-11T00:00:00Z TO 2019-12-11T23:59:59Z]&group.query=eventTimeStamp:[2019-12-12T00:00:00Z TO 2019-12-12T23:59:59Z]
>
> Using Apache solr statistics option, we are able to calculate max and min for whole result, But we need max and min value per day basis.
>
>
> When we try to get max and min value per day basis, we are able to fetch either min or max using following query.
> &group.sort=event1 desc or &group.sort=event1 asc
>
>
>
> But we need both min and max in single query.
>
> So kindly help us to go ahead.
>
> --
> Regards,
> Azar@EJ