mysql - 如何进行这样的查询以进行数据透视/汇总？

我将如何查询该数据集：

SessionId | GameMode | Score
----------|----------|-------
1         | 1        | 100
1         | 1        | 90
1         | 2        | 20
1         | 2        | 15
1         | 3        | 5
1         | 3        | 5
2         | 1        | 90
2         | 1        | 80
2         | 2        | 15
2         | 2        | 15
2         | 3        | 2
2         | 3        | 4

把它变成：

SessionId | GameMode1AvgScore  | GameMode2AvgScore | GameMode3AvgScore
----------|--------------------|-------------------|-------------------
1         | 95                 | 17.5              | 5
2         | 85                 | 15                | 3

为了澄清，我不是在问如何对行进行分组或聚合，我的问题的核心是如何将GameMode行旋转到列中？

最佳答案

使用MySQL，要返回指定的结果集，我们需要一个返回四列的查询（SELECT语句）。

 SELECT expr1    AS SessionId
      , expr2    AS GameMode1AvgScore
      , expr3    AS GameMode2AvgScore
      , expr4    AS GameMode3AvgScore
 FROM ...
GROUP BY SessionId

MySQL尚未提供“ PIVOT”（ala SQL Server和PostgreSQL）或“ MODEL”（ala Oracle）功能。

因此，就通过SQL SELECT语句实现此结果而言，我们剩下的是老式的条件聚合，

 AVG(CASE WHEN t.GameMode = 1 THEN t.Score ELSE NULL END) AS GameMode1AvgScore

这似乎属于您没有问过的“如何对行进行分组或聚合”存储桶。我们可以使用第二条SQL语句来帮助我们构建需要运行的SQL语句，但是似乎仍然属于“如何对行进行分组或聚合”。实际上，任何产生指定结果的SQL语句都将需要汇总行。

因此，从MySQL工具带中排除该工具后，我们得到一个答案：不可能将GamerMode值“透视”到列中。



返回指定结果的语句可能像这样：

 SELECT t.sessionid                                              AS SessionId
      , AVG(CASE WHEN t.GameMode = 1 THEN t.Score ELSE NULL END) AS GameMode1AvgScore
      , AVG(CASE WHEN t.GameMode = 2 THEN t.Score ELSE NULL END) AS GameMode2AvgScore
      , AVG(CASE WHEN t.GameMode = 3 THEN t.Score ELSE NULL END) AS GameMode3AvgScore
    --                             ^                                        ^
 FROM t
GROUP BY t.sessionid
ORDER BY t.sessionid

请注意，返回平均得分的表达式是相同的模式，我们只是在替换GameMode的值。

为了帮助我们编写查询，我们可以运行初始查询以获取来自同一表的GameMode值的不同列表。

SELECT q.GameMode FROM t q GROUP BY q.GameMode ORDER BY q.GameMode

或者我们可以查询GameMode的“维度”表（如果存在）。

我们遍历结果集（GameMode值）并生成返回每个GameMode的平均分数所需的表达式。我们可以使用它来构造SQL语句。