jodu jodu - 3 years ago 164
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 :

  1. Get all rows with option_type=”foo” and expiration_date > “2017-12-01”

  2. Get all rows with option_type=”bar” and expiration_date > “2017-12-01”

  3. 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.

  4. 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?


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

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