基于列组合计算表得分的SQL查询开发方案咨询

前提假设（需根据实际表结构调整） #

先明确两张表的核心字段（如果你的表结构不同，只需替换对应字段名即可）：

• 表A（元数据表）：记录每个表的字段类型信息
  • Table_Name：目标表名称
  • DE_Type：字段的数据元素类型（如用户ID、手机号、邮箱等）
• 表B（评分规则表）：定义不同数据元素组合对应的得分
  • DE_Type_Combination：数据元素类型的组合字符串（如用户ID,手机号，需和表A生成的组合格式一致）
  • Score：该组合对应的得分值

实现步骤与SQL代码 #

1. 统计每个表的元素数量与类型组合 #

先对表A按表名分组，统计字段总数，并拼接所有DE_Type得到组合字符串（必须排序，避免因顺序不同导致匹配失败）：

WITH Table_DE_Summary AS (
    SELECT
        Table_Name,
        COUNT(*) AS Data_Element_Counts,
        -- 不同数据库用对应的聚合函数，以下是常见数据库写法
        -- PostgreSQL
        STRING_AGG(DE_Type, ',' ORDER BY DE_Type) AS DE_Type_Combination
        -- MySQL
        -- GROUP_CONCAT(DE_Type ORDER BY DE_Type SEPARATOR ',') AS DE_Type_Combination
        -- SQL Server 2017+
        -- STRING_AGG(DE_Type, ',' WITHIN GROUP (ORDER BY DE_Type)) AS DE_Type_Combination
    FROM Table_A
    GROUP BY Table_Name
)

2. 关联评分规则计算最终得分 #

将统计结果与表B关联，匹配组合字符串得到得分，无匹配时默认得0（可按需调整）：

SELECT
    t.Table_Name,
    t.Data_Element_Counts,
    COALESCE(b.Score, 0) AS DE_Score
FROM Table_DE_Summary t
LEFT JOIN Table_B b 
    ON t.DE_Type_Combination = b.DE_Type_Combination;

完整SQL示例（PostgreSQL版本） #

WITH Table_DE_Summary AS (
    SELECT
        Table_Name,
        COUNT(*) AS Data_Element_Counts,
        STRING_AGG(DE_Type, ',' ORDER BY DE_Type) AS DE_Type_Combination
    FROM Table_A
    GROUP BY Table_Name
)
SELECT
    t.Table_Name,
    t.Data_Element_Counts,
    COALESCE(b.Score, 0) AS DE_Score
FROM Table_DE_Summary t
LEFT JOIN Table_B b 
    ON t.DE_Type_Combination = b.DE_Type_Combination;

关键注意点 #

• 组合顺序一致性：必须对DE_Type排序后再拼接，否则用户ID,手机号和手机号,用户ID会被视为不同组合，无法匹配同一规则。
• 数据库兼容性：不同数据库的字符串聚合函数语法不同，需根据实际使用的数据库替换对应的函数。
• 无匹配规则的处理：使用COALESCE函数将无匹配的得分设为0，若需要标记未匹配的记录，可改为返回NULL或自定义默认值。

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