PostgreSQL查询trades表按基础资产分组统计买卖交易数据

你写的SQL无法正常运行，核心问题如下：

• 分组维度错误：需求是按基础资产维度做汇总，你当前按is_buyer_maker分组，只能得到全局买入、卖出两组数据，无法按资产拆分
• 缺少条件聚合逻辑：没有针对买入、卖出两种状态分别统计交易次数和金额
• 语法不符合规范：SELECT子句中出现的非聚合字段没有全部加入GROUP BY，且引用了不存在的字段price_in_usd（表中实际字段为price_usd）
• 未定义price_usd的取值规则：从你给出的结果示例看，该字段取对应基础资产最新一笔成交的美元价格即可。

WITH recent_trades AS (
    SELECT *
    FROM trades
    -- 过滤最近24小时交易，原字段存的是毫秒级时间戳，除以1000转为秒级时间戳做比对
    WHERE trade_time / 1000 > EXTRACT(EPOCH FROM NOW()) - 86400
)
SELECT
    base_asset_trade AS base_asset,
    base_asset_icon,
    COUNT(*) FILTER (WHERE is_buyer_maker = true) AS sold,
    COUNT(*) FILTER (WHERE is_buyer_maker = false) AS bought,
    SUM(trade_value) FILTER (WHERE is_buyer_maker = true) AS sold_trade_value,
    SUM(trade_value) FILTER (WHERE is_buyer_maker = false) AS bought_trade_value,
    (ARRAY_AGG(price_usd ORDER BY trade_time DESC))[1] AS price,
    COUNT(*) AS total_trades
FROM recent_trades
GROUP BY base_asset_trade, base_asset_icon;

• 先用CTE预处理过滤最近24小时的交易，避免重复书写时间过滤逻辑
• 用PostgreSQL原生FILTER子句实现条件聚合，分别统计卖出、买入的次数和总金额，可读性比传统CASE WHEN写法更好
• 分组字段设置为base_asset_trade和base_asset_icon，符合SQL分组语法要求：所有非聚合的SELECT字段必须出现在GROUP BY中
• 通过ARRAY_AGG按交易时间倒序聚合价格字段，取数组第一个元素即可得到对应资产最新的成交价格，和示例中的price字段对应
• 直接用COUNT(*)统计每个资产分组下的总记录数，就是总交易笔数，不需要额外累加买卖次数，执行效率更高

如果你使用的是9.4以下的极旧版PostgreSQL（已停止官方维护多年），不支持FILTER语法，可以将条件聚合部分替换为CASE WHEN写法，示例：

COUNT(CASE WHEN is_buyer_maker = true THEN 1 END) AS sold,
SUM(CASE WHEN is_buyer_maker = true THEN trade_value ELSE 0 END) AS sold_trade_value

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