基于非NULL值动态关联两表并计算百分位排名的SQL问题

需求说明 #

需要基于多条件动态关联两张表（每行关联条件不同），同时计算T2表score2列的值在T1表匹配行score1列中的百分位排名：

• 关联规则：匹配两表前5列中所有非空值（排除score列），即T2中某字段非空时，T1对应字段必须相等；T2中字段为空时，该字段不参与匹配。
• 百分位排名逻辑：以T1匹配行的score1为范围，计算T2中score2的排名。例如T2第一行score2=2，对应T1匹配行的score1为1、1、5，排名为67%（大于2/3的匹配值）。

表结构 #

T1表 #

+--------+-------+--------+-------+-------+-------+
| height | width | weight | power | price | score1|
+--------+-------+--------+-------+-------+-------+
| 180    | 130   | 30     | 20    | 100   | 1     |
| 170    | 130   | 90     | 50    | 200   | 5     |
| 180    | 130   | 30     | 20    | 50    | 1     |
| 210    | 180   | 90     | 90    | 1000  | 1     |
| 210    | 300   | 90     | 90    | 1000  | 5     |
| 100    | 130   | 30     | 20    | 1000  | 5     |
| 170    | 300   | 90     | 90    | 200   | 4     |
| 180    | 80    | 90     | 10    | 1000  | 3     |
| 210    | 300   | 90     | 90    | 1000  | 6     |
+--------+-------+--------+-------+-------+-------+

T2表 #

+-----------+----------+-----------+----------+----------+-------+
| height_t2 | width_t2 | weight_t2 | power_t2 | price_t2 | score2|
+-----------+----------+-----------+----------+----------+-------+
| -         | 130      | 30        | 20       | -        | 2     |
| 170       | -        | 90        | -        | 200      | 2     |
| 180       | 80       | -         | 10       | -        | 5     |
| 210       | -        | 90        | -        | 1000     | 6     |
+-----------+----------+-----------+----------+----------+-------+

注：T2表中的-和空值均视为NULL处理

原SQL的问题分析 #

你提供的SQL存在多处逻辑错误，导致结果不准确：

1. 关联条件逻辑颠倒：原JOIN条件判断T1字段是否为NULL，但实际应该是T2字段非空时，T1对应字段必须相等，否则忽略该条件。
2. APPROX_PERCENTILE用法错误：该函数的正确用法是指定百分位值（如APPROX_PERCENTILE(score1, 0.5)），而非结合窗口函数分区使用；且你的需求是计算score2在score1集合中的百分位，不是对score1分区计算百分位。
3. WHERE子句不符合需求：score1 = score2会过滤掉所有不相等的行，而你需要基于所有匹配的score1来计算score2的排名，不是只保留相等的行。

解决方案 #

正确思路 #

1. 按规则正确关联T1和T2，得到每个T2行对应的所有T1匹配行。
2. 对每个T2行，聚合其匹配的score1集合，计算score2在该集合中的百分位排名（即小于等于score2的score1数量占总匹配数量的比例）。

示例SQL（通用SQL引擎） #

SELECT
    t2.*,
    -- 计算百分位排名：(小于等于score2的score1数量 / 总匹配数量) * 100，保留两位小数
    ROUND(
        (COUNT(CASE WHEN t1.score1 <= t2.score2 THEN 1 END) * 100.0) / COUNT(t1.score1),
        2
    ) AS percentile_rank
FROM t2
LEFT JOIN t1 ON
    (t2.height_t2 IS NULL OR t1.height = t2.height_t2)
    AND (t2.width_t2 IS NULL OR t1.width = t2.width_t2)
    AND (t2.weight_t2 IS NULL OR t1.weight = t2.weight_t2)
    AND (t2.power_t2 IS NULL OR t1.power = t2.power_t2)
    AND (t2.price_t2 IS NULL OR t1.price = t2.price_t2)
-- 过滤T2中无匹配的行（若需保留无匹配行可删除此条件）
WHERE t1.score1 IS NOT NULL
GROUP BY t2.height_t2, t2.width_t2, t2.weight_t2, t2.power_t2, t2.price_t2, t2.score2
ORDER BY t2.score2;

大数据集优化方案 #

如果数据集规模极大，精确计算性能不足，可使用近似百分位函数（以BigQuery为例）：

WITH matched_data AS (
    SELECT
        t2.*,
        t1.score1
    FROM t2
    LEFT JOIN t1 ON
        (t2.height_t2 IS NULL OR t1.height = t2.height_t2)
        AND (t2.width_t2 IS NULL OR t1.width = t2.width_t2)
        AND (t2.weight_t2 IS NULL OR t1.weight = t2.weight_t2)
        AND (t2.power_t2 IS NULL OR t1.power = t2.power_t2)
        AND (t2.price_t2 IS NULL OR t1.price = t2.price_t2)
    WHERE t1.score1 IS NOT NULL
)
SELECT
    DISTINCT
    height_t2, width_t2, weight_t2, power_t2, price_t2, score2,
    -- 计算score2在score1集合中的近似百分位
    APPROX_PERCENTILE_CONT(score2, 0.5) OVER (PARTITION BY height_t2, width_t2, weight_t2, power_t2, price_t2) AS approx_percentile
FROM matched_data;

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