PostgreSQL使用Crosstab函数展平标准化钻井数据遇阻求助

修复CASE WHEN重复行问题 #

你遇到的重复行是因为同一个datasource、hole_ID、depth_from、depth_to键组合下存在多条不同field_sgq的记录，直接用CASE WHEN会为每条记录生成一行。解决方式是结合聚合函数（如MAX、SUM，根据数据类型选择）和GROUP BY，将同一键组合的多条记录合并为一行：

SELECT
  datasource,
  hole_ID,
  depth_from,
  depth_to,
  -- 替换为你的实际field_sgq值，value为视图中存储参数值的列名
  MAX(CASE WHEN field_sgq = '密度' THEN value END) AS 密度,
  MAX(CASE WHEN field_sgq = '电阻率' THEN value END) AS 电阻率,
  MAX(CASE WHEN field_sgq = '孔隙度' THEN value END) AS 孔隙度
  -- 继续添加需要展平的参数
FROM tblvw_wadl4_a117888_norm
GROUP BY datasource, hole_ID, depth_from, depth_to;

如果同一键+参数组合有重复值，根据业务需求选择合适的聚合函数：数值类型用MAX/AVG，文本类型用MAX（取非空值）。

正确使用Crosstab函数 #

Crosstab未达预期通常是因为源查询格式不对、未排序或列定义不匹配。按以下步骤操作：

1. 先启用tablefunc扩展（首次使用需执行）：

CREATE EXTENSION IF NOT EXISTS tablefunc;

2. 编写标准Crosstab查询：
   Crosstab要求源查询返回三部分：行标识（你的四个键字段）、分类字段（field_sgq）、值字段（参数值），且必须按行标识+分类字段排序：

SELECT *
FROM crosstab(
  -- 源查询：生成行标识、分类、值三部分
  $$
  SELECT
    datasource,
    hole_ID,
    depth_from,
    depth_to,
    field_sgq,
    value
  FROM tblvw_wadl4_a117888_norm
  -- 必须按行标识和分类排序，避免结果混乱
  ORDER BY 1, 2, 3, 4, 5
  $$,
  -- 可选：指定要展平的field_sgq列表，确保列顺序固定
  $$
  SELECT DISTINCT field_sgq
  FROM tblvw_wadl4_a117888_norm
  ORDER BY field_sgq
  $$
) AS ct(
  -- 定义输出列，顺序要和第二个查询返回的field_sgq顺序一致
  datasource VARCHAR,
  hole_ID VARCHAR,
  depth_from NUMERIC,
  depth_to NUMERIC,
  密度 NUMERIC,
  电阻率 NUMERIC,
  孔隙度 NUMERIC
  -- 按field_sgq顺序添加后续列，类型要匹配value的实际类型
);

注意事项 #

• 如果同一键+field_sgq组合存在多条记录，Crosstab只会取第一条，需先在源查询中聚合（如用MAX(value) GROUP BY ...）。
• 输出列的类型必须和value字段类型一致，否则会报错。
• 不指定第二个查询时，Crosstab会按源查询中分类出现的顺序生成列，顺序可能不稳定，建议显式指定。

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