SQL获取分区首末行数据 筛选同时含买卖操作的交易记录

你现有写法存在两个核心问题：

• 无法直接添加「仅保留同时存在buy、sell记录的分组」的筛选逻辑，窗口函数计算完成后没有合适的位置添加分组级别的筛选条件
• 窗口函数+DISTINCT ON的写法会先为分区内所有行计算窗口值，再丢弃重复行，存在大量冗余计算，数据量大时性能较差

最优写法（PostgreSQL 环境，性能最高） #

直接使用标准分组聚合+有序聚合函数实现所有需求，避免冗余计算：

SELECT
    position,
    (array_agg(action ORDER BY executed_at ASC))[1] AS action,
    (array_agg(symbol ORDER BY executed_at ASC))[1] AS symbol,
    min(executed_at) AS opened_at,
    max(executed_at) AS closed_at,
    (array_agg(price ORDER BY executed_at ASC))[1] AS entry_price,
    (array_agg(price ORDER BY executed_at DESC))[1] AS close_price,
    sum(profit) AS profit,
    sum(lot_size) AS lot_size
FROM deals
WHERE action IN ('buy', 'sell')
GROUP BY position
HAVING count(DISTINCT action) = 2;

逻辑说明 #

• 按position字段分组，符合分组要求
• 用min(executed_at)取分组内最早交易时间作为opened_at，max(executed_at)取最晚交易时间作为closed_at，对应首尾时间取值要求
• 用有序聚合array_agg(price ORDER BY executed_at ASC)取按时间升序排列的第一个价格作为入场价entry_price，按时间降序排列取第一个价格作为收盘价close_price，对应首尾价格取值要求
• 直接用sum()聚合profit和lot_size字段，对应字段求和要求
• 在HAVING子句添加count(DISTINCT action) = 2，过滤掉只有单边交易的分组，符合有效分组筛选要求
• action、symbol字段统一取分组内最早一条记录的值，和预期输出完全匹配

窗口函数写法修正（不推荐，性能较差） #

如果你需要沿用窗口函数的逻辑，可以通过CTE新增窗口计数字段实现筛选，但性能比纯聚合写法低很多，仅作参考：

WITH deal_calc AS (
    SELECT
        position,
        symbol,
        action,
        first_value(executed_at) OVER w as opened_at,
        last_value(executed_at) OVER w as closed_at,
        first_value(price) OVER w as entry_price,
        last_value(price) OVER w as close_price,
        sum(lot_size) OVER w as lot_size,
        sum(profit) OVER w as profit,
        count(DISTINCT action) OVER w as valid_flag
    FROM deals
    WHERE action IN('buy', 'sell')
    WINDOW w AS (PARTITION BY position ORDER BY executed_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
)
SELECT DISTINCT ON (position)
    position, action, symbol, opened_at, closed_at, entry_price, close_price, profit, lot_size
FROM deal_calc
WHERE valid_flag = 2;

这个写法会为原表每一行都生成一份全分区聚合结果，再通过DISTINCT ON去重，单Position下交易记录越多，性能损耗越大。

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