SQL Question
Postgres - data manipulation on a single large table
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 :
- Get all rows with option_type=”foo” and expiration_date > “2017-12-01”
- Get all rows with option_type=”bar” and expiration_date > “2017-12-01”
- For each row of query #1, loop over resulting rows of query #2 with the same underlying and expiration_date, and calculate a “spread” derived from values of strike#1 and strike#2.
- Sort this resulting list of calculated “spreads” and get the best result for each symbol.
how do I perform the "in query" calculations (spread_profit, spread_distance, spread_max_loss, spread_profit_ratio) in the example below?
Example:
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
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Answer Source
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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download