Re: How to substract numeric value stored in 2 documents related by correlation id one-to-one

[Kevin Risden <ri...@avalonconsult.com>]

The Parallel SQL support for what you are asking for doesn't exist quite
yet. The use case you described is close to what I was envisioning for the
Solr SQL support. This would allow full text searches and then some
analytics on top of it (like call duration).

I'm not sure if subtracting fields (c2.time-c1.time) is supported in
streaming expressions yet. The leftOuterJoin is but not sure about
arbitrary math equations. The Parallel SQL side has an issue w/ 1!=0 right
now so I'm guessing adding/subtracting is also out for now.

The ticket you will want to follow is SOLR-8593 (
https://issues.apache.org/jira/browse/SOLR-8593) This is the Calcite
integration and should enable a lot more SQL syntax as a result.

Kevin Risden
Apache Lucene/Solr Committer
Hadoop and Search Tech Lead | Avalon Consulting, LLC
<http://www.avalonconsult.com/>
M: 732 213 8417
LinkedIn <http://www.linkedin.com/company/avalon-consulting-llc> | Google+
<http://www.google.com/+AvalonConsultingLLC> | Twitter
<https://twitter.com/avalonconsult>

-------------------------------------------------------------------------------------------------------------
This message (including any attachments) contains confidential information
intended for a specific individual and purpose, and is protected by law. If
you are not the intended recipient, you should delete this message. Any
disclosure, copying, or distribution of this message, or the taking of any
action based on it, is strictly prohibited.

On Wed, Oct 19, 2016 at 8:23 AM, <ka...@kahle.cz> wrote:

> Hello,
> I have 2 documents recorded at request or response of a service call  :
> Entity Request
>  {
>   "type":"REQ",
>   "reqid":"MES0",
>    "service":"service0",
>    "time":1,
>  }
> Entity response
>  {
>   "type":"RES",
>   "reqid":"MES0",
>    "time":10,
>  }
>
> I need to create following statistics:
> Total service call duration for each call (reqid is unique for each
> service call) :
> similar to query :
> select c1.reqid,c1.service,c1.time as REQTime, c2.time as RESTime ,
> c2.time - c1.time as TotalTime from collection c1 left join collection c2
> on c1.reqid = c2.reqid and c2.type = 'RES'
>
>  {
>    "reqid":"MES0",
>    "service":service0,
>    "REQTime":1,
>    "RESTime":10,
>    "TotalTime":9
>  }
>
> Average service call duration :
> similar to query :
> select c1.service,  avg(c2.time - c1.time) as AvgTime, count(*) from
> collection c1 left join collection c2 on c1.reqid = c2.reqid and c2.type =
> 'RES' group by c1.service
>
>  {
>    "service":service0,
>    "AvgTime":9,
>    "Count": 1
>  }
>
> I Tried to find solution in archives, I experimented  with !join,
> subquery, _query_ etc. but not succeeded..
> I can probably use streaming and leftOuterJoin, but in my understanding
> this functionality is not ready for production.
> Is SOLR capable to fulfill these use cases?  What are the key functions to
> focus on ?
>
> Thanks' Pavel
>
>
>
>
>
>
>
>
>