How to perform these SOLR queries?

Question

I have an indexed data(indexed using solrj from rdbms) having fields related to banking such as(sample):customerid, cust_name, accountno, amount, positions, pos_value, EOD_value etc

Now i want to do some searching on the data and search queries:

• top 10 stocks/positions(based on stock value)
• top 5 customers in decreasing order of amount in bank
• which stock gained the max in a day (and the stock details)
• lowest value of a stock in a particular time frame

How is it possible to query for the above in SOLR I did read Function Query and solr Plugins but could not find much useful information...

Can we perform faceting on fields(amount,stock value etc) using some maths operations like average,sum etc...

I want to use velocity UI for the following search and what customization to its search box would be required? Any idea???

Answer 1

We have implemented the same thing in one of our application.

In Browse.vm under navigators "div" we have created our custom facet and when we click on that facet it recreats the url along with the parameter mentioned by "phisch" in his answer.

Example: We have created a link called "Top 10 Stocks" in facet section on UI, and when we click it. we created a url adding parameters as

q=:&sort=stock_value DESC&rows=10

Please try this out at your end as it is working fine at my end. Sorry I cannot share the code as it is client confidential.

Answer 2

Solr is a high performance text search engine, bases on Lucene, an excellent token matching and scoring library. This said, the kind of queries you want to run will certainly work in one way or an other with Solr, but you will have to provide Solr will all the data you want to search on. Solr will not compute min, max average values for you. It's job is it to find, rank and sort as fast as possible in previously computed values.

The fields you have listed might not give you all the details you are looking for. You will need to index some more.

If you have the data you are looking for in your index, the following queries might get the answer you are looking for or should give you a hint on how to state them.

top 10 stocks/positions

q=*:* sort=stock_value DESC rows=10

This requires that stock_value is numeric and has the latest stock price in the index.

top 5 customers

This is pretty similar.

q=*:* sort=account_value DESC rows=5

which stock gained the max in a day

You will need to index the gain per day

q=date:1995-12-31T23:59:59.999Z sort=stock_gain  DESC rows=1

lowest value of a stock in a particular time frame

q=symbol:abc123 date:[1995-12-31T23:59:59.999Z TO 2007-03-06T00:00:00Z]  sort=stock_value ASC rows=1

See Solr Query Syntax for Details on Date Queries