需求說明

解釋此函數(shù)如何工作的最簡單方法是使用帶有數(shù)據(jù)透視表的示例。首先，我們將從實際角度解釋我們最初的觀點，然后定義所需的數(shù)據(jù)透視表。

假設(shè)我們是老師，需要統(tǒng)計你教所有科目的成績（語言、音樂等），學(xué)校為你提供了記錄所有評估或測試結(jié)果的系統(tǒng)。下面的SQL語句將顯示之前加載到系統(tǒng)中的計算結(jié)果：

SELECT *
FROM evaluations

示例數(shù)據(jù)如下：

• 期望結(jié)果

下面的表格可以很容易地跟蹤學(xué)生的進度。在計算機科學(xué)中，我們稱這種網(wǎng)格為透視表。如果分析數(shù)據(jù)透視表，你會發(fā)現(xiàn)我們使用原始數(shù)據(jù)中的值作為列標(biāo)題或字段名（在本例中是地理、歷史、數(shù)學(xué)等）。

希望的數(shù)據(jù)格式如下：

啟用tablefunc擴展

正如我們前面提到的，crosstab 函數(shù)是PostgreSQL擴展tablefunc的一部分。要調(diào)用crosstab 函數(shù)，必須首先通過執(zhí)行以下SQL命令啟用tablefunction擴展：

CREATE extension tablefunc;

crosstab 函數(shù)

crosstab 函數(shù)接收SQL SELECT命令作為參數(shù)，該參數(shù)必須符合以下限制：

• SELECT必須返回3列。
• SELECT中的第一列將是數(shù)據(jù)透視表或最終結(jié)果中每一行的標(biāo)識符。在我們的例子中，這是學(xué)生的名字。注意學(xué)生的名字（John Smith和Peter Gabriel）是如何出現(xiàn)在第一列中的。
• SELECT中的第二列表示透視表中的類別。在我們的例子中，這些類別是學(xué)校的科目。需要注意的是，該列的值將擴展到數(shù)據(jù)透視表中的許多列中。如果第二列返回5個不同的值（地理、歷史等），則數(shù)據(jù)透視表將有5列。
• SELECT中的第三列表示分配給數(shù)據(jù)透視表的每個單元格的值。這些是我們示例中的求值結(jié)果。

如果我們把數(shù)據(jù)透視表看作一個二維數(shù)組，那么第一個SELECT列是數(shù)組的第一個維度，第二個SELECT列是第二個維度，第三個是數(shù)組元素的值。比如grid [first_column_value， second_column_value] = third_column_value。

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

crosstab 函數(shù)在SELECT語句的FROM子句中調(diào)用。我們必須定義將進入最終結(jié)果的列和數(shù)據(jù)類型的名稱。就我們的目的而言，最終結(jié)果定義為：

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

整合上面的內(nèi)容，完整的語句：

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);

查詢結(jié)果如下：

透視表實戰(zhàn)示例

從單個數(shù)據(jù)集，我們可以生成許多不同的數(shù)據(jù)透視表。讓我們繼續(xù)以教師和班級為例，看看我們的一些選項。

查詢學(xué)生每月成績

作為老師，我們可能還需要一份學(xué)生今年迄今為止的評估結(jié)果報告。例如，假設(shè)我們想要獲得約翰·史密斯從3月到7月的平均評價。在如下的網(wǎng)格中，表格看起來是這樣的：

實現(xiàn)透視表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);

處理不完整記錄

我們也可以稱這一節(jié)為“交叉表的限制以及如何解決它”。在討論這個問題之前，讓我們先來設(shè)定一下場景：

假設(shè)你想看看是否有些學(xué)生在某些科目上沒有考試分數(shù)。也許你可以嘗試前面的查詢，為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);

下面的數(shù)據(jù)透視表是該查詢的結(jié)果。我們很快就可以看到，我們沒有給彼得的語言、數(shù)學(xué)和音樂評分。

但是，如果我們嘗試常規(guī)查詢以獲得Peter在7月份的成績……

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

當(dāng)然，第二個查詢是正確的，因為它顯示的是原始數(shù)據(jù)。問題是數(shù)據(jù)透視表構(gòu)建過程中,有些種類的信息缺失。為了解決這個問題，我們可以使用帶有第二個參數(shù)的交叉表函數(shù)，該參數(shù)表示完整的類別列表。如果存在缺失值，數(shù)據(jù)透視表仍將正確構(gòu)建。

第二個參數(shù)內(nèi)容：‘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);

現(xiàn)在輸出結(jié)果包括缺失科目，并使用–表示：

練習(xí)數(shù)據(jù)

數(shù)據(jù)透視表為我們提供了一種不同的方式來查看數(shù)據(jù)。此外，我們可以使用交叉表函數(shù)基于相同的原始數(shù)據(jù)創(chuàng)建不同的數(shù)據(jù)透視表。嘗試構(gòu)建一個數(shù)據(jù)透視表，根據(jù)下表中的原始數(shù)據(jù)顯示每個城市和月份的最高溫度。

CREATE TABLE weather (city text, when timestamp, temperature float);

數(shù)據(jù)透視表應(yīng)為每個城市有一行，每個月有一列。如果你愿意，可以考慮使用相同的數(shù)據(jù)制作其他數(shù)據(jù)透視表。卷起袖子，試試吧。

到此這篇關(guān)于PostgreSQL實現(xiàn)透視表查詢的方法詳解的文章就介紹到這了,更多相關(guān)PostgreSQL透視表查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論