PostgreSQL使用crosstab创建聚合透视表结果不符预期的问题

你的问题出在crosstab函数的行标识符定义和排序逻辑上：

• crosstab要求第一个查询的前两列是唯一的行分组键，你之前把date放在第一列、id第二列，导致同一日期下的不同ID被合并成一行，数据错位。
• 同时查询的排序逻辑和列定义顺序不匹配，进一步导致数据对应错误。

修正后的SQL语句 #

SELECT * FROM crosstab(
    'SELECT id, date, product, sum(product_cost)
     FROM product_info
     GROUP BY 1, 2, 3
     ORDER BY 1, 2, 3'
,
    'SELECT DISTINCT product FROM product_info ORDER BY 1'
) as cte(
    id text,
    date text, 
    "Ipad" numeric,
    "Keypad" numeric,
    "Laptop" numeric,
    "Phone" numeric
);

关键调整点 #

1. 行键顺序调整：将id和date作为crosstab第一个查询的前两列，确保每个id+date组合作为唯一行标识。
2. 排序逻辑匹配：按id, date, product排序，保证crosstab能正确将每个产品的成本值映射到对应行。
3. 列定义同步：修改cte的列定义顺序，与第一个查询的行键顺序一致。

执行结果（符合预期） #

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