PostgreSQL动态查询：按列名拆分政党票数并重新计算

我使用QGIS搭配带PostGIS的PostgreSQL，需要处理选举数据：表中包含地理区域、各政党得票、选举日期等字段，部分列名用下划线连接多个政党（比如PartyA_PartyB），这类列的得票需要均分给对应政党，累加到各政党的独立列中（例如新PartyA列值 = 原PartyA + PartyA_PartyB/2）。需支持任意数量政党联合的列（如PartyA_PartyB_PartyD_PartyE），将值均分给n个政党。

示例表结构及数据 #

create table election_results ("Country" text,  "PartyA" text,  "PartyB" text,  "PartyC" text, "PartyA_PartyB" text);
insert into election_results
VALUES
  ('Argentina', 100, 10, 20, 2),
  ('Uruguay', 3, 5, 1, 0),
  ('Chile', 40, 200, 50, 10)
;

create table parties (party text);
insert into parties
VALUES
  ('PartyA'),
  ('PartyB'),
  ('PartyC'),
  ('PartyD'),
  ('PartyE')
;

期望结果 #

实现方案 #

核心思路 #

通过动态SQL自动识别联合政党列，拆分列名中的政党名称，计算每个政党的总票数（独立列得票 + 所有包含该政党的联合列得票均分）。

代码实现 #

WITH party_columns AS (
    -- 筛选所有独立政党对应的列
    SELECT column_name
    FROM information_schema.columns
    WHERE table_name = 'election_results'
      AND column_name IN (SELECT party FROM parties)
),
coalition_columns AS (
    -- 识别联合政党列，拆分列名为政党数组并统计政党数量
    SELECT column_name,
           string_to_array(column_name, '_') AS party_list,
           array_length(string_to_array(column_name, '_'), 1) AS party_count
    FROM information_schema.columns
    WHERE table_name = 'election_results'
      AND column_name NOT IN (SELECT party FROM parties)
      AND column_name LIKE '%_%'
),
-- 生成每个政党的总票数计算表达式
party_calculations AS (
    SELECT 
        p.party,
        CONCAT(
            'COALESCE("', p.party, '", 0)::numeric + ',
            STRING_AGG(
                CONCAT('COALESCE("', c.column_name, '", 0)::numeric / ', c.party_count),
                ' + '
            )
        ) AS calculation
    FROM parties p
    LEFT JOIN coalition_columns c ON p.party = ANY(c.party_list)
    GROUP BY p.party
),
-- 拼接最终的创建汇总表SQL语句
final_query AS (
    SELECT CONCAT(
        'CREATE TABLE election_results_aggregated AS SELECT "Country", ',
        STRING_AGG(CONCAT(calculation, ' AS "', party, '"'), ', '),
        ' FROM election_results;'
    ) AS query
    FROM party_calculations
)
-- 执行动态生成的SQL
SELECT execute(query) FROM final_query;

步骤说明 #

1. party_columns：从系统表中筛选出所有属于独立政党的列；
2. coalition_columns：识别带下划线的联合政党列，拆分列名为政党数组并统计数组长度（即参与联合的政党数量）；
3. party_calculations：为每个政党生成总票数计算逻辑，将独立列的得票与所有包含该政党的联合列得票均分后相加；
4. final_query：拼接成创建汇总表的完整SQL语句，最后执行该语句生成结果表。

执行完成后，election_results_aggregated表即为所需的汇总结果。

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