Bobface - 2 months ago 5x

SQL Question

My current query looks like this:

`SELECT * FROM `Entrys` WHERE `Timestamp` > 1469308755 AND `Timestamp` < 1469308765 AND (`Exchange` = 1 OR `Exchange` = 2) AND `Market` = 1`

It gives me the following result:

For each timestamp value (1469308756 and 1469308761) , I want to calculate the spreads between the prices like this:

`For each timestamp-value`

For each Exchange

For each Other-Exchange

Divide Exchanges price where type = 2 by Other-Exchanges price where type = 1

This is the best I can explain it. If you don't understand it please leave a comment and I'll try to explain it better.

With the above data it should work like this:

Format: EnId.Field

`943.Price / 940.Price`

944.Price / 939.Price

961.Price / 985.Price

962.Price / 957.Price

The calculated numbers should be the output of the query.

My SQL skills are way to low for that. Is it even possible to do such calculation inside a single query?

Answer

You need to use a `JOIN`

. A JOIN is useful when you want data from two different rows to be referenced in the same expression (either an expression in the WHERE clause or an expression in the select-list).

So you need to join a row to another row that satisfies these conditions:

- the same timestamp
- a different exchange
- the first row is type=2 and the second row is type=1

When you're doing a self-join like this one, you must use table aliases. I will use "numerator" and "denominator" for the two rows.

```
SELECT numerator.timestamp, numerator.price / denominator.price AS spread
FROM Entrys AS numerator
JOIN Entries AS denominator ON numerator.timestamp=denominator.timestamp
AND numerator.exchange <> denominator.exchange
AND numerator.type = 2 AND denominator.type = 1;
```

Source (Stackoverflow)

Comments