PostgreSQL中能否对unnest进行过滤？如何实现动态年份行转列

原写法存在的问题 #

1. FILTER 子句仅支持和聚合函数搭配使用，unnest 属于行生成函数，不支持直接加FILTER，语法本身不合法。
2. 你当前的写法会把所有年份的数值合并到同一列，无法实现每一年单独成列的需求。

解决方案 #

方案1：动态SQL实现真正的动态列输出

如果需要直接返回列名是年份的结构化表，用动态SQL拼接即可，新增年份无需修改配置。

注意：使用crosstab前需要先开启扩展，执行 CREATE EXTENSION IF NOT EXISTS tablefunc; 即可。

DO $$
DECLARE
    year_cols text;
    year_sel text;
    query text;
BEGIN
    -- 动态获取所有年份的列定义
    SELECT string_agg(format('%s numeric', quote_ident(TAHUN::text)), ', '),
           string_agg(format('t.%s', quote_ident(TAHUN::text)), ', ')
    INTO year_cols, year_sel
    FROM vw_blm_dashboard_hasil_usaha
    WHERE TAHUN != 1000;

    -- 拼接完整查询语句
    query := format('
    SELECT 
        title, 
        %s,
        total
    FROM crosstab(
        -- 源数据：先把原宽表转成长表，再关联总计行数据
        ''SELECT
            a.title,
            a.TAHUN,
            a.value,
            b.total
        FROM (
            SELECT
                TAHUN,
                unnest(array[
                    ''''PENDAPATAN'''', ''''PENJUALAN 1'''', ''''PENJUALAN 2'''', 
                    ''''PENDAPATAN LAIN-LAIN'''', ''''TOTAL PENDAPATAN'''', 
                    ''''HARGA POKOK PENJUALAN (HPP)'''', ''''PEMBELIAN'''', ''''ONGKOS KIRIM''''
                ]) AS title,
                unnest(array[
                    "PENDAPATAN", "PENJUALAN 1", "PENJUALAN 2", 
                    "PENDAPATAN LAIN-LAIN", "TOTAL PENDAPATAN", 
                    "HARGA POKOK PENJUALAN (HPP)", "PEMBELIAN", "ONGKOS KIRIM"
                ]) AS value
            FROM vw_blm_dashboard_hasil_usaha
            WHERE TAHUN != 1000
        ) a
        LEFT JOIN (
            SELECT
                unnest(array[
                    ''''PENDAPATAN'''', ''''PENJUALAN 1'''', ''''PENJUALAN 2'''', 
                    ''''PENDAPATAN LAIN-LAIN'''', ''''TOTAL PENDAPATAN'''', 
                    ''''HARGA POKOK PENJUALAN (HPP)'''', ''''PEMBELIAN'''', ''''ONGKOS KIRIM''''
                ]) AS title,
                unnest(array[
                    "PENDAPATAN", "PENJUALAN 1", "PENJUALAN 2", 
                    "PENDAPATAN LAIN-LAIN", "TOTAL PENDAPATAN", 
                    "HARGA POKOK PENJUALAN (HPP)", "PEMBELIAN", "ONGKOS KIRIM"
                ]) AS total
            FROM vw_blm_dashboard_hasil_usaha
            WHERE TAHUN = 1000
        ) b ON a.title = b.title
        ORDER BY 1, 2'',
        -- 获取所有年份作为列名
        ''SELECT DISTINCT TAHUN FROM vw_blm_dashboard_hasil_usaha WHERE TAHUN != 1000 ORDER BY 1''
    ) AS t (title text, %s, total numeric)
    ', year_sel, year_cols);

    -- 执行查询，如果需要返回结果可以把这段逻辑封装成函数用RETURN QUERY返回
    EXECUTE query;
END $$;

如果不想用数据库端的存储过程，也可以在上层应用中先查询所有非1000的年份，自行拼接SQL后执行，效果一致。

方案2：JSON格式返回（无需动态SQL）

如果上层应用可以处理JSON结构，用这个方案更简单：

SELECT
    title,
    json_object_agg(TAHUN, value) AS year_values,
    MAX(total) AS total
FROM (
    SELECT
        a.title,
        a.TAHUN,
        a.value,
        b.total
    FROM (
        SELECT
            TAHUN,
            unnest(array[
                'PENDAPATAN', 'PENJUALAN 1', 'PENJUALAN 2', 
                'PENDAPATAN LAIN-LAIN', 'TOTAL PENDAPATAN', 
                'HARGA POKOK PENJUALAN (HPP)', 'PEMBELIAN', 'ONGKOS KIRIM'
            ]) AS title,
            unnest(array[
                "PENDAPATAN", "PENJUALAN 1", "PENJUALAN 2", 
                "PENDAPATAN LAIN-LAIN", "TOTAL PENDAPATAN", 
                "HARGA POKOK PENJUALAN (HPP)", "PEMBELIAN", "ONGKOS KIRIM"
            ]) AS value
        FROM vw_blm_dashboard_hasil_usaha
        WHERE TAHUN != 1000
    ) a
    LEFT JOIN (
        SELECT
            unnest(array[
                'PENDAPATAN', 'PENJUALAN 1', 'PENJUALAN 2', 
                'PENDAPATAN LAIN-LAIN', 'TOTAL PENDAPATAN', 
                'HARGA POKOK PENJUALAN (HPP)', 'PEMBELIAN', 'ONGKOS KIRIM'
            ]) AS title,
            unnest(array[
                "PENDAPATAN", "PENJUALAN 1", "PENJUALAN 2", 
                "PENDAPATAN LAIN-LAIN", "TOTAL PENDAPATAN", 
                "HARGA POKOK PENJUALAN (HPP)", "PEMBELIAN", "ONGKOS KIRIM"
            ]) AS total
        FROM vw_blm_dashboard_hasil_usaha
        WHERE TAHUN = 1000
    ) b ON a.title = b.title
) t
GROUP BY title
ORDER BY title;

返回的year_values字段是键为年份、值为对应指标的JSON对象，上层自行解析即可。

内容的提问来源于stack exchange，提问作者Muhammad Rafi Bahrur Rizki