Hive两表关联校验字段差值及统计合规占比SQL实现

• 语法顺序不符合Hive SQL规范：JOIN关联的ON条件必须紧接在关联表声明之后，原语句把SELECT子句里的字段计算逻辑写在了JOIN和ON中间，直接触发语法错误
• 差值判断逻辑有缺陷：直接计算两字段差值没有取绝对值，当h表字段值大于s表时，差值为负数会天然小于0.00001，出现实际偏差很大但被误判为符合要求的情况
• SELECT子句字段缺失：原语句只声明了rid、cid两个输出字段，没有把三个字段的校验结果放在SELECT子句中

修正语法顺序、补全字段、加上绝对值判断后，逐行输出校验结果的代码如下：

SELECT 
    s.rid,
    s.cid,
    IF(ABS(s.a - h.a) < 0.00001, 'True', 'False') AS result_a,
    IF(ABS(s.b - h.b) < 0.00001, 'True', 'False') AS result_b,
    IF(ABS(s.c - h.c) < 0.00001, 'True', 'False') AS result_c
FROM a.samples s
INNER JOIN a.hive h
  ON s.rid = h.rid
 AND s.cid = h.cid;

这里用INNER JOIN就是只保留两表rid、cid完全一致的匹配记录，符合关联需求。

统计三个字段差值全部小于0.00001的记录占总匹配记录的比例，可以直接用聚合函数计算，不需要嵌套子查询：

SELECT
    COUNT(1) AS total_matched_records,
    SUM(CASE
            WHEN ABS(s.a - h.a) < 0.00001
             AND ABS(s.b - h.b) < 0.00001
             AND ABS(s.c - h.c) < 0.00001
            THEN 1 ELSE 0
        END) AS all_fields_matched_records,
    CONCAT(
        ROUND(
            SUM(CASE
                    WHEN ABS(s.a - h.a) < 0.00001
                     AND ABS(s.b - h.b) < 0.00001
                     AND ABS(s.c - h.c) < 0.00001
                    THEN 1 ELSE 0
                END) / COUNT(1) * 100,
            2
        ),
        '%'
    ) AS match_percentage
FROM a.samples s
INNER JOIN a.hive h
  ON s.rid = h.rid
 AND s.cid = h.cid;

补充说明：如果需要分别统计a、b、c单个字段的符合占比，只需要把SUM判断里的多字段AND条件改成对应单字段判断即可；如果需要把逐行校验结果和占比同时输出，可以用窗口函数COUNT() OVER()给每行附加上总匹配数、符合条件总数再计算占比，避免两次查询。

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