PostgreSQL如何编写SELECT查询实现表数据转换为指定格式展示

• 第一步：从完整ID字段拆分出主ID、子ID两个维度，可使用substring正则匹配或者PostgreSQL内置的split_part函数按分隔符拆分
• 第二步：通过条件聚合（CASE WHEN+分组）或交叉表函数实现行转列，将子IDaa、bb对应的指标值从行数据转为同一行的两列
• 第三步：按时间戳、主ID排序输出符合要求的格式

假设原始表名为device_log，三列分别为full_id（存储1_aa/3_bb格式的完整ID）、metric_value（指标值）、collect_ts（采集时间戳），查询代码如下：

SELECT
  collect_ts AS 时间戳,
  substring(full_id from '^[0-9]+') AS 主ID,
  MAX(CASE WHEN substring(full_id from '[a-z]+$') = 'aa' THEN metric_value END) AS aa指标值,
  MAX(CASE WHEN substring(full_id from '[a-z]+$') = 'bb' THEN metric_value END) AS bb指标值
FROM device_log
GROUP BY collect_ts, substring(full_id from '^[0-9]+')
ORDER BY collect_ts, 主ID;

如果更习惯用分隔符拆分，可以替换substring部分为split_part函数：

SELECT
  collect_ts AS 时间戳,
  split_part(full_id, '_', 1) AS 主ID,
  MAX(CASE WHEN split_part(full_id, '_', 2) = 'aa' THEN metric_value END) AS aa指标值,
  MAX(CASE WHEN split_part(full_id, '_', 2) = 'bb' THEN metric_value END) AS bb指标值
FROM device_log
GROUP BY collect_ts, split_part(full_id, '_', 1)
ORDER BY collect_ts, 主ID;

如果数据量较大，可以使用PostgreSQL的crosstab交叉表函数实现，需要先启用tablefunc扩展：

-- 启用扩展
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- 交叉表查询
SELECT * FROM crosstab(
  'SELECT collect_ts, split_part(full_id, ''_'', 1) AS main_id, split_part(full_id, ''_'', 2) AS sub_id, metric_value
   FROM device_log
   ORDER BY 1,2',
  'VALUES (''aa''), (''bb'')'
) AS ct(collect_ts timestamp, main_id text, aa_value numeric, bb_value numeric);

如果需要封装为存储过程复用，可以参考如下函数写法：

CREATE OR REPLACE FUNCTION get_formatted_log()
RETURNS TABLE(
  时间戳 timestamp,
  主ID text,
  aa指标值 numeric,
  bb指标值 numeric
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    collect_ts,
    split_part(full_id, '_', 1),
    MAX(CASE WHEN split_part(full_id, '_', 2) = 'aa' THEN metric_value END),
    MAX(CASE WHEN split_part(full_id, '_', 2) = 'bb' THEN metric_value END)
  FROM device_log
  GROUP BY collect_ts, split_part(full_id, '_', 1)
  ORDER BY collect_ts, 2;
END;
$$ LANGUAGE plpgsql STABLE;

-- 调用函数
SELECT * FROM get_formatted_log();

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