如何在PostgreSQL中实现无连续重复分类的行交替显示

问题描述 #

现有一张结构如下的表（假设表名为category_ranks）：

需求是查询结果中避免连续两行出现相同的分类值，相同分类可在后续行重复，仅需规避连续排列（比如原表中连续的VIDEOS、WORLD行需要重新排列）。期望输出示例：

请问在PostgreSQL中是否可以实现这种交替排列？

解决方案 #

可以实现，核心思路是利用窗口函数给每个分类下的行生成分组内序号，再基于该序号和分类进行排序，从而让不同分类的行交替出现。

基础实现（适用于大多数场景） #

SELECT category, rank
FROM (
    SELECT 
        category, 
        rank,
        -- 按分类分组，给每组内的行按rank生成递增序号
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY rank) AS rn
    FROM category_ranks
) t
-- 先按分组序号排序，再按分类排序，确保同序号的不同分类行交替排列
ORDER BY rn, category;

逻辑说明 #

1. 子查询中使用ROW_NUMBER()窗口函数，按category分组，每组内按rank排序并生成序号rn，同一分类下的每行都会得到一个唯一的递增序号。
2. 外层查询按rn升序排序，再按category排序，这样序号相同的不同分类行会被排在一起，从根本上避免了同一分类连续出现的情况。

执行上述SQL后，输出结果如下（满足无连续重复分类的要求，与期望示例逻辑一致）：

特殊场景处理（绝对避免连续重复） #

如果存在某个分类的行数远多于其他分类的情况（比如某分类有5行，其他最多2行），当序号rn超过其他分类的最大序号后，该分类的行可能会连续出现。这种场景可以通过递归CTE来逐个选取下一个不同分类的行，确保绝对无连续重复：

WITH RECURSIVE sorted_data AS (
    SELECT 
        category, 
        rank,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY rank) AS rn
    FROM category_ranks
),
recursive_selection AS (
    -- 初始化：选取第一行
    SELECT category, rank, rn, 1 AS result_order
    FROM sorted_data
    ORDER BY rn, category
    LIMIT 1
    UNION ALL
    -- 递归步骤：选取下一行，分类与上一行不同
    SELECT sd.category, sd.rank, sd.rn, rs.result_order + 1
    FROM recursive_selection rs
    JOIN sorted_data sd ON sd.category != rs.category
    -- 确保选取当前序号最小的可用行
    WHERE NOT EXISTS (
        SELECT 1 FROM sorted_data sd2
        WHERE sd2.category != rs.category
        AND (sd2.rn < sd.rn OR (sd2.rn = sd.rn AND sd2.category < sd.category))
        AND NOT EXISTS (
            SELECT 1 FROM recursive_selection rs2 WHERE rs2.category = sd2.category AND rs2.rn = sd2.rn
        )
    )
    AND NOT EXISTS (
        SELECT 1 FROM recursive_selection rs2 WHERE rs2.category = sd.category AND rs2.rn = sd.rn
    )
)
SELECT category, rank
FROM recursive_selection
ORDER BY result_order;

这个递归方法会逐个选取下一个不同分类的行，确保无论分类行数差异多大，都不会出现连续重复的情况，但性能会比窗口函数方法稍差，适合数据量不大的场景。

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