PostgreSQL动态查询：选举结果表列转行适配Plotly整洁数据需求

示例表结构 #

先明确两张核心表的结构与示例数据，方便理解转换逻辑：

1. election_results（宽表存储选举结果） #

CREATE TABLE election_results (
    region_id INT,
    election_date DATE,
    party_a INT,
    party_b INT,
    party_c INT
);

-- 示例数据
INSERT INTO election_results VALUES
(1, '2024-03-10', 1500, 2200, 800),
(2, '2024-03-10', 900, 1800, 1200);

2. parties（存储所有政党名称） #

CREATE TABLE parties (
    party_name VARCHAR(50) PRIMARY KEY
);

-- 示例数据（包含选举结果表未覆盖的政党）
INSERT INTO parties VALUES
('party_a'),
('party_b'),
('party_c'),
('party_d');

解决方案 #

方法1：使用JSONB函数（推荐，无需硬编码列名） #

通过JSONB函数动态解析宽表列，自动匹配parties表中的政党，实现一行转多行的整洁格式转换：

SELECT
    er.region_id,
    er.election_date,
    p.party_name,
    (kv.value)::INT AS votes
FROM election_results er
-- 将非政党列（region_id、election_date）从JSONB对象中移除
CROSS JOIN jsonb_each_text(to_jsonb(er) - 'region_id' - 'election_date') kv
-- 关联政党表，仅保留有效政党记录
JOIN parties p ON kv.key = p.party_name
ORDER BY er.region_id, er.election_date, p.party_name;

逻辑说明：

• to_jsonb(er)将整行数据转为JSONB对象
• - 'region_id' - 'election_date'剔除不需要拆分的非政党字段
• jsonb_each_text将JSONB的键值对拆分为多行，键为政党名，值为得票数字符串
• 关联parties表确保仅处理合法政党，同时自动过滤选举结果表中不存在的政党（如示例中的party_d）

方法2：动态SQL生成UNION ALL（适合精细控制场景） #

如果需要生成明确的UNION ALL语句，可通过动态SQL自动拼接每个政党的查询逻辑：

DO $$
DECLARE
    party_query TEXT;
BEGIN
    -- 从政党表中筛选出选举结果表存在的列，生成UNION ALL子句
    SELECT string_agg(
        format(
            'SELECT region_id, election_date, ''%s'' AS party_name, %s AS votes FROM election_results',
            party_name,
            party_name
        ),
        ' UNION ALL '
    ) INTO party_query
    FROM parties
    WHERE EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = 'election_results' AND column_name = party_name
    );

    -- 执行动态生成的查询
    EXECUTE format('SELECT * FROM (%s) AS tidy_results ORDER BY region_id, election_date, party_name', party_query);
END $$;

最终输出结果 #

两种方法都会生成符合"整洁数据"要求的结果，每行对应一条政党得票记录：

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