跳至内容

MySQL/透视表

Add links
来自 Wikibooks，开放世界中的开放书籍
 一位 Wikibookian 建议将此书或章节合并到SQL中。
请在讨论页面上讨论是否应该进行此合并。

"透视表"或"交叉表报表"

(注意：此页面需要维基化)

SQL 特征函数：无需使用 "if"、"case" 或 "GROUP_CONCAT" 来实现。是的，这些函数在某些情况下有用...“if”语句有时在组合使用时会导致问题。

简单秘诀，这也是它们几乎在所有数据库中都能正常工作的原因，如下所示：

  • sign (x) 分别针对值 x < 0、x = 0、x > 0 返回 -1、0、+1
  • abs( sign( x) ) 如果 x = 0 则返回 0，否则，如果 x > 0 或 x < 0 则返回 1
  • 1-abs( sign( x) ) 上述内容的补充，因为此函数仅在 x = 0 时返回 1
   Quick example:   sign(-1) = -1,  abs( sign(-1) ) = 1,  1-abs( sign(-1) ) = 0

完整示例数据

      CREATE TABLE exams (
        pkey int(11) NOT NULL auto_increment,
        name varchar(15),
        exam int,
        score int,
        PRIMARY KEY  (pkey)
      );

      insert into exams (name,exam,score) values ('Bob',1,75);
      insert into exams (name,exam,score) values ('Bob',2,77);
      insert into exams (name,exam,score) values ('Bob',3,78);
      insert into exams (name,exam,score) values ('Bob',4,80);

      insert into exams (name,exam,score) values ('Sue',1,90);
      insert into exams (name,exam,score) values ('Sue',2,97);
      insert into exams (name,exam,score) values ('Sue',3,98);
      insert into exams (name,exam,score) values ('Sue',4,99);

mysql> select * from exams;
+------+------+------+-------+
| pkey | name | exam | score |
+------+------+------+-------+
|    1 | Bob  |    1 |    75 |
|    2 | Bob  |    2 |    77 |
|    3 | Bob  |    3 |    78 |
|    4 | Bob  |    4 |    80 |
|    5 | Sue  |    1 |    90 |
|    6 | Sue  |    2 |    97 |
|    7 | Sue  |    3 |    98 |
|    8 | Sue  |    4 |    99 |
+------+------+------+-------+
8 rows in set (0.00 sec)

mysql> select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4
from exams group by name;

+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob  |    75 |    77 |    78 |    80 |
| Sue  |    90 |    97 |    98 |    99 |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)

注意，以上透视表是用一个 select 语句创建的。

为了使技巧更清晰，让我们分解第二个示例

mysql> select name, score, exam, exam-2, sign(exam-2), abs(sign(exam-2)), 1-abs(sign(exam-2)),
       score*(1-abs(sign(exam-2))) as exam2 from exams;
+------+-------+------+--------+--------------+-------------------+---------------------+-------+
| name | score | exam | exam-2 | sign(exam-2) | abs(sign(exam-2)) | 1-abs(sign(exam-2)) | exam2 |
+------+-------+------+--------+--------------+-------------------+---------------------+-------+
| Bob  |    75 |    1 |     -1 |           -1 |                 1 |                   0 |     0 |
| Bob  |    77 |    2 |      0 |            0 |                 0 |                   1 |    77 |
| Bob  |    78 |    3 |      1 |            1 |                 1 |                   0 |     0 |
| Bob  |    80 |    4 |      2 |            1 |                 1 |                   0 |     0 |
| Sue  |    90 |    1 |     -1 |           -1 |                 1 |                   0 |     0 |
| Sue  |    97 |    2 |      0 |            0 |                 0 |                   1 |    97 |
| Sue  |    98 |    3 |      1 |            1 |                 1 |                   0 |     0 |
| Sue  |    99 |    4 |      2 |            1 |                 1 |                   0 |     0 |
+------+-------+------+--------+--------------+-------------------+---------------------+-------+
8 rows in set (0.00 sec)

您可能认为 IF 会很干净，但要注意！看看以下结果（不正确！！）

mysql> select name,
if(exam=1,score,null) as exam1,
if(exam=2,score,null) as exam2,
if(exam=3,score,null) as exam3,
if(exam=4,score,null) as exam4
from exams group by name;

+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob  |    75 |  NULL |  NULL |  NULL |
| Sue  |    90 |  NULL |  NULL |  NULL |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)

注意：以下方法有效 - 毕竟所有的数学运算都是必要的吗？

mysql> SELECT name,
       SUM(IF(exam=1,score,NULL)) AS exam1,
       SUM(IF(exam=2,score,NULL)) AS exam2,
       SUM(IF(exam=3,score,NULL)) AS exam3,
       SUM(IF(exam=4,score,0)) AS exam4
       FROM exams GROUP BY name;
+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob  |    75 |    77 |    78 |    80 |
| Sue  |    90 |    97 |    98 |    99 |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)


mysql> select name,
       sum(score*(1-abs(sign(exam-1)))) as exam1,
       sum(score*(1-abs(sign(exam-2)))) as exam2,
       sum(score*(1-abs(sign(exam-3)))) as exam3,
       sum(score*(1-abs(sign(exam-4)))) as exam4,
         sum(score*(1-abs(sign(exam- 2)))) -   sum(score*(1-abs(sign(exam- 1)))) as delta_1_2,
         sum(score*(1-abs(sign(exam- 3)))) -   sum(score*(1-abs(sign(exam- 2)))) as delta_2_3,
         sum(score*(1-abs(sign(exam- 4)))) -   sum(score*(1-abs(sign(exam- 3)))) as delta_3_4
       from exams group by name;
+------+-------+-------+-------+-------+-----------+-----------+-----------+
| name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 | delta_3_4 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+
| Bob  |    75 |    77 |    78 |    80 |         2 |         1 |         2 |
| Sue  |    90 |    97 |    98 |    99 |         7 |         1 |         1 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+
2 rows in set (0.00 sec)

上面的 delta_1_2 显示了第一次和第二次考试之间的差异，数字为正，因为 Bob 和 Sue 的成绩在每次考试中都有所提高。在这里计算增量表明可以比较两行，而不是列，而列可以用标准 SQL 语句轻松完成，但在原始表中则是行。

mysql>select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,
  sum(score*(1-abs(sign(exam- 2)))) -   sum(score*(1-abs(sign(exam- 1)))) as delta_1_2,
  sum(score*(1-abs(sign(exam- 3)))) -   sum(score*(1-abs(sign(exam- 2)))) as delta_2_3,
  sum(score*(1-abs(sign(exam- 4)))) -   sum(score*(1-abs(sign(exam- 3)))) as delta_3_4,

  sum(score*(1-abs(sign(exam- 2)))) -   sum(score*(1-abs(sign(exam- 1))))  +
  sum(score*(1-abs(sign(exam- 3)))) -   sum(score*(1-abs(sign(exam- 2))))  +
  sum(score*(1-abs(sign(exam- 4)))) -   sum(score*(1-abs(sign(exam- 3))))  as TotalIncPoints
  from exams group by name;

+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+
| name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 | delta_3_4 | TotalIncPoints |
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+
| Bob  |    75 |    77 |    78 |    80 |         2 |         1 |         2 |              5 |
| Sue  |    90 |    97 |    98 |    99 |         7 |         1 |         1 |              9 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+
2 rows in set (0.00 sec)

TotalIncPoints 显示增量的总和。

select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,
  sum(score*(1-abs(sign(exam- 2)))) -   sum(score*(1-abs(sign(exam- 1)))) as delta_1_2,
  sum(score*(1-abs(sign(exam- 3)))) -   sum(score*(1-abs(sign(exam- 2)))) as delta_2_3,
  sum(score*(1-abs(sign(exam- 4)))) -   sum(score*(1-abs(sign(exam- 3)))) as delta_3_4,


  sum(score*(1-abs(sign(exam- 2)))) -   sum(score*(1-abs(sign(exam- 1))))  +
  sum(score*(1-abs(sign(exam- 3)))) -   sum(score*(1-abs(sign(exam- 2))))  +
  sum(score*(1-abs(sign(exam- 4)))) -   sum(score*(1-abs(sign(exam- 3))))  as TotalIncPoints,

(sum(score*(1-abs(sign(exam-1)))) +
sum(score*(1-abs(sign(exam-2)))) +
sum(score*(1-abs(sign(exam-3)))) +
sum(score*(1-abs(sign(exam-4)))))/4 as AVG

from exams group by name;

+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+-------+
| name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 | delta_3_4 | TotalIncPoints | AVG   |
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+-------+
| Bob  |    75 |    77 |    78 |    80 |         2 |         1 |         2 |              5 | 77.50 |
| Sue  |    90 |    97 |    98 |    99 |         7 |         1 |         1 |              9 | 96.00 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+-------+
2 rows in set (0.00 sec)

可以将 Total Increasing Point TotalIncPoints 与 AVG 组合起来。实际上，可以将所有示例数据切片组合到一个 SQL 语句中，这为在页面上显示数据提供了更多选择

select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,

(sum(score*(1-abs(sign(exam-1)))) +
sum(score*(1-abs(sign(exam-2)))))/2  as AVG1_2,

(sum(score*(1-abs(sign(exam-2)))) +
sum(score*(1-abs(sign(exam-3)))))/2 as AVG2_3,

(sum(score*(1-abs(sign(exam-3)))) +
sum(score*(1-abs(sign(exam-4)))))/2 as AVG3_4,

(sum(score*(1-abs(sign(exam-1)))) +
sum(score*(1-abs(sign(exam-2)))) +
sum(score*(1-abs(sign(exam-3)))) +
sum(score*(1-abs(sign(exam-4)))))/4 as AVG

from exams group by name;

+------+-------+-------+-------+-------+--------+--------+--------+-------+
| name | exam1 | exam2 | exam3 | exam4 | AVG1_2 | AVG2_3 | AVG3_4 | AVG   |
+------+-------+-------+-------+-------+--------+--------+--------+-------+
| Bob  |    75 |    77 |    78 |    80 |  76.00 |  77.50 |  79.00 | 77.50 |
| Sue  |    90 |    97 |    98 |    99 |  93.50 |  97.50 |  98.50 | 96.00 |
+------+-------+-------+-------+-------+--------+--------+--------+-------+
2 rows in set (0.00 sec)

考试分数与移动平均值一起列出...同样，这一切都用一个 select 语句完成。

"交叉表"或反规范化数据以显示统计信息的优秀文章：https://dev.mysqlserver.cn/tech-resources/articles/wizard/print_version.html

ADOdb (PHP) 可以使用 PivotTableSQL() 生成透视表。

对于 Perl，请查看 DBIx-SQLCrosstab。

检索自 "https://wikibooks.cn/w/index.php?title=MySQL/Pivot_table&oldid=3266959"
华夏公益教科书