skip to Main Content

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


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