如何在PostgreSQL中用窗口函数替换复杂的Lateral Join子查询？

问题背景 #

我们系统里有检测重复帖子的逻辑，原本用Lateral Join实现，大致查询结构如下：

select *
from posts outer lateral join
(select id
from posts where id != outer.id and 
(tag = outer.tag) and
(date = outer.date) and
(title = outer.title) and
...//其他带AND/OR的条件
)

现在想改用窗口函数替换这个Lateral Join子查询。单个字段的情况很简单，比如按标题分组获取重复ID：

select id, array_agg(id) over (partition by title)
from posts

但实际的重复判断条件包含约10个带AND/OR的规则（比如字段相等或双方均为NULL），不知道怎么扩展窗口函数实现。

补充的精确查询与建表语句 #

原精确查询：

select *
from posts p join lateral
(select id
from posts where id != p.id and 
(tag is null or p.tag is null or tag = p.tag) and
(date is null or p.date is null or date = p.date) and
(title = p.title) and
(category_id is null or p.category_id is null or category_id = p.category_id)) p2 on true

建表语句：

create table if not exists posts(
   id serial primary key,
   title varchar,
   tag varchar,
   category_id bigint,
   date TIMESTAMP DEFAULT NOW()
)

解决方案 #

核心是把**"字段相等或双方均为NULL"**的多字段匹配逻辑，转化为窗口函数可识别的分区键，具体步骤如下：

1. 构造NULL兼容的匹配键 #

对于每个需要判断的字段，用coalesce把NULL替换成一个业务中不会出现的特殊值（比如'__NULL__'），这样就能让"字段相等"和"双方都是NULL"的记录分到同一组。

针对你的精确查询，各字段的匹配键处理：

• 标题：直接用title（因为条件是严格相等，无NULL兼容）
• 标签：coalesce(tag, '__NULL__')
• 分类ID：coalesce(category_id::varchar, '__NULL__')（转字符串是为了统一类型，避免NULL与数值的兼容问题）
• 日期：coalesce(date::varchar, '__NULL__')

2. 用窗口函数生成重复ID列表 #

将这些匹配键作为partition by的分组条件，用array_agg(id)收集同组内的所有ID，再用array_remove去掉当前记录的ID，得到真正的重复帖子ID列表：

select
    id,
    title,
    tag,
    category_id,
    date,
    array_remove(array_agg(id) over (
        partition by
            title,
            coalesce(tag, '__NULL__'),
            coalesce(category_id::varchar, '__NULL__'),
            coalesce(date::varchar, '__NULL__')
    ), id) as duplicate_post_ids
from posts;

3. 生成与原查询一致的展开式结果 #

如果需要和原Lateral Join一样，每条重复对生成一行数据，可以用unnest把数组拆成行：

select
    p.id as original_id,
    unnest(p.duplicate_post_ids) as duplicate_id
from (
    select
        id,
        array_remove(array_agg(id) over (
            partition by
                title,
                coalesce(tag, '__NULL__'),
                coalesce(category_id::varchar, '__NULL__'),
                coalesce(date::varchar, '__NULL__')
        ), id) as duplicate_post_ids
    from posts
) p
where array_length(p.duplicate_post_ids, 1) > 0;

这个结果和原Lateral Join输出的p.id、p2.id对应关系完全一致。

扩展到10个字段的情况 #

如果有更多字段，只需在partition by中继续添加对应的coalesce(字段, '__NULL__')（非字符串类型记得转成字符串）即可，逻辑完全通用。

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