从另一表更新拼接列的唯一值/组合计数至目标表

问题背景 #

• table2：包含26列（列名a-z），约200万行数据
• table1：joins字段存储table2中2-7列的列名拼接字符串（比如a,c,e），共约918555行；需要把table2中对应列的唯一值组合数量，更新到table1的result字段

基础SQL直接用joins字段的字符串去分组统计会报错，因为SQL无法将字符串自动解析为列名，必须用动态SQL来处理。

解决方案 #

核心思路 #

先预先生成所有列组合的唯一值计数，存到临时表，再关联table1完成更新——避免重复计算，同时解决列名解析问题。

PostgreSQL 实现步骤 #

1. 创建临时表存储统计结果

CREATE TEMP TABLE col_combo_counts (
    combo TEXT PRIMARY KEY,
    count INT
);

2. 生成动态SQL并执行（自动遍历所有列组合）

DO $$
DECLARE
    rec RECORD;
    sql TEXT;
BEGIN
    FOR rec IN SELECT DISTINCT joins FROM table1 LOOP
        sql := format(
            'INSERT INTO col_combo_counts (combo, count)
             SELECT ''%s'', COUNT(DISTINCT (%s)) FROM table2',
            rec.joins,
            rec.joins
        );
        EXECUTE sql;
    END LOOP;
END $$;

3. 更新table1的result字段

UPDATE table1 t1
SET result = t2.count
FROM col_combo_counts t2
WHERE t1.joins = t2.combo;

MySQL 实现步骤 #

1. 创建临时表

CREATE TEMPORARY TABLE col_combo_counts (
    combo VARCHAR(100) PRIMARY KEY,
    count INT
);

2. 批量生成动态SQL并执行

SET @sql = '';
SELECT GROUP_CONCAT(
    DISTINCT CONCAT(
        'INSERT INTO col_combo_counts (combo, count)
         SELECT ''', joins, ''', COUNT(DISTINCT(', joins, ')) FROM table2;'
    ) SEPARATOR ' '
) INTO @sql
FROM table1;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

3. 关联更新table1

UPDATE table1 t1
JOIN col_combo_counts t2 ON t1.joins = t2.combo
SET t1.result = t2.count;

性能优化提示 #

• 给table2中高频出现的列组合建立复合索引，能大幅加快COUNT(DISTINCT)的查询速度
• 如果table1里有重复的joins值，先去重再生成动态SQL，减少重复计算
• 数据量极大时，可分批次处理列组合，避免一次性占用过多数据库资源

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