support need in solr for min and max

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

support need in solr for min and max

Mohamed Azharuddin
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
Reply | Threaded
Open this post in threaded view
|

Re: support need in solr for min and max

Walter Underwood
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
[hidden email]
http://observer.wunderwood.org/  (my blog)

> On Jan 8, 2020, at 3:07 AM, Mohamed Azharuddin <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: support need in solr for min and max

Mel Mason
In reply to this post by Mohamed Azharuddin
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
>