如何在DolphinDB SQL中实现两表差异字段对比函数

需求说明 #

• 支持指定对比字段，默认对比全部字段；
• 识别不匹配记录，输出差异字段名称及对应值；
• 将差异合并至单个结果表，并列展示两表原始记录便于对比。

示例演示 #

假设有以下两条记录：

• TableA：ID = 1，Name = "John"，Age = 30
• TableA1：ID = 1，Name = "Jon"，Age = 50

仅对比Name字段时的输出 #

仅对比Age字段时的输出 #

同时对比Name与Age字段时的输出 #

函数结构要求 #

需要将对比逻辑封装为如下结构的函数：

// t1, t2 为待对比的两张表
// cols 为指定对比的字段名向量，默认空向量表示对比全部字段
def compare(t1, t2, cols=[]){
    // 对比逻辑实现
    ....
    // 返回结果表
    return re
}

DolphinDB实现方案 #

以下是满足需求的函数实现，核心通过表关联、字段转置提取差异：

def compare(t1, t2, cols=[]){
    // 校验两表是否包含ID作为关联主键（若实际主键不同可修改此处）
    if (!contains(t1.schema().colNames, `ID) || !contains(t2.schema().colNames, `ID)) {
        throw("两张表必须包含ID字段作为关联键")
    }
    
    // 确定目标对比字段：默认取两表共有的非ID字段，指定字段时先校验合法性
    targetCols = if (size(cols) == 0) {
        intersect(t1.schema().colNames, t2.schema().colNames) exclude `ID
    } else {
        invalidCols = cols[not contains(t1.schema().colNames, cols) || not contains(t2.schema().colNames, cols)]
        if (size(invalidCols) > 0) {
            throw("指定字段 " + str(invalidCols) + " 不存在于其中一张表")
        }
        cols exclude `ID
    }
    
    // 等值关联两表，仅保留ID和目标对比字段
    joined = ej(`ID, t1[`ID join targetCols], t2[`ID join targetCols])
    
    // 筛选出存在字段值差异的记录
    diffFilter = any(
        each(colName -> joined[colName] != joined[colName + "_t2"], targetCols)
    )
    diffTable = select ID, * from joined where diffFilter
    
    // 将宽表转置为长表，整理成需求的输出格式
    t1Unpivot = unpivot(diffTable, targetCols, `ID, `Field, `TableA_Value)
    t2Unpivot = unpivot(t2[`ID join targetCols], targetCols, `ID, `Field, `TableA1_Value)
    result = ej([`ID, `Field], t1Unpivot, t2Unpivot)
    
    // 过滤掉无差异的行（避免转置后出现的冗余）
    result = select * from result where TableA_Value != TableA1_Value
    
    return result
}

代码说明 #

1. 主键校验：默认以ID作为两表关联的唯一标识，确保同ID记录能正确匹配；
2. 字段处理：自动适配默认对比全部字段的场景，同时校验指定字段是否在两表中都存在；
3. 差异筛选：通过等值关联后直接对比字段值，快速定位有差异的记录；
4. 格式整理：使用unpivot将宽表转为长表，最终输出符合需求的差异明细。

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