如何高效编写message表按条件去重的SQL查询？

问题描述 #

我有一个message表，需要编写SQL查询满足以下需求：

• 当type为multi-store时，按collection_id分组仅保留每组的第一条记录
• 当type为standard-store时，保留所有记录

表原始数据 #

id                  |            collection_id             |      type      | affiliation_id |    status    | scheduled_for_date 
--------------------------------------+--------------------------------------+----------------+----------------+--------------+--------------------
 1143c066-01ed-4eb5-a146-68487de702a9 | bce85e31-4d2f-43b1-b263-57fca356856f | multi-store    | 12091          | draft        | 
 e1183732-e91d-42eb-9998-110e039cfc25 | bce85e31-4d2f-43b1-b263-57fca356856f | multi-store    | 12092          | draft        | 
 8f962a49-7da1-46d0-87b9-595788767dfe | bce85e31-4d2f-43b1-b263-57fca356856f | multi-store    | 12097          | draft        | 
 6e4dee09-7a4e-47be-bdd8-935a67bb2063 | 740f6b42-bbf1-4aeb-8fe9-6874635d9e29 | multi-store    | 12091          | draft        | 
 79afab0e-14e7-4d1b-9a15-358763743c3e | 740f6b42-bbf1-4aeb-8fe9-6874635d9e29 | multi-store    | 12092          | draft        | 
 7bc78bee-074a-4031-9492-954e7c4eeb09 | 740f6b42-bbf1-4aeb-8fe9-6874635d9e29 | multi-store    | 12097          | draft        | 
 3bb38fbd-d411-4f78-9c42-c858bf57b784 |                                      | standard-store | 10511          | draft        | 
 fbb3b175-1a3b-4515-b0b3-0ce6d6d0145f |                                      | standard-store | 10511          | draft        | 
 84004999-d2cf-4af4-bfaa-c1077d1d8621 |                                      | standard-store | 10511          | sent         | 2017-05-21
 cbea0789-6886-431a-a8e1-723d5aafc7b9 |                                      | standard-store | 10511          | scheduled    | 2019-02-12
 ec8988ff-5136-4b81-b448-cd456dc487a4 |                                      | standard-store | 10511          | review       | 2019-01-13
 0e119440-5fbc-4afe-a784-a6bcfe3a6e4d |                                      | standard-store | 10511          | draft        | 
 98503a20-4396-4809-b3ec-8e330c15afa9 |                                      | standard-store | 10511          | needs_action | 2018-12-11
 d33a9173-dc64-464f-8e58-49b4c9c2fdae |                                      | standard-store | 10511          | draft        | 
 bee0dc72-acca-44e2-82ea-d18e830f91a2 |                                      | standard-store | 10511          | sent         | 2016-03-12

预期输出 #

id                  |            collection_id             |      type      | affiliation_id |    status    | scheduled_for_date 
--------------------------------------+--------------------------------------+----------------+----------------+--------------+--------------------
 1143c066-01ed-4eb5-a146-68487de702a9 | bce85e31-4d2f-43b1-b263-57fca356856f | multi-store    | 12091          | draft        | 
 6e4dee09-7a4e-47be-bdd8-935a67bb2063 | 740f6b42-bbf1-4aeb-8fe9-6874635d9e29 | multi-store    | 12091          | draft        | 
 3bb38fbd-d411-4f78-9c42-c858bf57b784 |                                      | standard-store | 10511          | draft        | 
 fbb3b175-1a3b-4515-b0b3-0ce6d6d0145f |                                      | standard-store | 10511          | draft        | 
 84004999-d2cf-4af4-bfaa-c1077d1d8621 |                                      | standard-store | 10511          | sent         | 2017-05-21
 cbea0789-6886-431a-a8e1-723d5aafc7b9 |                                      | standard-store | 10511          | scheduled    | 2019-02-12
 ec8988ff-5136-4b81-b448-cd456dc487a4 |                                      | standard-store | 10511          | review       | 2019-01-13
 0e119440-5fbc-4afe-a784-a6bcfe3a6e4d |                                      | standard-store | 10511          | draft        | 
 98503a20-4396-4809-b3ec-8e330c15afa9 |                                      | standard-store | 10511          | needs_action | 2018-12-11
 d33a9173-dc64-464f-8e58-49b4c9c2fdae |                                      | standard-store | 10511          | draft        | 
 bee0dc72-acca-44e2-82ea-d18e830f91a2 |                                      | standard-store | 10511          | sent         | 2016-03-12

当前实现方案 #

我目前用UNION实现，但觉得效率较低：

SELECT DISTINCT ON (collection_id)
    collection_id,
    id,
    type,
    affiliation_id,
    status,
    scheduled_for_date
from
    message
where
    type = 'multi-store'

union

SELECT
    collection_id,
    id,
    type,
    affiliation_id,
    status,
    scheduled_for_date
from
    message
where
    type = 'standard-store'

另一种思路是用CASE分组，但需要把所有查询字段都加入分组，实现起来太复杂。想问下最高效的写法是什么？

高效解决方案 #

用**窗口函数ROW_NUMBER()**是最优方案，只需要扫描一次表就能完成筛选，比UNION两次扫描的效率更高。核心逻辑是给每条记录按规则分配行号：

• 对于multi-store类型，按collection_id分区，给每个分区内的记录排序（这里按id排序和你预期输出的第一条记录匹配），行号从1开始递增
• 对于standard-store类型，按id分区（因为每个id唯一，所以每条记录的行号都是1）

最终只需要筛选出行号为1的记录即可。

实现代码 #

SELECT 
    id,
    collection_id,
    type,
    affiliation_id,
    status,
    scheduled_for_date
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY CASE WHEN type = 'multi-store' THEN collection_id ELSE id END
            ORDER BY id -- 这里的排序规则决定了multi-store分组中保留哪条记录，和你的预期输出匹配
        ) AS rn
    FROM message
) t
WHERE rn = 1;

逻辑说明 #

1. 内层子查询给每条记录计算行号rn：
   • 当type是multi-store时，以collection_id作为分区键，同一个collection_id下的记录会被分到同一组，按id排序后第一条记录行号为1
   • 当type是standard-store时，以id作为分区键，每个分区只有一条记录，行号必然是1
2. 外层查询筛选rn = 1的记录，正好满足需求：保留multi-store每个分组的第一条，以及所有standard-store记录

这种写法只需要扫描一次表，避免了UNION的两次扫描和后续的去重操作，在数据量较大时效率优势明显。另外，你可以根据实际需求调整ORDER BY子句，比如如果需要保留最新的记录，可以换成按创建时间排序。

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