如何通过GROUP BY批量计算多字符变量的ASSISTANCE分组占比？

问题背景 #

现有source_table，包含约30个字符列与数值列ASSISTANCE（取值1-3），示例表结构如下：

需求：对每个字符列，按ASSISTANCE分组，计算该字符列每个取值下各ASSISTANCE的占比（分子为该字符值+ASSISTANCE分组的记录数，分母为该字符值的总记录数），最终生成堆叠式结果表，包含Variable（字符列名）、Variable_value（列取值）、ASSISTANCE、count、char_pct字段，示例输出如下：

已有单变量实现的SQL代码（两种写法）：

写法1：JOIN子查询计算占比 #

select split.Question_response as Question_response
,split.ASSISTANCE as ASSISTANCE
,split.count as count
,(100.0 * split.count)/total.count as char_pct
from
(select Question_response, ASSISTANCE, count(*) as count
from source_table
group by Question_response, ASSISTANCE
) as split
join
(select Question_response, count(*) as count
from source_table
group by Question_response
) as total
on total.Question_response = split.Question_response
order by Question_response, ASSISTANCE

写法2：窗口函数计算占比（更简洁） #

select Question_response
,ASSISTANCE
,count(*) as count
,100.0 * count(*)/sum(count(*)) over (partition by Question_response) as char_pct
from source_table
group by Question_response, ASSISTANCE
order by Question_response, ASSISTANCE

自动化批量实现方案 #

方案1：动态SQL生成（适用于MySQL、PostgreSQL、SQL Server等关系型数据库） #

核心思路是查询数据库元数据获取所有字符列名，自动生成UNION ALL拼接的批量处理SQL语句。

PostgreSQL版本

WITH char_columns AS (
    SELECT column_name
    FROM information_schema.columns
    WHERE table_name = 'source_table'
      AND table_schema = 'public' -- 替换为你的数据表所属schema
      AND data_type IN ('character varying', 'text', 'char') -- 根据数据库调整字符类型
      AND column_name != 'ASSISTANCE' -- 排除目标数值列
)
SELECT string_agg(
    format(
        $$
        SELECT '%s' AS Variable,
               %s AS Variable_value,
               ASSISTANCE,
               count(*) AS count,
               100.0 * count(*) / sum(count(*)) OVER (PARTITION BY %s) AS char_pct
        FROM source_table
        GROUP BY %s, ASSISTANCE
        $$,
        column_name,
        column_name,
        column_name,
        column_name
    ),
    ' UNION ALL '
) AS dynamic_sql
FROM char_columns;

执行后复制生成的完整SQL语句，再次执行即可得到堆叠式结果。

MySQL版本

SELECT GROUP_CONCAT(
    CONCAT(
        "SELECT '", column_name, "' AS Variable, ",
        column_name, " AS Variable_value, ",
        "ASSISTANCE, COUNT(*) AS count, ",
        "100.0 * COUNT(*) / SUM(COUNT(*)) OVER (PARTITION BY ", column_name, ") AS char_pct ",
        "FROM source_table GROUP BY ", column_name, ", ASSISTANCE"
    ) SEPARATOR ' UNION ALL '
) AS dynamic_sql
FROM information_schema.columns
WHERE table_schema = DATABASE()
  AND table_name = 'source_table'
  AND data_type IN ('varchar', 'char', 'text')
  AND column_name != 'ASSISTANCE';

执行后复制生成的SQL语句运行即可。

方案2：Python pandas实现（适合非SQL环境或灵活处理场景） #

通过Python读取表数据，循环处理每个字符列后合并结果：

import pandas as pd
import sqlalchemy

# 替换为你的数据库连接信息
engine = sqlalchemy.create_engine('postgresql://user:password@host:port/dbname')
df = pd.read_sql_table('source_table', engine)

# 筛选所有字符列，排除ASSISTANCE
char_cols = df.select_dtypes(include=['object']).columns.tolist()
char_cols.remove('ASSISTANCE')

result_list = []
for col in char_cols:
    # 分组统计记录数
    grouped = df.groupby([col, 'ASSISTANCE']).size().reset_index(name='count')
    # 计算占比
    grouped['char_pct'] = 100.0 * grouped['count'] / grouped.groupby(col)['count'].transform('sum')
    # 添加列名标识并调整列顺序
    grouped['Variable'] = col
    grouped = grouped.rename(columns={col: 'Variable_value'})
    grouped = grouped[['Variable', 'Variable_value', 'ASSISTANCE', 'count', 'char_pct']]
    result_list.append(grouped)

# 合并所有结果
final_result = pd.concat(result_list, ignore_index=True)
# 输出到数据库或保存为文件
final_result.to_sql('stacked_assistance_report', engine, if_exists='replace', index=False)
final_result.to_csv('stacked_assistance_report.csv', index=False)

方案3：手动拼接UNION ALL（适合列数较少场景） #

如果字符列数量不多，可基于单变量SQL手动替换列名并拼接：

SELECT 'AGE_GROUP' AS Variable, AGE_GROUP AS Variable_value, ASSISTANCE, COUNT(*) AS count, 100.0 * COUNT(*) / SUM(COUNT(*)) OVER (PARTITION BY AGE_GROUP) AS char_pct FROM source_table GROUP BY AGE_GROUP, ASSISTANCE
UNION ALL
SELECT 'GENDER_TYP' AS Variable, GENDER_TYP AS Variable_value, ASSISTANCE, COUNT(*) AS count, 100.0 * COUNT(*) / SUM(COUNT(*)) OVER (PARTITION BY GENDER_TYP) AS char_pct FROM source_table GROUP BY GENDER_TYP, ASSISTANCE
UNION ALL
-- 依次添加其他字符列的查询语句
SELECT 'Remoteness' AS Variable, Remoteness AS Variable_value, ASSISTANCE, COUNT(*) AS count, 100.0 * COUNT(*) / SUM(COUNT(*)) OVER (PARTITION BY Remoteness) AS char_pct FROM source_table GROUP BY Remoteness, ASSISTANCE

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