Postgres Tips:宽转长的常规与动态 SQL 实现

本文结合Unpivot with Postgres^[1] 和Dynamic unpivot with Postgres^[2]翻译整理

前文介绍过了PostgreSQL 实现动态行转列的方法汇总(返回 json，而不是 SQL 的方案见PostgreSQL Tips: 动态 SQL)，但有时有必要规范化非规范化的表，这刚好与交叉表
或Pivot
操作相反。Postgres 不支持像 Oracle 或 SQL Server 这样的UNPIVOT
运算符，但是对其进行模拟非常简单。

例子

例如取得存储每季度汇总值的下表：

create table customer_turnover
(
  customer_id   integer,
  q1            integer,
  q2            integer,
  q3            integer,
  q4            integer
);

以及以下示例数据：

customer_id | q1  | q2  | q3  | q4
------------+-----+-----+-----+----
          1 | 100 | 210 | 203 | 304
          2 | 150 | 118 | 422 | 257
          3 | 220 | 311 | 271 | 269

但是我们希望 quarter 是行（因为它们应该在规范化数据模型中）。

常规实现

在 Oracle 或 SQL Server 中，可以使用 UNPIVOT 运算符来实现，但这在 Postgres 中不可用。但是 PostgresVALUES
像表一样使用子句的能力实际上使这很容易：

select c.customer_id, t.*
from customer_turnover c
  cross join lateral (
     values
       (c.q1, 'Q1'),
       (c.q2, 'Q2'),
       (c.q3, 'Q3'),
       (c.q4, 'Q4')
  ) as t(turnover, quarter)
order by customer_id, quarter;

将返回以下结果：

customer_id | turnover | quarter
------------+----------+--------
          1 |      100 | Q1
          1 |      210 | Q2
          1 |      203 | Q3
          1 |      304 | Q4
          2 |      150 | Q1
          2 |      118 | Q2
          2 |      422 | Q3
          2 |      257 | Q4
          3 |      220 | Q1
          3 |      311 | Q2
          3 |      271 | Q3
          3 |      269 | Q4

使用标准 UNPIVOT 运算符的等效查询为：

select customer_id, turnover, quarter
from customer_turnover c
  UNPIVOT (turnover for quarter in (q1 as 'Q1',
                                    q2 as 'Q2',
                                    q3 as 'Q3',
                                    q4 as 'Q4'))
order by customer_id, quarter;

动态 SQL 实现，借助 JSON

使用 Postgres 的 JSON 函数，可以通过以下方式完成数据透视（和转换）：

select c.customer_id, t.quarter, t.value
from customer_turnover c
  cross join lateral
  jsonb_each_text(to_jsonb(c) - 'customer_id')
  as t(quarter, value);
order by customer_id, t.quarter;

该表达式 to_jsonb(c)会将整个行转换为 JSON 值，其中列名是键。这些值将转换为文本。-'customer_id'然后该表达式删除键，"customer_id"以便仅将我们感兴趣的列保留在 JSON 值中。

jsonb_each_text()然后，该函数将此 JSON 值转换为具有两列（键和值）的多行。

因此以上将返回相同结果。

这样做的好处是，新列将自动包含在输出中，而无需更改 SQL 查询，我们还可以借此封装我们的 UNPIVOT 函数

参考资料