将SQL查询结果转置，使得一个列分散到多个列中。

Question

我正在尝试以特定格式从一张调查表中获取数据。然而，由于连接过多/对数据库负担过重，我的所有尝试似乎都会导致数据库崩溃。

我的数据长这样:

id, user, question_id, answer_id, 
1,   1,   1,           1
3,   1,   3,           15
4,   2,   1,           2
5,   2,   2,           12
6,   2,   3,           20

大约有25万行，每个用户大约有30行。我希望结果看起来像这样：

user0, q1, q2,   q3 
1,     1,  NULL, 15
2,     2,  12,   20 

每个用户都需要在结果中有一行，每个答案都需要独立的列。

我正在使用Postgres，但是任何SQL语言的答案都可以，因为我可以将其转换为Postgres。

注：我还需要解决用户未回答问题的情况，例如上面示例中用户1的q2。

- Yule

---

在这里搜索交叉表关键字例如：https://dev59.com/c3A75IYBdhLWcg3w-OVA - Pavel Stehule

Answer 1

考虑以下演示：

CREATE TEMP TABLE qa (id int, usr int, question_id int, answer_id int);
INSERT INTO qa VALUES
 (1,1,1,1)
,(2,1,2,9)
,(3,1,3,15)
,(4,2,1,2)
,(5,2,2,12)
,(6,2,3,20);

SELECT *
FROM   crosstab('
    SELECT usr::text
          ,question_id
          ,answer_id
    FROM qa
    ORDER BY 1,2')
 AS ct (
     usr text
    ,q1 int
    ,q2 int
    ,q3 int);

结果：

 usr | q1 | q2 | q3
-----+----+----+----
 1   |  1 |  9 | 15
 2   |  2 | 12 | 20
(2 rows)

user是一个保留字，不要将其用作列名！我把它重命名为usr。

您需要安装额外的模块tablefunc，该模块提供了函数crosstab()。请注意，此操作严格适用于每个数据库。 在PostgreSQL9.1中，您可以简单地执行以下操作：

CREATE EXTENSION tablefunc;

对于旧版本，您需要执行在contrib目录中提供的shell脚本。 在Debian中，对于PostgreSQL8.4，应该是这样的：

psql mydb -f /usr/share/postgresql/8.4/contrib/tablefunc.sql

- Erwin Brandstetter

---

这是我最初去的地方，但我很难处理缺失的数据，因为问题已被编辑。因此，如果用户没有问题2的行，则仍应输出null（或0）。无论如何，您的努力值得点赞 :) - Yule

交叉表的语法可能有些具有挑战性......您需要为每个用户的每个答案返回一行，以便正确地填充它。在交叉表报告中引号区域内，构建一个表格（子查询可以处理它，类似于从用户内连接问题1 = 1。记住按顺序！）。然后左连接回到您的数据以填充值。否则，用户缺少的答案将错误地偏移字段。 - Twelfth

@Twelfth：有一个带有两个参数的变量crosstab(text, text)，通过在第二个参数中提供显式类别列表来处理缺失的类别。更多详细信息请参见这个最近的答案。 - Erwin Brandstetter

@ErwinBrandstetter：谢谢，这实际上大大简化了这个问题。 - Twelfth

Answer 2

Erwin的回答很好，但当一个用户的缺失答案出现时，他就无法解释了。 我要做一个假设......你有一个用户表，每个用户有一行，你有一个问题表，每个问题有一行。

select usr, question_id
from users u inner join questions q on 1=1
order by 1,

这个语句将为每个用户/问题创建一行，并按相同顺序排列。将其转换为子查询并左连接到您的数据中...

select usr,question_id,qa.answer_id
from
(select usr, question_id
from users u inner join questions q on 1=1
)a
left join qa on qa.usr = a.usr and qa.question_id = a.usr
order by 1,2

将其插入Erwins的交叉表语句中，并给他答案的功劳:P

- Twelfth

---

只是要补充一点...您需要为返回的每个字段定义交叉表结果。如果您有3个答案，则AS ct ( usr text ,q1 int ,q2 int ,q3 int);可以工作。如果您有30个答案，请准备好像这样定义每一个。如果添加了新问题，交叉表选择语句将捕获它，但您需要确保将该字段添加到此ct()列表中。 - Twelfth

Answer 3

我实现了一个真正动态的函数来处理这个问题，而不需要硬编码任何特定数量的问题或使用外部模块/扩展。它也比更简单易用。

你可以在这里找到它: https://github.com/jumpstarter-io/colpivot

解决这个特定问题的示例:

begin;

create temp table qa (id int, usr int, question_id int, answer_id int);
insert into qa values
 (1,1,1,1)
,(2,1,2,9)
,(3,1,3,15)
,(4,2,1,2)
,(5,2,2,12)
,(6,2,3,20);

select colpivot('_output', $$
    select usr, ('q' || question_id::text) question_id, answer_id from qa
$$, array['usr'], array['question_id'], '#.answer_id', null);

select * from _output;

rollback;

结果：

 usr | 'q1' | 'q2' | 'q3' 
-----+------+------+------
   1 |    1 |    9 |   15
   2 |    2 |   12 |   20
(2 rows)

- Hannes Landeholm