如何使用PostgreSQL查询按ID排序后连续出现至少3次的字段值

核心思路 #

利用窗口函数对连续出现的相同Code做分组标记，统计每组长度后筛选符合条件的值：

• 先按序号（ID）全局升序排序生成全局行号
• 对每个Code单独生成内部排序的行号
• 两个行号的差值可作为连续相同Code的分组标识：只要Code连续出现，该差值就不会变
• 统计每个分组的记录数，筛选记录数≥3的分组对应的Code，去重后即为最终结果

实现代码 #

假设表名为code_table，序号字段为id，待查询字段为code，查询语句如下：

WITH group_mark AS (
    SELECT
        code,
        (row_number() OVER (ORDER BY id)) - (row_number() OVER (PARTITION BY code ORDER BY id)) AS group_flag
    FROM code_table
)
SELECT DISTINCT code
FROM group_mark
GROUP BY code, group_flag
HAVING COUNT(*) >= 3;

你给出的示例数据运行上述语句后，会唯一输出3310，符合要求。如果需要调整连续出现的次数，只需要修改HAVING子句里的数值即可。

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