问题

I have a table (Hist_price) with Time, Price, and Ticker. Basically, a table of historical prices for a list of assets (Ticker).

我有一个表格（Hist_price），包含Time、Price和Ticker。基本上是一张包含多个资产（Ticker）的历史价格表。

I want to calculate my own candlesticks. First method tried (and failed) was:

我想计算自己的K线图。首先尝试的方法（失败了）是：

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

但是它没有起作用。FIRST_VALUE 和 LAST_VALUE 返回了相同的值...基本上 FIRST_VALUE 和 LAST_VALUE 没有按我所想的工作...

second try is to do it as advised with (I will find the open and close by looking through their trade_id)

第二次尝试是按照建议进行的（我将通过查看它们的 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...

有什么帮助吗？我快要搞定了...

   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`;

   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`;

答案1

得分: 1

Sure, here is the translated part:

把第一笔和最后一笔交易的计算放在一个CTE中。将其与`Hist_price`表连接两次，以获取第一笔和最后一笔交易的价格。

```SQL
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
    FROM `Hist_price`
    WHERE `Time` >= 1683469229380 AND `Time` < 1683469349380
    GROUP BY Ticker
)

SELECT cte.*, h1.price AS open_price, h2.price AS close_price
FROM cte
JOIN Hist_price AS h1 ON h1.trade_id = cte.first_trade
JOIN Hist_price AS h2 ON h2.trade_id = cte.last_trade;

<details>
<summary>英文:</summary>

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
FROM Hist_price
WHERE Time >= 1683469229380 AND Time < 1683469349380
GROUP BY Ticker
)

SELECT cte.*, h1.price AS open_price, h2.price AS close_price
FROM cte
JOIN Hist_price AS h1 ON h1.trade_id = cte.first_trade
JOIN Hist_price AS h2 on h2.trade_id = cte.last_trade;

</details>



# 答案2
**得分**: 0

你可以尝试仅使用窗口函数如下：

```sql
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

演示在此处

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

答案3

得分: 0

你不能在SELECT列表中的子查询中使用聚合值的别名作为引用，但可以直接使用聚合函数 -

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;

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;