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 :
SELECT a.underlying,
a.expiration_date,
(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