如何在单条MySQL查询中按分钟分组获取价格最值及首尾值

针对你的需求，我们可以分两种情况来实现——如果你的MySQL版本是8.0及以上，窗口函数是最简洁高效的方案；如果是5.x版本，就用子查询关联的方式来完成。

方案一：MySQL 8.0+ 窗口函数实现 #

窗口函数可以直接在分组内对记录排序，轻松提取首尾价格，同时计算极值：

SELECT DISTINCT
    DATE_FORMAT(date, '%Y-%m-%d %H:%i') AS minute_group,
    FIRST_VALUE(price) OVER (
        PARTITION BY DATE_FORMAT(date, '%Y-%m-%d %H:%i') 
        ORDER BY date ASC
    ) AS firstPrice,
    LAST_VALUE(price) OVER (
        PARTITION BY DATE_FORMAT(date, '%Y-%m-%d %H:%i') 
        ORDER BY date ASC
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    ) AS lastPrice,
    MIN(price) OVER (
        PARTITION BY DATE_FORMAT(date, '%Y-%m-%d %H:%i')
    ) AS minPrice,
    MAX(price) OVER (
        PARTITION BY DATE_FORMAT(date, '%Y-%m-%d %H:%i')
    ) AS maxPrice
FROM products;

方案二：MySQL 5.x 兼容方案（无窗口函数） #

通过子查询先计算分组的极值，再关联查询首尾价格：

SELECT
    t.minute_group,
    (SELECT price FROM products 
     WHERE DATE_FORMAT(date, '%Y-%m-%d %H:%i') = t.minute_group 
     ORDER BY date ASC LIMIT 1) AS firstPrice,
    (SELECT price FROM products 
     WHERE DATE_FORMAT(date, '%Y-%m-%d %H:%i') = t.minute_group 
     ORDER BY date DESC LIMIT 1) AS lastPrice,
    t.minPrice,
    t.maxPrice
FROM (
    SELECT
        DATE_FORMAT(date, '%Y-%m-%d %H:%i') AS minute_group,
        MIN(price) AS minPrice,
        MAX(price) AS maxPrice
    FROM products
    GROUP BY minute_group
) t;

关键逻辑说明 #

• 分组依据：DATE_FORMAT(date, '%Y-%m-%d %H:%i')将时间截断到分钟级别，确保同一分钟的记录归为一组。
• 起始价格（firstPrice）：取分组内时间最早的记录价格，窗口函数用FIRST_VALUE，子查询用ORDER BY date ASC LIMIT 1。
• 结束价格（lastPrice）：窗口函数中LAST_VALUE需要指定ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING，否则默认范围是从分组开头到当前行，无法取到最后一条；子查询则用ORDER BY date DESC LIMIT 1直接取最晚记录。
• 极值计算：直接用MIN()和MAX()聚合函数在分组内统计即可。

执行任意一种方案，都能得到你预期的输出结果：

firstPrice lastPrice minPrice maxPrice #

1 3 1 5 --> 分组为(2018-04-27 12:10)
4 3 3 4 --> 分组为(2018-04-27 12:11)

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