sql - 使用 postgres tablefunc crosstab() 计算错误答案

Question

我有一个 View (我们称它为“水果”)，其中有一列正确答案和多项选择考试中的相关错误答案，我想计算最常选择哪些错误答案(容易混淆的答案)那些)。 View 看起来像这样:

correct_answer | wrong_response
-------------------------------
apple          |   pear
apple          |   pear
apple          |   banana
banana         |   apple
banana         |   pear
banana         |   pear
banana         |   pear
pear           |   apple

我想要的是一个数据透视表，它计算相对于正确答案的错误响应，这样列代表正确答案，行代表错误答案的计数。

wrong_response | apple | banana | pear
---------------------------------------
apple          | 0     | 1      | 1
banana         | 1     | 0      | 0
pear           | 2     | 3      | 0

我来过这里before有了这个功能，但我当时并没有试图数数。任何帮助将不胜感激!

编辑:对于 future 的人们，这两种解决方案都有效!然而，条件聚合更灵活。交叉表解决方案仅在您完全具有查询中的所有可能性时才有效。例如，如果您排除梨(或添加猕猴桃)，交叉表解决方案将返回错误。无论您是否排除(或添加当前不存在的)记录，条件聚合都会返回结果。感谢大家的帮助。

Answer 1

假设您已经完成:CREATE EXTENSION tablefunc;

然后通过 crosstab() 函数实现你想要的是:

SELECT *
FROM crosstab('SELECT wrong_response,
                      correct_answer,
                      count(*)
               FROM fruit
               GROUP BY wrong_response, correct_answer 
               ORDER BY wrong_response',

              'SELECT correct_answer
               FROM fruit
               GROUP BY correct_answer
               ORDER BY correct_answer')

AS (wrong_answer varchar(20),
    apple bigint,
    banana bigint,
    pear bigint);

上面的代码会给你下面的结果，这就是你想要的:

注意这里0输出为null，为了得到你想要的，你只需要稍微修改一下select:

SELECT
    wrong_answer,
    coalesce(apple, 0) as apple,
    coalesce(banana, 0) as banana,
    coalesce(pear, 0) as pear
FROM crosstab('SELECT wrong_response,
                      correct_answer,
                      count(*)
               FROM fruit
               GROUP BY wrong_response, correct_answer 
               ORDER BY wrong_response',

              'SELECT correct_answer
               FROM fruit
               GROUP BY correct_answer
               ORDER BY correct_answer')

AS (wrong_answer varchar(20),
    apple bigint,
    banana bigint,
    pear bigint)

上面的内容会让你得到你想要的: