How to make operations on rows that resulted from a self join query?-CodePudding

I have a table containing many rows about financial data. Colums are as follows Unixtime,open,high,low,close,timeframe,sourceId.

Given two assets with same timeframe but different sourceId, how to show a table which has unixtime, Asset1open/asset2open,Asset1close/asset2close as columns? Every resulting row should be the result of prices that have the same unixtime, and should be ordered by unixtime asc order. How to do it with a self join?

CodePudding user response：

You don't mention the specific database, so I'll assume this is for Sybase.

You can do:

select
  a.unixtime,
  a.open / b.open,
  a.close / b.close  
from t a
join t b on a.unixtime = b.unixtime and a.timeframe = b.timeframe
where a.sourceid = 123
  and b.sourceid = 456
order by a.unixtime