Extracting data based on different criteria

Question

So I am trying to extract the date,strike price, and ask price from a data set given a particular date for each month in 2015, where additional unique restriction apply to the strike price and we want the largest strike price with its corresponding ask price. For instance, on date A, we want max(strike price <= x1), and max(strike price<=x2) on date B. So I am thinking to write 10 SELECT statements to implement this, but this seems to be inefficient. Heres a sample query I wrote:

SELECT currentdate,max(strike),ask
FROM opt
WHERE currentdate between '2015-01-16' and '2015-01-16'
AND T='P'
AND strike <=191.55;

And when I run this in impala, I have the error:

Starting Impala Shell without Kerberos authentication ERROR: AnalysisException: select list expression not produced by aggregation output (missing from GROUP BY clause?): currentdate

I apologize for the bad formatting, not very familiar with Stackover.

That question should not be tagged impala. You would get the same kind of syntax error with any SQL database. Learn SQL, that's a good investment for your career... — Samson Scharfrichter
BTW there should be no need to run 10 different queries with 10 different filters on strike if Impala supports the case syntax (it's been a long time since I last used that particular tool...) > try 10 different columns such as max(case when strike <=191.55 then strike else null end) -- just one full scan. — Samson Scharfrichter

Deef Deef · Accepted Answer · 2018-04-16T08:56:45

If your expected output is a list of dates with the maximum strike on that day and the ask, then you need to group by the currentdate field, because otherwise the query engine doesn't understand you want the max of a particular day. The query as you have it now can't run because the max isn't available if you don't provide multiple records (without a group, your recordset is individual rows, not summarised rows).

You're missing a Group By:

SELECT currentdate,max(strike),ask
FROM opt
WHERE currentdate between '2015-01-16' and '2015-01-16'
AND T='P'
AND strike <=191.55
GROUP BY currentdate;

This will also return an error:

But you'll also need to provide an aggregation statement for the ask column. This could be a maximum, average, min, depending on your exact use case.

SELECT currentdate,max(strike),min(ask)
FROM opt
WHERE currentdate between '2015-01-16' and '2015-01-16'
AND T='P'
AND strike <=191.55
GROUP BY currentdate;

Extracting data based on different criteria

1 Answers