pgsql 行列转换|极客教程

pgsql 行列转换

在 PostgreSQL 数据库中，行列转换是一个常见的操作，用于将行数据转换为列数据或将列数据转换为行数据。这在数据处理和分析中非常有用，可以帮助提取和展示数据更加清晰和容易理解。本文将介绍如何在 pgsql 中进行行列转换操作，并为您提供详细的示例代码。

行转列

在 pgsql 中，行转列操作可以通过使用 crosstab 函数来实现。crosstab 函数是在 tablefunc 扩展中定义的，因此在使用之前需要先安装该扩展。

示例代码：

CREATE EXTENSION IF NOT EXISTS tablefunc;

在这个示例中，我们将以 geek-docs.com 网站的访问数据为例，假设数据表结构如下：

访问日期	用户ID	访问次数
2021-10-01	1001	5
2021-10-01	1002	3
2021-10-02	1001	7
2021-10-02	1002	2

我们可以使用以下示例代码来进行行转列操作：

SELECT * FROM crosstab(
  'SELECT "访问日期", "用户ID", "访问次数" FROM 访问数据 ORDER BY 1,2',
  'SELECT DISTINCT "用户ID" FROM 访问数据'
) AS ct("访问日期" date, "1001" int, "1002" int);

运行结果：

访问日期	1001	1002
2021-10-01	5	3
2021-10-02	7	2

从上面的示例中可看出，行数据已经成功转换为列数据，每个用户的访问次数会根据日期显示在对应的列中。

列转行

与行转列相反，列转行操作可以通过 UNION ALL 来实现。在 pgsql 中，我们可以使用 generate_series 函数来生成序列，以便将列数据转换为行数据。

示例代码：

SELECT gs."日期", ud."用户ID", ud."访问次数"
FROM generate_series('2021-10-01'::date, '2021-10-02'::date, '1 day') gs("日期")
CROSS JOIN LATERAL (
  SELECT "用户ID", "访问次数" FROM 访问数据 WHERE "访问日期" = gs."日期"
) ud
ORDER BY gs."日期", ud."用户ID";

运行结果：