PostgreSQL中如何重写含DISTINCT的查询以移除DISTINCT

你当前使用的DISTINCT ON是PostgreSQL特有的语法，在有匹配索引的场景下性能表现并不差，若确实需要完全规避DISTINCT相关语法，可以参考以下两种改写方式：

方案1：窗口函数ROW_NUMBER实现（逻辑与原视图完全一致） #

CREATE OR REPLACE VIEW {accountId}.last_assets AS
SELECT * FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY coin ORDER BY ts DESC) AS row_num
    FROM {accountId}.{tableAssetsName}
) AS temp_assets
WHERE row_num = 1;

实现逻辑：按coin字段分组，每组内按ts倒序排列后给每条记录分配行号，取每组行号为1的记录，就是对应coin的最新更新记录，和原DISTINCT ON的返回结果完全一致。

方案2：GROUP BY关联查询实现（适合确认无同coin同ts重复记录的场景） #

CREATE OR REPLACE VIEW {accountId}.last_assets AS
SELECT a.*
FROM {accountId}.{tableAssetsName} a
JOIN (
    SELECT coin, MAX(ts) AS latest_ts
    FROM {accountId}.{tableAssetsName}
    GROUP BY coin
) AS latest_record ON a.coin = latest_record.coin AND a.ts = latest_record.latest_ts;

注意：如果同一个coin存在多条ts完全相同的最新记录，该方案会返回所有符合条件的记录，和原DISTINCT ON仅返回第一条的逻辑有差异，需要先确认数据特性再使用。

补充说明 #

• 只要你在表上创建了(coin, ts DESC)的联合索引，上述两种方案的性能和原DISTINCT ON写法差异极小，部分场景下窗口函数的执行效率甚至更高
• 业内常说的"DISTINCT几乎总是风险信号"，通常指的是开发者滥用DISTINCT来掩盖业务逻辑导致的数据重复问题，你当前的DISTINCT ON是PostgreSQL的标准典型用法，本身不存在逻辑问题，不需要为了规避而强行改写

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