I have a table (Hist_price
) with Time
, Price
and Ticker
. Basically a table of historical prices for a list of assets (Ticker
)
I want to calculate my own candle sticks. First method tried (and failed) was:
SELECT `Ticker`,
FIRST_VALUE(`Price`) OVER(PARTITION BY `Ticker` ORDER BY `Time` ) AS open_p,
MIN(`Price`) AS min_price,
MAX(`Price`) AS max_price,
LAST_VALUE(`Price`) OVER(PARTITION BY `Ticker`ORDER BY `Time` ) AS close_p
FROM `Hist_price`
WHERE `Time` >= 1683212332325 AND `Time` < 2683222332325
GROUP BY `Ticker`;
But it did not work. The FIRST_VALUE and LAST_VALUE returned the same value… Basically FIRST_VALUE and LAST_VALUE did not work as I thought they did….
second try is to do it as advised with (I will find the open and close by looking through their trade_id)
SELECT `Ticker`,
MIN(`trade_ID`) AS first_trade,
MIN(`Price`) AS min_price,
MAX(`Price`) AS max_price,
MAX(`trade_ID`) AS last_trade,
(SELECT `Price` FROM `Hist_price` WHERE `trade_ID` = first_trade) AS open_price,
(SELECT `Price` FROM `Hist_price` WHERE `trade_ID` = last_trade) AS close_price
FROM `Hist_price`
WHERE `Time` >= 1683469229380 AND `Time` < 1683469349380
GROUP BY `Ticker`;
But I seem to struggle with that correlated subquery :
#1247 – Reference ‘first_trade’ not supported (reference to group function)
#1247 – Reference ‘last_trade’ not supported (reference to group function)
Any help , please? I am almost there…
3
Answers
You can try using the window functions only as follows :
Demo here
You cannot use the alias to an aggregate value as a reference in a subquery in the SELECT list but you can use the aggregate function directly –
Put the calculation of the first and last trades in a CTE. Join that with the
Hist_price
table twice to get the first and last prices.