PostgreSQL中不分组base_asset_icon字段如何查询获取该列值

当前使用如下查询语句从trades表获取交易统计数据：

SELECT base_asset_trade, base_asset_icon,
       count(CASE WHEN is_buyer_maker = 'true' THEN 1 END) AS sold,
       count(CASE WHEN is_buyer_maker = 'false' THEN 1 END) AS bought,
       sum(CASE WHEN is_buyer_maker = 'true' THEN trade_value  END) AS sold_trade_value,
       sum(CASE WHEN is_buyer_maker = 'false'  THEN trade_value  END) AS bought_trade_value,
       COUNT(base_asset_trade) AS total_trades
FROM trades
WHERE trade_time / 1000 > (extract(epoch from now()) - (86400)*1) 
GROUP BY base_asset_trade, base_asset_icon
ORDER BY total_trades DESC

trades表结构如下：

id (int)
exchange_name (VARCHAR)
exchange_icon (VARCHAR)
trade_time (bigint)
price_quote (int)
price_usd (int)
trade_value (int)
base_asset_icon (VARCHAR)
qty (int)
quoteQty (int)
is_buyer_maker (boolean)
pair (VARCHAR)
base_asset_trade (VARCHAR)
quote_asset_trade (VARCHAR)

需求为仅按base_asset_trade字段分组统计，同时返回对应base_asset_icon字段值，不需要将base_asset_icon加入GROUP BY子句。

PostgreSQL的GROUP BY语法规则要求：SELECT列表中出现的非聚合字段，必须全部包含在GROUP BY子句中。
结合业务逻辑，base_asset_icon是币种图标，和base_asset_trade（币种标识）是一对一映射关系，同一个base_asset_trade分组下的base_asset_icon值完全一致，这种场景下只需要用聚合函数包裹base_asset_icon，明确告知数据库取该分组下的字段值，即可绕过GROUP BY校验，不需要把该字段加入分组。

• 最简写法：用MAX()/MIN()聚合base_asset_icon
  因为同组下所有base_asset_icon值相同，取最大值/最小值的结果就是正确的图标值，修改后的SQL如下：

  SELECT base_asset_trade, 
         MAX(base_asset_icon) AS base_asset_icon,
         count(CASE WHEN is_buyer_maker = true THEN 1 END) AS sold,
         count(CASE WHEN is_buyer_maker = false THEN 1 END) AS bought,
         sum(CASE WHEN is_buyer_maker = true THEN trade_value  END) AS sold_trade_value,
         sum(CASE WHEN is_buyer_maker = false  THEN trade_value  END) AS bought_trade_value,
         COUNT(base_asset_trade) AS total_trades
  FROM trades
  WHERE trade_time / 1000 > (extract(epoch from now()) - 86400) 
  GROUP BY base_asset_trade
  ORDER BY total_trades DESC
  

  额外修正：原语句中is_buyer_maker是boolean类型，不需要加单引号写'true'/'false'，直接写布尔值即可，避免隐式类型转换带来的逻辑错误或性能损耗。
• 脏数据兼容写法：用PostgreSQL独有DISTINCT ON语法
  如果担心存在同一个base_asset_trade对应多个不同base_asset_icon的脏数据，可以先完成分组统计，再关联原表取每个币种最新记录对应的icon，写法如下：

  WITH trade_stat AS (
      SELECT base_asset_trade,
             count(CASE WHEN is_buyer_maker = true THEN 1 END) AS sold,
             count(CASE WHEN is_buyer_maker = false THEN 1 END) AS bought,
             sum(CASE WHEN is_buyer_maker = true THEN trade_value  END) AS sold_trade_value,
             sum(CASE WHEN is_buyer_maker = false  THEN trade_value  END) AS bought_trade_value,
             COUNT(base_asset_trade) AS total_trades
      FROM trades
      WHERE trade_time / 1000 > (extract(epoch from now()) - 86400) 
      GROUP BY base_asset_trade
  )
  SELECT DISTINCT ON (t.base_asset_trade) 
         t.base_asset_trade,
         tr.base_asset_icon,
         t.sold,
         t.bought,
         t.sold_trade_value,
         t.bought_trade_value,
         t.total_trades
  FROM trade_stat t
  JOIN trades tr ON t.base_asset_trade = tr.base_asset_trade
  ORDER BY t.base_asset_trade, tr.id DESC
  

注意：如果同一个base_asset_trade分组下确实存在多个不同的base_asset_icon值，上述两种写法都只会返回其中一个值，不会抛出错误，使用前请确认两个字段在业务上是一对一映射关系，避免返回错误的图标数据。

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