PostgreSQL实现透视表查询

时间：2024-12-02 07:04:07

PostgreSQL 8.3版本发布时，引入了一个名为tablefunc的新扩展。这个扩展提供了一组非常有趣的函数。其中之一是交叉表函数，用于创建数据透视表。这就是我们将在本文中讨论的内容。
在这里插入图片描述

需求说明

解释此函数如何工作的最简单方法是使用带有数据透视表的示例。首先，我们将从实际角度解释我们最初的观点，然后定义所需的数据透视表。

假设我们是老师，需要统计你教所有科目的成绩（语言、音乐等），学校为你提供了记录所有评估或测试结果的系统。下面的SQL语句将显示之前加载到系统中的计算结果：

SELECT *
FROM evaluations

示例数据如下：

Student Subject Evaluation_result Evaluation_day
Smith, John Music 7.0 2016-03-01
Smith, John Maths 4.0 2016-03-01
Smith, John History 9.0 2016-03-22
Smith, John Language 7.0 2016-03-15
Smith, John Geography 9.0 2016-03-04
Gabriel, Peter Music 2.0 2016-03-01
Gabriel, Peter Maths 10.0 2016-03-01
Gabriel, Peter History 7.0 2016-03-22
Gabriel, Peter Language 4.0 2016-03-15
Gabriel, Peter Geography 10.0 2016-03-04
  • 期望结果

下面的表格可以很容易地跟踪学生的进度。在计算机科学中，我们称这种网格为透视表。如果分析数据透视表，你会发现我们使用原始数据中的值作为列标题或字段名（在本例中是地理、历史、数学等）。

希望的数据格式如下：

Student Geography History Language Maths Music
Gabriel, Peter 10.0 7.0 4.0 10.0 2.0
Smith, John 9.0 9.0 7.0 4.0 7.0

启用tablefunc扩展

正如我们前面提到的，crosstab 函数是PostgreSQL扩展tablefunc的一部分。要调用crosstab 函数，必须首先通过执行以下SQL命令启用tablefunction扩展：

CREATE extension tablefunc;

crosstab 函数

crosstab 函数接收SQL SELECT命令作为参数，该参数必须符合以下限制：

  • SELECT必须返回3列。
  • SELECT中的第一列将是数据透视表或最终结果中每一行的标识符。在我们的例子中，这是学生的名字。注意学生的名字（John Smith和Peter Gabriel）是如何出现在第一列中的。
  • SELECT中的第二列表示透视表中的类别。在我们的例子中，这些类别是学校的科目。需要注意的是，该列的值将扩展到数据透视表中的许多列中。如果第二列返回5个不同的值（地理、历史等），则数据透视表将有5列。
  • SELECT中的第三列表示分配给数据透视表的每个单元格的值。这些是我们示例中的求值结果。

如果我们把数据透视表看作一个二维数组，那么第一个SELECT列是数组的第一个维度，第二个SELECT列是第二个维度，第三个是数组元素的值。比如grid [first_column_value， second_column_value] = third_column_value。

SELECT student, subject, evaluation_result FROM evaluations ORDER BY 1,2

crosstab 函数在SELECT语句的FROM子句中调用。我们必须定义将进入最终结果的列和数据类型的名称。就我们的目的而言，最终结果定义为：

AS final_result(Student TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC)

整合上面的内容，完整的语句：

SELECT *
FROM crosstab( 'select student, subject, evaluation_result from evaluations order by 1,2')
     AS final_result(Student TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);

查询结果如下：

Student Geography History Language Maths Music
Gabriel, Peter 10.0 7.0 4.0 10.0 2.0
Smith, John 9.0 9.0 7.0 4.0 7.0

透视表实战示例

从单个数据集，我们可以生成许多不同的数据透视表。让我们继续以教师和班级为例，看看我们的一些选项。

查询学生每月成绩

作为老师，我们可能还需要一份学生今年迄今为止的评估结果报告。例如，假设我们想要获得约翰·史密斯从3月到7月的平均评价。在如下的网格中，表格看起来是这样的：

month text geography numeric history numeric language numeric maths numeric music numeric
3 9.00 9.00 7.00 4.00 7.00
4 4.00 7.50 7.00 4.00 5.66
5 8.00 6.00 7.00 7.00 7.00
6 7.50 7.00 7.00 7.00 8.00
7 6.66 9.00 7.75 10.00 6.00

实现透视表SQL:

SELECT *
FROM crosstab( 'select extract(month from period)::text, subject.name,
             trunc(avg(evaluation_result),2)
     from evaluation, subject 
     where evaluation.subject_id = subject.subject_id and student_id = 1
     group by 1,2 order by 1,2')  AS final_result(Month TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);

处理不完整记录

我们也可以称这一节为“交叉表的限制以及如何解决它”。在讨论这个问题之前，让我们先来设定一下场景：

假设你想看看是否有些学生在某些科目上没有考试分数。也许你可以尝试前面的查询，为July添加一个WHERE子句。代码看起来像这样：

SELECT *
FROM crosstab( 'select student, subject, evaluation_result from evaluations where extract (month from evaluation_day) = 7 order by 1,2')
     AS final_result(Student TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);

下面的数据透视表是该查询的结果。我们很快就可以看到，我们没有给彼得的语言、数学和音乐评分。

Student Geography History Language Maths Music
Gabriel, Peter 10.0 6.0 7.00
Smith, John 6.0 8.0 6.0 9.0 4.0

但是，如果我们尝试常规查询以获得Peter在7月份的成绩……

SELECT * from evaluations
where extract ( month from evaluation_day)=7 and student like 'Gabriel, Peter'

我们获得了不同的结果：

student subject evaluation_result evaluation_day
Gabriel, Peter Language 6.0 2016-07-15
Gabriel, Peter Geography 10.0 2016-07-04

当然，第二个查询是正确的，因为它显示的是原始数据。问题是数据透视表构建过程中,有些种类的信息缺失。为了解决这个问题，我们可以使用带有第二个参数的交叉表函数，该参数表示完整的类别列表。如果存在缺失值，数据透视表仍将正确构建。

第二个参数内容：‘select name from subject order by 1’ ，完整语句如下：

SELECT *
FROM crosstab( 'select student, subject, evaluation_result from evaluations
                where extract (month from evaluation_day) = 7 order by 1,2',
                'select name from subject order by 1')
     AS final_result(Student TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);

现在输出结果包括缺失科目，并使用–表示：

Student Geography History Language Maths Music
Gabriel, Peter 10.0 – 6.0 – –
Smith, John 6.0 8.0 6.0 9.0 4.0

练习数据

数据透视表为我们提供了一种不同的方式来查看数据。此外，我们可以使用交叉表函数基于相同的原始数据创建不同的数据透视表。尝试构建一个数据透视表，根据下表中的原始数据显示每个城市和月份的最高温度。

CREATE TABLE weather (city text, when timestamp, temperature float);
City When Temperature
Miami 2016-01-01 08:00:00 68.6
Miami 2016-01-21 08:00:00 73.3
Orlando 2016-01-01 08:00:00 72.5
Miami 2016-02-01 18:00:00 58.6
Orlando 2016-02-02 18:00:00 62.5
Miami 2016-03-03 08:00:00 55.6
Orlando 2016-03-03 08:00:00 56.7
Miami 2016-04-04 18:00:00 50.6
Orlando 2016-04-04 18:00:00 61.5

数据透视表应为每个城市有一行，每个月有一列。如果你愿意，可以考虑使用相同的数据制作其他数据透视表。卷起袖子，试试吧。

相关文章