postgresql 交叉表简单例子

Question

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

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

我想将它转换成这样的结构:

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

我想可以使用 postgres 内置的 crosstab 函数来做到这一点。

你能告诉我怎么做并解释它为什么有效吗？

Answer 1

首先激活 tablefunc 中的构建 - 扩展:

CREATE EXTENSION tablefunc;

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

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

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

现在让我们准备crosstab 语句:

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 并看看会发生什么:

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     |

当我们更改键的排序时，crosstab 函数会看到键按另一个方向排序，从而反转生成的列。

---

工作原理#2

另一件事帮助我理解了它是如何工作的:列定义都是关于位置的:

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

结果

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