PostgreSQL：基于函数结果为表1生成对应表2行的列及异常值列

核心思路 #

由于表2的行数决定了需要生成的动态列数量，必须通过动态SQL实现列的自动生成，同时通过聚合判断所有动态列是否全为NULL来生成is_outlier列。以下以主流数据库为例提供实现方案：

PostgreSQL 实现步骤 #

1. 生成动态列表达式
   先查询表2的所有行，拼接出每个belongs_to_id_another_xxx列的函数调用语句：

   DO $$
   DECLARE
     cols_list text;
     outlier_condition text;
   BEGIN
     -- 生成所有动态列的SQL片段
     SELECT string_agg(
       format(
         'is_loc_inside_cls(t1.val1, t1.val2, %L, %L, %L) AS belongs_to_id_another_%s',
         t2.val1, t2.val2, t2.val3, t2.id_another
       ),
       ', '
     ) INTO cols_list FROM table2 t2;
   
     -- 生成is_outlier的判断条件：所有动态列均为NULL
     SELECT CASE 
       WHEN COUNT(*) = 0 THEN 'TRUE'
       ELSE string_agg('belongs_to_id_another_' || t2.id_another, ' IS NULL AND ') || ' IS NULL'
     END INTO outlier_condition FROM table2 t2;
   
     -- 拼接并执行最终SQL
     EXECUTE format(
       'SELECT t1.id, t1.val1, t1.val2, %s, CASE WHEN %s THEN 1 ELSE NULL END AS is_outlier FROM table1 t1',
       cols_list, outlier_condition
     );
   END $$;
   

2. 关键细节

   • 使用format()函数安全拼接字符串，避免SQL注入
   • 处理表2为空的边界情况：此时is_outlier直接设为1（无匹配列则默认全为NULL）

MySQL 实现步骤 #

1. 生成动态列与判断条件
   通过GROUP_CONCAT拼接SQL片段，再用预处理语句执行：

   -- 生成动态列片段
   SET @cols_list = (
     SELECT GROUP_CONCAT(
       CONCAT(
         'is_loc_inside_cls(t1.val1, t1.val2, ', QUOTE(t2.val1), ', ', QUOTE(t2.val2), ', ', QUOTE(t2.val3), ') AS belongs_to_id_another_', t2.id_another
       )
     ) FROM table2 t2
   );
   
   -- 生成is_outlier判断条件
   SET @outlier_condition = (
     SELECT CASE
       WHEN COUNT(*) = 0 THEN 'TRUE'
       ELSE GROUP_CONCAT(CONCAT('belongs_to_id_another_', t2.id_another, ' IS NULL') SEPARATOR ' AND ')
     END FROM table2 t2
   );
   
   -- 拼接并执行SQL
   SET @sql = CONCAT(
     'SELECT t1.id, t1.val1, t1.val2, ', @cols_list, ', CASE WHEN ', @outlier_condition, ' THEN 1 ELSE NULL END AS is_outlier FROM table1 t1'
   );
   
   PREPARE stmt FROM @sql;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;
   

性能提示 #

由于表1与表2的比例为1000:1，总函数调用次数为表1行数 × 表2行数，需确保is_loc_inside_cls函数逻辑足够高效（比如避免复杂计算或嵌套查询）。若表2行数较多，可考虑预先缓存表2的参数值，减少重复读取。

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