如何在含GROUP BY与聚合函数的SQL中按条件选取列

针对5亿行量级的SCHEMA.TRADES表，要按Session分组获取最早DateStart、最晚DateEnd，以及对应Session中TradeID为"."的TradeKP值，最优解决方案如下：

优先方案：条件聚合（单次全表扫描） #

利用条件聚合函数在一次扫描中完成所有计算，性能最优，适合超大规模表：

SELECT
    Session,
    MIN(DateStart) AS EarliestDateStart,
    MAX(DateEnd) AS LatestDateEnd,
    MAX(CASE WHEN TradeID = '.' THEN TradeKP END) AS TargetTradeKP
FROM SCHEMA.TRADES
GROUP BY Session;

原理说明 #

因为每个Session仅存在一行TradeID为"."的数据，MAX(CASE...)只会提取这唯一的TradeKP值，不会出现多值冲突。整个查询仅需扫描一次全表，IO开销最小，是5亿行数据场景下的首选。

备选方案：窗口函数筛选 #

如果数据库支持窗口函数（如PostgreSQL、MySQL 8+、SQL Server），也可以用窗口函数预计算聚合值后筛选目标行，但性能略逊于条件聚合（需两次扫描）：

WITH SessionAgg AS (
    SELECT
        Session,
        DateStart,
        DateEnd,
        TradeID,
        TradeKP,
        MIN(DateStart) OVER (PARTITION BY Session) AS EarliestDateStart,
        MAX(DateEnd) OVER (PARTITION BY Session) AS LatestDateEnd
    FROM SCHEMA.TRADES
)
SELECT
    Session,
    EarliestDateStart,
    LatestDateEnd,
    TradeKP AS TargetTradeKP
FROM SessionAgg
WHERE TradeID = '.';

性能优化建议 #

为进一步提升查询速度，可创建复合索引避免回表：

• PostgreSQL/SQL Server：

CREATE INDEX idx_trades_session_agg ON SCHEMA.TRADES (Session) INCLUDE (DateStart, DateEnd, TradeID, TradeKP);

• MySQL：

CREATE INDEX idx_trades_session_agg ON SCHEMA.TRADES (Session, DateStart, DateEnd, TradeID, TradeKP);

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