MySQL：如何在按天分组的金属价格查询中添加起始价与终价

没问题！要在你现有每日最高最低价的基础上，加上起始价（当日第一条记录的价格）和终价（当日最后一条记录的价格），咱们可以用窗口函数来实现，这里给你两种可行的方案：

方案一：用ROW_NUMBER标记首尾记录再聚合 #

这种方法先给每个金属的每日记录按时间排序编号，再筛选出首尾记录的价格，和最高最低价合并：

WITH ranked_prices AS (
    SELECT 
        metal_type,
        DATE(timestamp) AS price_date,
        price,
        -- 按时间升序编号，1就是当日第一条记录
        ROW_NUMBER() OVER (PARTITION BY metal_type, DATE(timestamp) ORDER BY timestamp ASC) AS rn_asc,
        -- 按时间降序编号，1就是当日最后一条记录
        ROW_NUMBER() OVER (PARTITION BY metal_type, DATE(timestamp) ORDER BY timestamp DESC) AS rn_desc,
        -- 计算当日最高最低价
        MAX(price) OVER (PARTITION BY metal_type, DATE(timestamp)) AS daily_high,
        MIN(price) OVER (PARTITION BY metal_type, DATE(timestamp)) AS daily_low
    FROM metal_prices
)
SELECT 
    metal_type,
    price_date,
    daily_high,
    daily_low,
    -- 取当日第一条记录的价格作为起始价
    MAX(CASE WHEN rn_asc = 1 THEN price END) AS opening_price,
    -- 取当日最后一条记录的价格作为终价
    MAX(CASE WHEN rn_desc = 1 THEN price END) AS closing_price
FROM ranked_prices
GROUP BY metal_type, price_date, daily_high, daily_low
ORDER BY price_date, metal_type;

方案二：用FIRST_VALUE和LAST_VALUE直接取值 #

这种方法更简洁，用窗口函数直接提取首尾价格，搭配DISTINCT去重：

SELECT DISTINCT
    metal_type,
    DATE(timestamp) AS price_date,
    MAX(price) OVER (PARTITION BY metal_type, DATE(timestamp)) AS daily_high,
    MIN(price) OVER (PARTITION BY metal_type, DATE(timestamp)) AS daily_low,
    -- 直接取当日第一条记录的价格
    FIRST_VALUE(price) OVER (PARTITION BY metal_type, DATE(timestamp) ORDER BY timestamp ASC) AS opening_price,
    -- 注意要指定窗口范围为整个分组，否则LAST_VALUE只会取到当前行之前的最后一条
    LAST_VALUE(price) OVER (
        PARTITION BY metal_type, DATE(timestamp) 
        ORDER BY timestamp ASC 
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS closing_price
FROM metal_prices
ORDER BY price_date, metal_type;

两种方案都能满足你的需求，你可以根据自己的习惯选择。如果你的表数据量很大，方案一的性能可能更稳定一些，不过大部分场景下两种方法都能高效运行。

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