如何将子查询结果作为SELECT列输入，实现指定透视表需求

需求说明 #

• 构建透视表：以ip_address为行维度，id为列维度，统计值为对应分组的记录数count
• 新增固定列：status列统一填充字符串'success'
• 新增聚合列：max_id列显示每个ip_address下，记录数最多的那个id

修正后的SQL实现 #

WITH b AS (
    SELECT DISTINCT 
        ip_address,
        FIRST_VALUE(id) OVER (PARTITION BY ip_address ORDER BY count DESC) AS max_id
    FROM (
        SELECT ip_address, id, count(*) AS count
        FROM public.table_1 
        WHERE code = '200' 
        GROUP BY ip_address, id
    ) AS a
)
SELECT 
    ct.*,
    'success' AS status,
    b.max_id
FROM crosstab(
    -- 透视表基础数据查询：生成行(ip)、列(id)、值(count)
    'SELECT ip_address, id, count(*) AS count 
     FROM public.table_1 
     WHERE code = ''200'' 
     GROUP BY 1,2 
     ORDER BY 1,2',
    -- 指定透视表的列顺序（包含空值处理）
    'SELECT COALESCE(id::text, ''null'') 
     FROM public.table_1 
     GROUP BY id 
     ORDER BY id ASC'
) AS ct(
    ip_address text,
    "blank" bigint,
    "306" bigint, 
    "308" bigint, 
    "309" bigint, 
    "310" bigint
)
JOIN b ON ct.ip_address = b.ip_address;

关键逻辑解释 #

1. CTE b的作用：先通过子查询统计每个ip_address+id组合的记录数，再利用FIRST_VALUE窗口函数，按记录数倒序排序后，提取每个ip_address对应的最高记录数的id，作为max_id。
2. crosstab函数生成透视表：PostgreSQL的tablefunc扩展提供的crosstab函数用于行转列，第一个参数是生成行、列、值的基础查询，第二个参数明确指定透视表的列顺序（这里用COALESCE处理空id的情况）。
3. 关联补充扩展列：将透视表结果与CTE b通过ip_address关联，补充固定值的status列和聚合得到的max_id列。

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