如何在PostgreSQL/Redshift中用非子查询方式重写分组计数查询？

原SQL的作用是统计tbl1表中col1字段出现次数大于1的不同值的总数，在PostgreSQL和Redshift中可以不用子查询实现，这里提供两种可行写法：

方法一：窗口函数结合条件判断 #

直接在SELECT中通过窗口函数计算每个col1的出现次数，再筛选统计符合条件的值：

SELECT COUNT(DISTINCT CASE WHEN COUNT(*) OVER (PARTITION BY col1) > 1 THEN col1 END)
FROM tbl1;

逻辑说明：

• COUNT(*) OVER (PARTITION BY col1) 计算每个col1值在全表中的出现次数
• CASE 语句仅保留出现次数大于1的col1值
• COUNT(DISTINCT) 统计这些符合条件的唯一col1值的数量，与原SQL结果完全一致

方法二：分组后直接统计符合条件的分组数 #

这是更简洁的写法，利用GROUP BY和HAVING筛选出重复的col1分组，直接统计分组数量：

SELECT COUNT(*)
FROM tbl1
GROUP BY col1
HAVING COUNT(*) > 1;

逻辑说明：

• GROUP BY col1 按col1值分组
• HAVING COUNT(*) > 1 筛选出出现次数大于1的分组
• 最终COUNT(*)统计这些分组的数量，就是原SQL要得到的结果

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