如何合并两个SELECT结果表以计算源的唯一关系占比

背景 #

• 多数据源包含引用关系，已算出每个关系在数据源中的出现占比（普及度百分比）
• 定义：仅在不足10%数据源中出现的关系为「唯一关系」
• 目标指标：单个数据源的唯一关系数 / 该数据源总关系数

问题分析 #

你现有的两个查询分别能获取分子（唯一关系数）和分母（总关系数），但直接在SELECT语句中嵌套这两个查询会触发1242错误——因为两个查询都返回多行结果，无法直接作为单列值输出。正确做法是将两个查询作为独立的临时表，通过Source ID关联，或者优化查询逻辑避免重复计算。

优化后的SQL（推荐） #

此方法将公共计算逻辑抽离，减少重复查询，效率更高：

WITH relationship_ubiquity AS (
    SELECT 
        s.`Source ID`,
        o.`Relationship ID`,
        -- 计算关系的数据源普及百分比
        ROUND(COUNT(DISTINCT s.`Source ID`) OVER (PARTITION BY o.`Relationship ID`) / (SELECT COUNT(*) FROM sources) * 100, 3) AS `Ubiquity Percentage`
    FROM statements s
    JOIN observations o ON s.`Old Observation ID` = o.`Old Obs ID`
),
source_metrics AS (
    SELECT
        `Source ID`,
        -- 分母：当前数据源的总关系数
        COUNT(DISTINCT `Relationship ID`) AS Denominator,
        -- 分子：当前数据源中的唯一关系数
        SUM(CASE WHEN `Ubiquity Percentage` < 10 THEN 1 ELSE 0 END) AS Numerator
    FROM relationship_ubiquity
    GROUP BY `Source ID`
)
SELECT
    `Source ID`,
    Numerator,
    Denominator,
    -- 计算占比，处理分母为0的异常
    CASE WHEN Denominator = 0 THEN 0 ELSE ROUND(Numerator / Denominator, 4) END AS Unique_Ratio
FROM source_metrics
ORDER BY `Source ID`;

逻辑说明 #

1. relationship_ubiquity：关联statements和observations表，用窗口函数统计每个关系覆盖的数据源数量，计算出普及度百分比。
2. source_metrics：按数据源分组，统计总关系数（分母）和符合条件的唯一关系数（分子）。
3. 最终查询：计算占比，同时避免分母为0导致的报错。

兼容低版本MySQL的写法（无CTE支持） #

如果你的MySQL版本低于8.0，不支持CTE，可以用嵌套子查询实现：

SELECT
    sm.`Source ID`,
    sm.Numerator,
    sm.Denominator,
    CASE WHEN sm.Denominator = 0 THEN 0 ELSE ROUND(sm.Numerator / sm.Denominator, 4) END AS Unique_Ratio
FROM (
    SELECT
        `Source ID`,
        COUNT(DISTINCT `Relationship ID`) AS Denominator,
        SUM(CASE WHEN `Ubiquity Percentage` < 10 THEN 1 ELSE 0 END) AS Numerator
    FROM (
        SELECT 
            s.`Source ID`,
            o.`Relationship ID`,
            ROUND(COUNT(DISTINCT s.`Source ID`) OVER (PARTITION BY o.`Relationship ID`) / (SELECT COUNT(*) FROM sources) * 100, 3) AS `Ubiquity Percentage`
        FROM statements s
        JOIN observations o ON s.`Old Observation ID` = o.`Old Obs ID`
    ) AS ru
    GROUP BY `Source ID`
) AS sm
ORDER BY sm.`Source ID`;

复用你现有查询的JOIN写法 #

如果希望直接复用你已经写好的分子/分母查询，可以通过内关联合并结果：

SELECT
    n.`Source ID`,
    n.Numerator,
    d.Denominator,
    CASE WHEN d.Denominator = 0 THEN 0 ELSE ROUND(n.Numerator / d.Denominator, 4) END AS Unique_Ratio
FROM (
    -- 你的分子查询
    SELECT `Source ID`, COUNT(`Source ID`) AS Numerator
    FROM (
        SELECT statements.`Source ID`, observations.`Relationship ID`, 
            ROUND((COUNT(statements.`Source ID`) / (SELECT COUNT(sources.`Source ID`) FROM sources)),3) * 100 AS `Ubiquity Percentage`
        FROM statements
        JOIN observations ON statements.`Old Observation ID` = observations.`Old Obs ID`
        GROUP BY `Relationship ID`
    ) AS subsubquery
    WHERE `Ubiquity Percentage` < 10
    GROUP BY `Source ID`
) AS n
JOIN (
    -- 你的分母查询
    SELECT `Source ID`, COUNT(`Source ID`) AS Denominator
    FROM (
        SELECT statements.`Source ID`, observations.`Relationship ID`, 
            ROUND((COUNT(statements.`Source ID`) / (SELECT COUNT(sources.`Source ID`) FROM sources)),3) * 100 AS `Ubiquity Percentage`
        FROM statements
        JOIN observations ON statements.`Old Observation ID` = observations.`Old Obs ID`
        GROUP BY `Relationship ID`
    ) AS subquery
    GROUP BY `Source ID`
) AS d ON n.`Source ID` = d.`Source ID`
ORDER BY n.`Source ID`;

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