如何通过SQL关联基因组范围数据集，避免主索引表行数冗余？

问题描述 #

我有多个基因组范围数据集，需要将各数据集中的重叠基因组范围与主索引表（S2）关联。但当存在多个重叠范围时，不希望主索引表的行数增加，而是让关联数据集的多条重叠范围在对应主行下展示，避免笛卡尔积导致的行数膨胀问题。

当前使用LEFT JOIN编写的SQL会生成大量冗余行，代码如下：

SELECT DISTINCT 
    S2.chrom, 
    S2.ChromStart, 
    S2.ChromEnd, 
    S2.width,
    S2_1ug.S2_1ug.ChromStart AS S2_1ug_ChromStart, 
    S2_1ug.S2_1ug.ChromEnd AS S2_1ug_ChromEnd,
    S2_5ug.s2_5ug.ChromStart AS S2_5ug_ChromStart, 
    S2_5ug.S2_5ug.ChromEnd AS S2_5ug_ChromEnd,
    s2_1ug.ChromStart AS s2_1ug_ChromStart, 
    s2_1ug.ChromEnd AS s2_1ug_ChromEnd,
    s2_5ug.ChromStart AS s2_5ug_ChromStart, 
    s2_5ug.ChromEnd AS s2_5ug_ChromEnd

FROM S2.S2
LEFT JOIN S2_1ug.S2_1ug ON 
    S2.chrom = S2_1ug.chrom
    AND ((S2.ChromStart BETWEEN S2_1ug.ChromStart AND S2_1ug.ChromEnd) OR (S2.ChromEnd BETWEEN S2_1ug.ChromStart AND S2_1ug.ChromEnd))
LEFT JOIN S2_1ug.s2_1ug ON 
    S2.chrom = s2_1ug.chrom
    AND ((S2.ChromStart BETWEEN s2_1ug.ChromStart AND s2_1ug.ChromEnd) OR (S2.ChromEnd BETWEEN s2_1ug.ChromStart AND s2_1ug.ChromEnd))
LEFT JOIN S2_5ug.S2_5ug ON 
    S2.chrom = S2_5ug.chrom
    AND ((S2.ChromStart BETWEEN S2_5ug.ChromStart AND S2_5ug.ChromEnd) OR (S2.ChromEnd BETWEEN S2_5ug.ChromStart AND S2_5ug.ChromEnd))
LEFT JOIN S2_5ug.s2_5ug ON 
    S2.chrom = s2_5ug.chrom
    AND ((S2.ChromStart BETWEEN s2_5ug.ChromStart AND s2_5ug.ChromEnd) OR (S2.ChromEnd BETWEEN s2_5ug.ChromStart AND s2_5ug.ChromEnd));

期望输出为主索引表每行仅出现一次，关联数据集的多条重叠范围在对应行下展示，示例如下：

chr2L       850264       850894     chr2L       850635       850883     chr2L       850635       850883     chr2L       850264       850845     chr2L       850264       850845
chr2L       1076737       1077723   chr2L       1076875       1077423   chr2L       1076875       1077023   chr2L       1076737       1077674   chr2L       1076738       1077699
                                    chr2L       1077575       1077723   chr2L       1077225       1077373           

解决方案 #

方案1：聚合重叠范围为字符串（简洁型） #

将每个关联数据集的重叠范围拼接成字符串，保证主表每行仅返回一条记录，适合不需要保留列格式的场景。以PostgreSQL为例，MySQL可替换为GROUP_CONCAT：

SELECT
    s2.chrom,
    s2.ChromStart,
    s2.ChromEnd,
    s2.width,
    -- 聚合S2_1ug的重叠范围，用|分隔多个记录
    STRING_AGG(CONCAT(s2_1ug.chrom, ' ', s2_1ug.ChromStart, ' ', s2_1ug.ChromEnd), ' | ') AS s2_1ug_ranges,
    -- 聚合S2_5ug的重叠范围
    STRING_AGG(CONCAT(s2_5ug.chrom, ' ', s2_5ug.ChromStart, ' ', s2_5ug.ChromEnd), ' | ') AS s2_5ug_ranges
FROM S2.S2
LEFT JOIN S2_1ug.S2_1ug ON
    s2.chrom = s2_1ug.chrom
    AND (
        s2.ChromStart BETWEEN s2_1ug.ChromStart AND s2_1ug.ChromEnd
        OR s2.ChromEnd BETWEEN s2_1ug.ChromStart AND s2_1ug.ChromEnd
    )
LEFT JOIN S2_5ug.S2_5ug ON
    s2.chrom = s2_5ug.chrom
    AND (
        s2.ChromStart BETWEEN s2_5ug.ChromStart AND s2_5ug.ChromEnd
        OR s2.ChromEnd BETWEEN s2_5ug.ChromStart AND s2_5ug.ChromEnd
    )
GROUP BY s2.chrom, s2.ChromStart, s2.ChromEnd, s2.width;

方案2：窗口函数对齐多行记录（列对齐型） #

通过窗口函数给每个主表记录的关联重叠范围分配行号，实现主表内容仅在首行显示、后续行主表字段为NULL的格式，匹配你期望的输出样式：

WITH s2_1ug_with_row AS (
    SELECT
        s2.chrom AS s2_chrom,
        s2.ChromStart AS s2_start,
        s2.ChromEnd AS s2_end,
        s2_1ug.chrom,
        s2_1ug.ChromStart,
        s2_1ug.ChromEnd,
        -- 按主表记录分组生成行号
        ROW_NUMBER() OVER (PARTITION BY s2.chrom, s2.ChromStart, s2.ChromEnd ORDER BY s2_1ug.ChromStart) AS row_num
    FROM S2.S2 s2
    LEFT JOIN S2_1ug.S2_1ug ON
        s2.chrom = s2_1ug.chrom
        AND (
            s2.ChromStart BETWEEN s2_1ug.ChromStart AND s2_1ug.ChromEnd
            OR s2.ChromEnd BETWEEN s2_1ug.ChromStart AND s2_1ug.ChromEnd
        )
),
s2_5ug_with_row AS (
    SELECT
        s2.chrom AS s2_chrom,
        s2.ChromStart AS s2_start,
        s2.ChromEnd AS s2_end,
        s2_5ug.chrom,
        s2_5ug.ChromStart,
        s2_5ug.ChromEnd,
        ROW_NUMBER() OVER (PARTITION BY s2.chrom, s2.ChromStart, s2.ChromEnd ORDER BY s2_5ug.ChromStart) AS row_num
    FROM S2.S2 s2
    LEFT JOIN S2_5ug.S2_5ug ON
        s2.chrom = s2_5ug.chrom
        AND (
            s2.ChromStart BETWEEN s2_5ug.ChromStart AND s2_5ug.ChromEnd
            OR s2.ChromEnd BETWEEN s2_5ug.ChromStart AND s2_5ug.ChromEnd
        )
),
-- 获取主表所有可能的行号（覆盖关联表的最大行数）
s2_row_numbers AS (
    SELECT DISTINCT row_num
    FROM (
        SELECT row_num FROM s2_1ug_with_row
        UNION
        SELECT row_num FROM s2_5ug_with_row
    ) AS all_rows
),
s2_main AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY chrom, ChromStart, ChromEnd ORDER BY 1) AS row_num
    FROM S2.S2
)
SELECT
    -- 主表字段仅在首行显示
    CASE WHEN sm.row_num = 1 THEN sm.chrom END AS chrom,
    CASE WHEN sm.row_num = 1 THEN sm.ChromStart END AS ChromStart,
    CASE WHEN sm.row_num = 1 THEN sm.ChromEnd END AS ChromEnd,
    CASE WHEN sm.row_num = 1 THEN sm.width END AS width,
    s1.ChromStart AS s2_1ug_ChromStart,
    s1.ChromEnd AS s2_1ug_ChromEnd,
    s5.ChromStart AS s2_5ug_ChromStart,
    s5.ChromEnd AS s2_5ug_ChromEnd
FROM s2_main sm
CROSS JOIN s2_row_numbers rn
LEFT JOIN s2_1ug_with_row s1
    ON sm.chrom = s1.s2_chrom
    AND sm.ChromStart = s1.s2_start
    AND sm.ChromEnd = s1.s2_end
    AND rn.row_num = s1.row_num
LEFT JOIN s2_5ug_with_row s5
    ON sm.chrom = s5.s2_chrom
    AND sm.ChromStart = s5.s2_start
    AND sm.ChromEnd = s5.s2_end
    AND rn.row_num = s5.row_num
WHERE sm.row_num = 1 OR s1.ChromStart IS NOT NULL OR s5.ChromStart IS NOT NULL
ORDER BY sm.chrom, sm.ChromStart, rn.row_num;

注意事项 #

• 原SQL中存在重复关联同一张表的冗余操作，需删除重复的JOIN语句；
• 不同数据库的函数语法有差异：SQL Server用STRING_AGG，Oracle用LISTAGG，需根据实际数据库调整；
• 方案2会生成部分NULL行，但能严格匹配你期望的列对齐展示格式。

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