MySQL查询中FIRST_VALUE与LAST_VALUE失效及关联子查询报错求助

原因分析 #

你当前的查询里，GROUP BY Ticker会把每个资产的行合并成一行，而窗口函数FIRST_VALUE/LAST_VALUE的默认窗口范围是从分组起始到当前行，单一行的情况下自然只能返回同一个值，完全没起到取首尾价格的作用。

修正方案 #

方案A：用行号标记首尾行后聚合

先给每个资产的记录按时间排序标记行号，再通过聚合取行号为1的价格（最早时间的开盘价，最晚时间的收盘价）：

SELECT 
  `Ticker`,
  MAX(CASE WHEN rn_asc = 1 THEN `Price` END) AS open_p,
  MIN(`Price`) AS min_price,
  MAX(`Price`) AS max_price,
  MAX(CASE WHEN rn_desc = 1 THEN `Price` END) AS close_p
FROM (
  SELECT 
    `Ticker`,
    `Price`,
    -- 按时间升序标记，1为最早的记录
    ROW_NUMBER() OVER(PARTITION BY `Ticker` ORDER BY `Time` ASC) AS rn_asc,
    -- 按时间降序标记，1为最晚的记录
    ROW_NUMBER() OVER(PARTITION BY `Ticker` ORDER BY `Time` DESC) AS rn_desc
  FROM `Hist_price`
  WHERE `Time` >= 1683212332325 AND `Time` < 2683222332325
) t
GROUP BY `Ticker`;

方案B：指定窗口函数的完整范围

给窗口函数明确指定范围为整个分组，再用DISTINCT去重（因为窗口函数会给每一行都生成结果）：

SELECT DISTINCT
  `Ticker`,
  FIRST_VALUE(`Price`) OVER(
    PARTITION BY `Ticker` 
    ORDER BY `Time` ASC 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS open_p,
  MIN(`Price`) OVER(PARTITION BY `Ticker`) AS min_price,
  MAX(`Price`) OVER(PARTITION BY `Ticker`) AS max_price,
  LAST_VALUE(`Price`) OVER(
    PARTITION BY `Ticker` 
    ORDER BY `Time` ASC 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS close_p
FROM `Hist_price`
WHERE `Time` >= 1683212332325 AND `Time` < 2683222332325;

原因分析 #

SQL的执行顺序是先执行FROM/WHERE/GROUP BY，再执行SELECT生成别名，所以你在同层SELECT的子查询里直接引用first_trade/last_trade这些别名时，SQL还没生成这些值，自然报错。

修正方案 #

先把分组后的结果放到子查询里，再通过关联原表获取对应trade_ID的价格：

SELECT 
  t.`Ticker`,
  t.first_trade,
  t.min_price,
  t.max_price,
  t.last_trade,
  hp_open.`Price` AS open_price,
  hp_close.`Price` AS close_price
FROM (
  -- 先分组获取每个资产的首尾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
  FROM `Hist_price`
  WHERE `Time` >= 1683469229380 AND `Time` < 1683469349380
  GROUP BY `Ticker`
) t
-- 关联原表取开盘价
LEFT JOIN `Hist_price` hp_open 
  ON hp_open.`Ticker` = t.`Ticker` AND hp_open.`trade_ID` = t.first_trade
-- 关联原表取收盘价
LEFT JOIN `Hist_price` hp_close 
  ON hp_close.`Ticker` = t.`Ticker` AND hp_close.`trade_ID` = t.last_trade;

内容的提问来源于stack exchange，提问作者Mat