如何自动实现多行SQL表的one-hot编码（千级类别）

嘿，碰到类别数量上千的情况，手动写列肯定不现实，咱们用动态SQL来自动生成one-hot编码就对了！下面针对几种主流SQL数据库给出具体方案：

1. MySQL 方案 #

MySQL没有原生的透视函数，咱们用动态SQL拼接所有类别列：

步骤1：生成并执行动态SQL #

SET @sql = NULL;

-- 先获取所有唯一的Cat类别，拼接成one-hot列的逻辑语句
SELECT GROUP_CONCAT(DISTINCT
    CONCAT(
        'MAX(CASE WHEN Cat = ''',
        Cat,
        ''' THEN 1 ELSE 0 END) AS `',
        Cat,
        '`'
    )
) INTO @sql
FROM your_table_name;

-- 拼接完整的查询SQL
SET @sql = CONCAT('SELECT ID, ', @sql, ' FROM your_table_name GROUP BY ID ORDER BY ID');

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

说明 #

• GROUP_CONCAT会自动遍历所有Cat类别，把每个类别转换成对应的one-hot标记逻辑
• 执行后会自动生成所有Cat作为列，每个ID对应的类别标记为1，未出现的类别标记为0

2. PostgreSQL 方案 #

PostgreSQL可以借助jsonb特性更灵活地处理大量类别，结合动态SQL自动生成列：

动态SQL完整版本 #

DO $$
DECLARE
    col_list TEXT;
BEGIN
    -- 自动生成所有类别对应的列表达式
    SELECT string_agg(DISTINCT 'COALESCE((cats->>''' || Cat || ''')::INT, 0) AS ' || quote_ident(Cat), ', ')
    INTO col_list
    FROM your_table_name;

    -- 执行动态查询
    EXECUTE format('
        SELECT ID, %s
        FROM (
            SELECT ID, jsonb_object_agg(Cat, 1) AS cats
            FROM your_table_name
            GROUP BY ID
        ) AS sub
        ORDER BY ID', col_list);
END $$;

说明 #

• 先通过jsonb_object_agg把每个ID的所有Cat聚合为json对象，再展开成对应列
• COALESCE用来把未出现的类别值从NULL转为0

3. SQL Server 方案 #

SQL Server有PIVOT函数，结合动态SQL自动生成透视列：

动态PIVOT方案 #

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);

-- 获取所有唯一的Cat类别，拼接成列名字符串
SELECT @cols = STRING_AGG(QUOTENAME(Cat), ', ')
FROM (SELECT DISTINCT Cat FROM your_table_name) AS cats;

-- 拼接带PIVOT的完整查询
SET @query = '
SELECT ID, ' + @cols + '
FROM (
    SELECT ID, Cat, 1 AS value
    FROM your_table_name
) AS source
PIVOT (
    MAX(value)
    FOR Cat IN (' + @cols + ')
) AS pivot_table
ORDER BY ID';

-- 执行动态查询
EXEC sp_executesql @query;

优化：把NULL转为0 #

如果需要把未出现的类别值从NULL转为0，可以在动态SQL里自动拼接ISNULL：

-- 修改列名拼接逻辑
SELECT @cols = STRING_AGG('ISNULL(' + QUOTENAME(Cat) + ', 0) AS ' + QUOTENAME(Cat), ', ')
FROM (SELECT DISTINCT Cat FROM your_table_name) AS cats;

这些方案都能自动适配上千个Cat类别，不用手动写每一列，把your_table_name换成你的实际表名就行！

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