透视表不使用交叉表/表函数

Question

I have a table like this

INPUT

id    author    size    file_ext
--------------------------------
1     a         13661   python
1     a         13513   cpp
1     a         1211    non-code
2     b         1019    python
2     b         6881    cpp
2     b         1525    python
2     b         1208    non-code
3     c         1039    python 
3     c         1299    cpp

我希望能够在以下方式中旋转此表格：

输出

id    author    size    python    cpp    non-code
-------------------------------------------------
1     a         13661   1         0      0
1     a         13513   0         1      0
1     a         1211    0         0      1 
2     b         1019    1         0      0
2     b         6881    0         1      0
2     b         1525    1         0      0
2     b         1208    0         0      1
3     c         1039    1         0      0
3     c         1299    0         1      0

我在网上找到的所有关于数据透视表的文章都是基于第二列的。我的最终目标是每个ID只输出一条记录。

最终输出结果

id    author    size    python    cpp    non-code
-------------------------------------------------
1     a         28385   1         1      1
2     b         10633   2         1      1
3     c         2338    1         1      0

这里汇总了大小、Python、C++和非代码列的值。

- sm1994

Answer 1

使用条件聚合：

select 
  id, author,
  sum(size) size,
  sum((file_ext = 'python')::int) python,
  sum((file_ext = 'cpp')::int) cpp,
  sum((file_ext = 'non-code')::int) "non-code"
from tablename
group by id, author

查看演示.
结果:

> id | author |  size | python | cpp | non-code
> -: | :----- | ----: | -----: | --: | -------:
>  1 | a      | 28385 |      1 |   1 |        1
>  2 | b      | 10633 |      2 |   1 |        1
>  3 | c      |  2338 |      1 |   1 |        0

- forpas

---

你已经在查询中将所有可能的文件扩展名硬编码了。如果你不知道所有可能的文件扩展名，有没有办法做到这一点？ - djsosofresh

@djsosofresh 目前 PostgreSQL 不支持动态透视（据我所知）。请查看 Mohamad 回答中的链接以获取扩展函数。 - forpas

谢谢回复。看起来Mohomad的答案中提到的交叉表函数也需要定义输出列的名称和类型。 - djsosofresh

Answer 2

虽然要避免使用 crosstab() 函数，但使用聚合 FILTER 子句可以获得最佳性能和清晰的代码：

SELECT id, author
     , sum(size) AS size
     , count(*) FILTER (WHERE file_ext = 'python')   AS python
     , count(*) FILTER (WHERE file_ext = 'cpp')      AS cpp
     , count(*) FILTER (WHERE file_ext = 'non-code') AS "non-code"
FROM   tablename
GROUP  BY id, author;

这是只使用聚合函数的最快方法。详见：

• 对于绝对性能，SUM和COUNT哪个更快？
• 如何简化这个游戏统计查询？

为了实现绝对最佳性能，在这种情况下，crosstab()通常更快-即使更加冗长：

SELECT id, author, size
     , COALESCE(python    , 0) AS python
     , COALESCE(cpp       , 0) AS cpp
     , COALESCE("non-code", 0) AS "non-code"
FROM   crosstab(
$$
SELECT id, author
     , sum(sum(size)) OVER (PARTITION BY id) AS size
     , file_ext
     , count(*) AS ct
FROM   tablename
GROUP  BY id, author, file_ext
ORDER  BY id, author, file_ext
$$
, $$VALUES ('python'), ('cpp'), ('non-code')$$
) AS (id int, author text, size numeric
    , python int, cpp int, "non-code" int);

相同的结果。

db<>fiddle 这里 - 包括中间步骤.

详细说明:

• PostgreSQL 交叉表查询
• 使用 Tablefunc 在多列上进行数据透视

关于聚合函数之上的窗口函数(sum(sum(size)) OVER (...))，请参见：

• 获取连接表列的不同求和

请注意，如果相同的id有多个author，则会出现细微差别：第一个查询在这种情况下返回多行，而crosstab()变量只选择第一个作者。

- Erwin Brandstetter

---

大多数时候，我在关于Postgresql的问题上遇到您高质量的答案之一。这非常受欢迎。谢谢！ - Ludovic Kuty

Answer 3

您还可以使用名为tablefunc的PostgreSQL交叉表函数扩展。我建议您查看此链接（其中给出的示例非常类似于您想要的结果）：https://vertabelo.com/blog/creating-pivot-tables-in-postgresql-using-the-crosstab-function/

- Mohamad TAGHLOBI