PostgreSQL按some_id分组后区分other_id是否一致的查询方法

解决方案 #

原始SQL实现 #

核心思路是通过窗口函数给每个some_id分组计算去重后的other_id数量，再基于这个数量过滤对应分组的全量数据：

1. 查询分组内other_id完全相同的全量数据

SELECT some_id, description, other_id
FROM (
    SELECT 
        *,
        COUNT(DISTINCT other_id) OVER (PARTITION BY some_id) AS distinct_other_cnt
    FROM my_table
) t
WHERE distinct_other_cnt = 1;

2. 查询分组内存在不同other_id的全量数据

SELECT some_id, description, other_id
FROM (
    SELECT 
        *,
        COUNT(DISTINCT other_id) OVER (PARTITION BY some_id) AS distinct_other_cnt
    FROM my_table
) t
WHERE distinct_other_cnt > 1;

如果数据量较大，也可以用子查询先过滤符合条件的some_id再关联原表，性能更优：

-- 第一类查询示例（第二类仅需把HAVING条件改为COUNT(DISTINCT other_id) > 1即可）
SELECT m.*
FROM my_table m
INNER JOIN (
    SELECT some_id
    FROM my_table
    GROUP BY some_id
    HAVING COUNT(DISTINCT other_id) = 1
) filtered_some_id ON m.some_id = filtered_some_id.some_id;

Sequelize实现 #

以第一类查询为例，第二类仅需修改判断条件即可：

const result = await MyTable.findAll({
    attributes: ['some_id', 'description', 'other_id'],
    // 直接用窗口函数实现写法更简洁
    attributes: [
        'some_id', 'description', 'other_id',
        [sequelize.literal(`COUNT(DISTINCT other_id) OVER (PARTITION BY some_id)`), 'distinct_other_cnt']
    ],
    having: sequelize.literal(`distinct_other_cnt = 1`)
});

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