DateDiff
Hey All, Using solr I want to get the difference between two dates, is this possible? Something similar to SELECT DateDiff(d, GetDate(), date_Field) as Diff FROM MyTable
Group By and Sum
Hello All, 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: 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) 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 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. Thanks, Adam
RE: Group By and Sum
I agree however the powers that be, being upper management, have decided that we need to switch to SOLR, JSONiq and JavaScript MVC for all our reporting needs. I would love to just keep using the SQL DB that we have been using but alas I am not allowed to. Thanks, Adam -Original Message- From: Walter Underwood [mailto:wun...@wunderwood.org] Sent: Monday, March 18, 2013 11:58 AM To: solr-user@lucene.apache.org Subject: Re: Group By and Sum You should use a relational database. Solr is not really designed for this kind of query. wunder On Mar 18, 2013, at 9:48 AM, Adam Harris wrote: > Hello All, > > 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: > > > > > > > >required="true"/> > > > >required="true"/> > >required="true"/> > > > >required="true"/> > >required="true"/> > > > > > > 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) > > 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 > > 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. > > Thanks, > Adam -- Walter Underwood wun...@wunderwood.org
RE: Group By and Sum
Alan - Thank you, with that information I can manage to get the correct data from SOLR and then aggregate it in JSONiq. Eric / Walter - I totally agree that SOLR is more than likely not the solution we should be using however all I can do is hope that upper management comes to their senses and realizes this. All - Thank you all for your help and suggestions. It's greatly appreciated. Thanks, Adam -Original Message- From: Erick Erickson [mailto:erickerick...@gmail.com] Sent: Monday, March 18, 2013 8:56 PM To: solr-user@lucene.apache.org Subject: Re: Group By and Sum Second Walter's comment. Make really, _really_ sure that "the powers that be" recognize that they're asking for something unreasonable and it'll cost them dearly to get it. Best Erick On Mon, Mar 18, 2013 at 12:04 PM, Alan Woodward wrote: > Hi Adam, > > Have a look at the stats component: > http://wiki.apache.org/solr/StatsComponent. In your case, I think > you'd need to add an extra field for your month, and then run a query > filtered by your date range with stats.field=NetSales, > stats.field=TransCount, and stats.facet=month. > > Make sure you use Solr 4.2 for this, by the way, as it's massively > faster > - I've found stats queries over ~500,000 documents dropping from 60 > seconds to 2 seconds with an upgrade from 4.0 to 4.2. > > Alan Woodward > www.flax.co.uk > > > On 18 Mar 2013, at 16:48, Adam Harris wrote: > > > Hello All, > > > > 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: > > > > > > > > > > > >required="true"/> > > > >stored="true" required="true"/> > > > >stored="true" /> > > > >required="true"/> > > > >stored="true" required="true"/> > > > >> stored="true" /> > > > >required="true"/> > > > >required="true"/> > > > > > > > > > > > > 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) > > > > 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 > > > > 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. > > > > Thanks, > > Adam > >
