SOLR Group BY和SUM [英] SOLR Group BY and SUM

问题描述

大家好，



***更新：

Hello All,

*** Updates:

1) To clarify this is an Apache SOLR project that will be consumed from JavaScript MVC using JSONiq as the middle man.

2) I have found a solution which i added and accepted.

相当困在这里，我是希望你能成为帮助我的人。我正在使用SOLR和JSONiq这对我来说是全新的，即使是最简单的事情也只是逃避我。我很熟悉SQL但是这个简单的要求似乎让我感到震惊。我会跳进去。



这是我''核心''的架构：

Pretty stuck here and I am hoping you might be the person to help me out. I am working with SOLR and JSONiq which are totally new to me and doing even the simplest of things is just escaping me. I know SQL pretty well however this simple requirement seems escape me. I''ll jump right into it.

Here is the schema of my ''Core'':

<fields>

   <field name="BuID" type="int" indexed="true" stored="true" required="true"/>
   <field name="BusinessDate" type="text_general" indexed="true" stored="true" required="true"/>
   <field name="BusinessDateTime" type="date" indexed="true" stored="true" />
   <field name="Name" type="text_general" indexed="true" stored="true" required="true"/>
   <field name="BeginTime" type="text_general" indexed="true" stored="true" required="true"/>
   <field name="BeginDateTime" type="date" indexed="true" stored="true" />
   <field name="TransCount" type="int" indexed="true" stored="true" required="true"/>
   <field name="NetSales" type="float" indexed="true" stored="true" required="true"/>

</fields>

我需要按BusinessDateTime的月份进行分组，并在给定的日期范围内总结NetSales和TransCount。现在如果这是SQL我会恰到好处

I need to group by the month of BusinessDateTime and sum up NetSales and TransCount for a given date range. Now if this were SQL i would just right

SELECT sum(TransCount), sum(NetSales)
FROM Core
WHERE BusinessDateTime BETWEEN '2012/04/01' AND '2013/04/01'
GROUP BY MONTH(BusinessDateTime)

但是对于SOLR和/或JSONiq而言，当然没有什么是这么简单的。我曾尝试搞乱Facet和Group，但他们似乎从未像我想要的那样工作。例如，这是我正在玩的一个查询：

But ofcourse nothing is this simple with SOLR and/or JSONiq. I have tried messing around with Facet and Group but they never seem to work the way i want them to. For example here is a query i am currently playing with:

?wt=json
&indent=true
&q=*:*
&rows=0
&facet=true
&facet.date=BusinessDateTime
&facet.date.start=2012-02-01T00:00:01Z
&facet.date.end=2013-02-01T23:59:59Z
&facet.date.gap=%2B1MONTH
&group=true
&group.field=BusinessDateTime
&group.facet=true
&group.field=NetSales

现在facet工作正常，但它返回了文档的数量，但我需要NetSales和TransCount字段的总和。



我们将非常感谢任何帮助或建议。

此外，如果我遗漏了任何内容，请随时发表评论，以便我提供更多详细信息。



谢谢，

Adam

Now the facet is working properly however it is returning the count of the documents however i need the sum of the NetSales and the TransCount fields instead.

Any help or suggestions would be greatly appreciated.
Also, if i left anything out please feel free to post a comment so i can provide more detail.

Thanks,
Adam

推荐答案

在我的老板和SOLR的帮助下疯狂搜索米病态清单我已设法提出一个解决方案，我想发布，以防将来有人需要完成类似的事情。



我最后的结果正在使用SOLR统计数据与facet相结合，以获得每天的NetSales和TransCount。

Well after searching like crazy and with the help of my boss and the SOLR mailing list i have managed to come up with a solution that i would like to post in case someone in the future needs to accomplish a similar thing.

What I ended up doing is using SOLR stats combined with facets to get the NetSales and TransCount for each day.

wt=json
&indent=true
&q=*:*
&fq=BusinessDateTime:[2012-03-01T00:00:0Z TO 2013-03-01T23:59:5Z]
&rows=0
&stats=on
&stats.field=NetSales
&stats.field=TransCount
&stats.facet=BusinessDateTime

然后在JSONiq中我使用这个函数转换数据：

Then in JSONiq i transform the data using this function:

import module namespace functx = "http://www.functx.com/";

variable

t：= [data];

let

t := [data]; let

stats：=

这篇关于SOLR Group BY和SUM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！