如何用SQL生成基于多分类字段的n×n计数矩阵？

需求说明 #

需要生成一个n×n矩阵：

• n为业务表中所有分类字段的全部唯一值（含处理后的NULL）
• 矩阵单元格数值为同时具备某两个分类值的用户记录计数
• 业务表包含20+分类字段（如gender、tier、age_group等）、50万+用户记录，每个字段有2-10个固定分类值
• 最终用于分析不同分类值间的重叠占比

SQL实现步骤 #

1. 行转列：展开所有用户的分类键值对 #

先将每个用户的所有分类字段转换为(user_id, 分类类型, 分类值)的行格式，同时统一处理NULL值为字符串'null'（避免分组时NULL被单独对待）。

WITH user_categories AS (
    -- 逐个添加所有分类字段，替换your_table为实际表名
    SELECT user_id, 'gender' AS category_type, gender AS category_value FROM your_table
    UNION ALL
    SELECT user_id, 'tier' AS category_type, COALESCE(tier, 'null') AS category_value FROM your_table
    UNION ALL
    SELECT user_id, 'age_group' AS category_type, age_group AS category_value FROM your_table
    UNION ALL
    -- 继续添加其他分类字段：Marital_status、Income_band、Education_level等
    SELECT user_id, 'Marital_status' AS category_type, Marital_status AS category_value FROM your_table
    SELECT user_id, 'Income_band' AS category_type, Income_band AS category_value FROM your_table
    -- ... 剩余分类字段
)

2. 统计两两分类值的共同用户数 #

通过自连接关联同一用户的分类记录，分组统计任意两个分类值的重叠用户数。

, category_pair_counts AS (
    SELECT
        a.category_value AS row_category,
        b.category_value AS col_category,
        COUNT(DISTINCT a.user_id) AS overlap_count
    FROM user_categories a
    JOIN user_categories b ON a.user_id = b.user_id
    GROUP BY a.category_value, b.category_value
)

3. 生成矩阵格式结果（行转列） #

由于分类值是动态的，推荐使用动态SQL自动生成所有列；如果是固定分类值，也可以用静态CASE语句实现。

静态示例（已知部分分类值）

SELECT
    row_category,
    COALESCE(SUM(CASE WHEN col_category = 'male' THEN overlap_count ELSE 0 END), 0) AS male,
    COALESCE(SUM(CASE WHEN col_category = 'female' THEN overlap_count ELSE 0 END), 0) AS female,
    COALESCE(SUM(CASE WHEN col_category = 'other' THEN overlap_count ELSE 0 END), 0) AS other,
    COALESCE(SUM(CASE WHEN col_category = 'tier1' THEN overlap_count ELSE 0 END), 0) AS tier1,
    COALESCE(SUM(CASE WHEN col_category = 'tier2' THEN overlap_count ELSE 0 END), 0) AS tier2,
    -- 继续添加所有其他分类值
    COALESCE(SUM(CASE WHEN col_category = row_category THEN overlap_count ELSE 0 END), 0) AS total
FROM category_pair_counts
GROUP BY row_category
UNION ALL
-- 添加总计行
SELECT
    'total' AS row_category,
    SUM(CASE WHEN col_category = 'male' THEN overlap_count ELSE 0 END) AS male,
    SUM(CASE WHEN col_category = 'female' THEN overlap_count ELSE 0 END) AS female,
    SUM(CASE WHEN col_category = 'other' THEN overlap_count ELSE 0 END) AS other,
    SUM(CASE WHEN col_category = 'tier1' THEN overlap_count ELSE 0 END) AS tier1,
    SUM(CASE WHEN col_category = 'tier2' THEN overlap_count ELSE 0 END) AS tier2,
    -- 继续添加所有其他分类值
    SUM(overlap_count) AS total
FROM category_pair_counts
GROUP BY 'total'

动态SQL示例（PostgreSQL）

自动生成所有分类值对应的列，无需手动维护：

WITH all_categories AS (
    SELECT DISTINCT category_value FROM (
        SELECT gender AS category_value FROM your_table
        UNION ALL SELECT COALESCE(tier, 'null') FROM your_table
        UNION ALL SELECT age_group FROM your_table
        -- 补充其他分类字段
        UNION ALL SELECT Marital_status FROM your_table
        UNION ALL SELECT Income_band FROM your_table
    ) t
),
pivot_columns AS (
    SELECT string_agg(
        format('COALESCE(SUM(CASE WHEN col_category = ''%s'' THEN overlap_count ELSE 0 END), 0) AS %I', category_value, category_value),
        ', '
    ) AS col_sql
    FROM all_categories
),
total_columns AS (
    SELECT string_agg(
        format('SUM(CASE WHEN col_category = ''%s'' THEN overlap_count ELSE 0 END) AS %I', category_value, category_value),
        ', '
    ) AS total_col_sql
    FROM all_categories
)
SELECT format(
    'WITH user_categories AS (
        SELECT user_id, ''gender'' AS category_type, gender AS category_value FROM your_table
        UNION ALL
        SELECT user_id, ''tier'' AS category_type, COALESCE(tier, ''null'') AS category_value FROM your_table
        UNION ALL
        SELECT user_id, ''age_group'' AS category_type, age_group AS category_value FROM your_table
        -- 补充其他分类字段
        UNION ALL
        SELECT user_id, ''Marital_status'' AS category_type, Marital_status AS category_value FROM your_table
        UNION ALL
        SELECT user_id, ''Income_band'' AS category_type, Income_band AS category_value FROM your_table
    ),
    category_pair_counts AS (
        SELECT
            a.category_value AS row_category,
            b.category_value AS col_category,
            COUNT(DISTINCT a.user_id) AS overlap_count
        FROM user_categories a
        JOIN user_categories b ON a.user_id = b.user_id
        GROUP BY a.category_value, b.category_value
    )
    SELECT row_category, %s, COALESCE(SUM(CASE WHEN col_category = row_category THEN overlap_count ELSE 0 END), 0) AS total
    FROM category_pair_counts
    GROUP BY row_category
    UNION ALL
    SELECT ''total'' AS row_category, %s, SUM(overlap_count) AS total
    FROM category_pair_counts
    GROUP BY ''total'';',
    (SELECT col_sql FROM pivot_columns),
    (SELECT total_col_sql FROM total_columns)
) AS dynamic_sql \gexec;

关键注意事项 #

1. NULL值处理：用COALESCE将所有分类字段的NULL替换为'null'字符串，确保矩阵中NULL值作为统一分类显示。
2. 性能优化：50万用户×20+分类字段会生成1000万+行的中间表，建议给user_id加索引；若数据有重复，可在user_categories中添加DISTINCT去重。
3. 分类值冲突：如果不同分类字段有相同取值（如某字段也有'male'），可给分类值添加字段前缀（如'gender_male'、'tier_tier1'），修改user_categories中的category_value为CONCAT(category_type, '_', category_value)即可。
4. 数据库兼容性：动态SQL语法因数据库而异，MySQL需用PREPARE/EXECUTE，SQL Server需用sp_executesql，上述示例为PostgreSQL版本，需根据实际数据库调整。

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