I have the following postgres table with about 1 million rows.
id | underlying | option_symbol | option_type | expiration_date | strike | mid |
I need to perform the following in under a few seconds :
(a.strike - b.strike) as spread_profit,
(a.mid - b.mid) as spread_distance,
(spread_distance - spread_profit) as spread_max_loss,
(spread_profit / spread_max_loss) as spread_profit_ratio
FROM option_data a
JOIN option_data b ON a.underlying = b.underlying AND a.expiration_date = b.expiration_date
WHERE a.option_type = 'foo'
AND b.option_type = 'bar'
AND a.expiration_date <= '2017-12-01'
AND b.expiration_date <= '2017-12-01'
GROUP BY a.underlying, a.expiration_date
ORDER BY spread_profit_ratio DESC
I'm not 100% certain of what you're trying to do, but the answer probably looks something like this:
select a.underlying , a.expiration_date , max(a.strike - b.strike) as spread from mytable a join mytable b on a.underlying = b.underlying and a.expiration_date = b.expiration_date where a.option_type = 'foo' and b.option_type = 'bar' group by a.underlying, a.expiration_date