如何获取分组首尾数据及聚合结果？以股票分钟K线生成为例

嘿，看你已经搞定了最高价和最低价的分组查询，接下来补全开盘价（Open）和收盘价（Close）就完美了！这两个分别对应每个分钟组里第一笔成交的价格和最后一笔成交的价格，我给你两种实用的实现方式：

方法一：关联子查询（适合中小数据量） #

你提到用min(id)获取首个价格的思路完全可行，我们可以扩展这个逻辑同时拿到首尾价格：

SELECT
    -- 按分钟分组，格式转为hhmm
    (time_stamp / 100000) AS [time],
    -- 开盘价：分组内最早成交的价格（用id或时间戳排序）
    (SELECT TOP 1 price FROM [20160104] 
     WHERE symbol = 'AAPL' AND price_field = 0 
       AND (time_stamp / 100000) = main.time_group 
     ORDER BY time_stamp ASC) AS [open],
    MAX(price) AS [high],
    MIN(price) AS [low],
    -- 收盘价：分组内最晚成交的价格
    (SELECT TOP 1 price FROM [20160104] 
     WHERE symbol = 'AAPL' AND price_field = 0 
       AND (time_stamp / 100000) = main.time_group 
     ORDER BY time_stamp DESC) AS [close]
FROM (
    -- 先做基础分组，拿到每个分钟组的时间标识
    SELECT (time_stamp / 100000) AS time_group, price
    FROM [20160104]
    WHERE symbol = 'AAPL' AND price_field = 0
) AS main
GROUP BY main.time_group
ORDER BY main.time_group;

如果你的id是严格按成交时间递增的，也可以把排序字段换成id，效果是一样的。

方法二：窗口函数（大数据量更高效） #

如果你的逐笔成交数据量很大，关联子查询可能会有性能瓶颈，用窗口函数ROW_NUMBER()会更高效——只需要扫描一次数据表就能完成计算：

WITH minute_data AS (
    SELECT
        (time_stamp / 100000) AS [time],
        price,
        -- 按分钟分组，给每笔成交按时间戳升序编号，1就是第一笔（开盘价）
        ROW_NUMBER() OVER (PARTITION BY (time_stamp / 100000) ORDER BY time_stamp ASC) AS rn_asc,
        -- 按分钟分组，给每笔成交按时间戳降序编号，1就是最后一笔（收盘价）
        ROW_NUMBER() OVER (PARTITION BY (time_stamp / 100000) ORDER BY time_stamp DESC) AS rn_desc
    FROM [20160104]
    WHERE symbol = 'AAPL' AND price_field = 0
)
SELECT
    [time],
    -- 提取分组内编号为1的升序价格作为开盘价
    MAX(CASE WHEN rn_asc = 1 THEN price END) AS [open],
    MAX(price) AS [high],
    MIN(price) AS [low],
    -- 提取分组内编号为1的降序价格作为收盘价
    MAX(CASE WHEN rn_desc = 1 THEN price END) AS [close]
FROM minute_data
GROUP BY [time]
ORDER BY [time];

小提示 #

• 一定要确保time_stamp是严格递增的，这样分组后的首尾价格才是准确的开盘/收盘价；
• 如果你的数据库支持FIRST_VALUE()和LAST_VALUE()窗口函数，还能进一步简化写法，比如FIRST_VALUE(price) OVER (PARTITION BY (time_stamp/100000) ORDER BY time_stamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)直接获取开盘价，注意要指定窗口范围避免只取到当前行之前的数据。

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