Question 138720 in Mysql Question posted in
The official MySQL documentation can be found here.

Does FIRST_VALUE and LAST_VALUE fail in MySQL query?

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…

Tags:

3

Answers


  1. 0

    You can try using the window functions only as follows :

    with cte as (
  SELECT `Ticker`,
  ROW_NUMBER() over (partition by `Ticker` order by `Time` desc) as rn,
  FIRST_VALUE(`Price`) OVER(PARTITION BY `Ticker` ORDER BY `Time` ) AS open_p,
  MIN(`Price`) OVER(PARTITION BY `Ticker` ORDER BY `Time` ) AS min_price,
  MAX(`Price`) OVER(PARTITION BY `Ticker` ORDER BY `Time` ) 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
)
select *
from cte
where rn=1

    Demo here

    Login or Signup to reply.
  2. 0

    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 –

    SELECT
    Ticker,
    (SELECT Price FROM Hist_price WHERE trade_ID = MIN(hp.trade_ID)) AS open_price,
    MIN(Price) AS min_price,
    MAX(Price) AS max_price,
    (SELECT Price FROM Hist_price WHERE trade_ID = MAX(hp.trade_ID)) AS close_price
FROM Hist_price hp
WHERE Time >= 1683469229380 AND Time < 1683469349380
GROUP BY Ticker;
    Login or Signup to reply.
  3. 0

    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.

    WITH cte AS (
    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
)

SELECT cte.*, h1.price AS open_price, h2.price AS close_price
JOIN Hist_price AS h1 ON h1.trade_id = cte.first_trade
JOIN Hist_price AS h2 on h2.trade_id = cte.last_trade;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search