PostgreSQL交叉表简单示例 [英] postgresql crosstab simple example

问题描述

我有一个基于键值的表，其中每个键值对都分配给一个由ID标识的实体：

I got a key-value based table where each key-value pair is assigned to an entity which is identified by an id:

|_id__|_key_______|_value_|
| 123 | FIRSTNAME | John  |
| 123 | LASTNAME  | Doe   |

我想将其转换为这样的结构：

And I want to transform it a structre like this:

|_id__|_firstName_|_lastName_|
| 123 | John      | Doe      |

我想可以在 crosstab

I suppose one can use postgres build in crosstab function to do it.

您能告诉我如何做并解释它为什么起作用吗？

推荐答案

首先在 tablefunc -extension中激活构建：

First of all activate the build in tablefunc-extension:

CREATE EXTENSION tablefunc;

然后创建表并添加示例数据：

Then create table and add sample data:

CREATE TABLE example (
  id int,
  key text,
  value text
);

INSERT INTO example VALUES
  (123, 'firstName', 'John'),
  (123, 'lastName', 'Doe');

现在让我们准备交叉表语句：

SELECT *
FROM example
ORDER BY id ASC, key ASC;

在这里放置 ORDER BY 很重要。

结果：

|_id__|_key_______|_value_|
| 123 | FIRSTNAME | John  |
| 123 | LASTNAME  | Doe   |

---

解决方案

现在 crosstab 根据需要创建表：

SELECT *
FROM crosstab(
    'SELECT *
     FROM example
     ORDER BY id ASC, key ASC;'
) AS ct(id INT, firstname TEXT, lastname TEXT);

结果：

|_id__|_firstName_|_lastName_|
| 123 | John      | Doe      |

---

工作原理＃1

要了解它的工作原理，我发现最简单的方法就是更改 ORDER BY 并查看会发生什么：

---

How it works #1

To however understand how it works I found it easiest to just change the ORDER BY and see what happens:

SELECT *
FROM crosstab(
    'SELECT *
     FROM example
     ORDER BY id ASC, key DESC;'
) AS ct(id INT, firstname TEXT, lastname TEXT);

结果：

|_id__|_firstName_|_lastName_|
| 123 | Doe       | John     |

当我们更改键的排序方式时，交叉表函数会看到按相反方向排序的键，从而反转生成的列。

As we changed the sorting of the key, the crosstab function sees the keys sorted in the other direction, thus reversing the generated columns.

另一件事帮助我了解其工作原理：列定义全都与职位有关：

Another thing that helped me understand how it works: the column definition is all about positions:

SELECT *
FROM crosstab(
    'SELECT *
     FROM example
     ORDER BY id ASC, key ASC;'
) AS ct(blablafirst INT, blablasecond TEXT, blablathird TEXT);

结果

|_blablafirst__|_blablasecond_|_blablathird_|
| 123          | Doe          | John        |

这篇关于PostgreSQL交叉表简单示例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！