每天10分鐘學(xué)習(xí)T-SQL語言基礎(chǔ)（Part 3）

【數(shù)據(jù)庫技術(shù)】|?作者 / Edison Zhou

《Microsoft SQL Server 2008技術(shù)內(nèi)幕：T-SQL語言基礎(chǔ)》是一本關(guān)于T-SQL方面的好書，可能現(xiàn)在我們在京東上都買不到了，我也是在2014年在淘寶上淘的偎谁⊙餐ǎ看完之后仑氛，我總結(jié)了一些精華筆記瓷产，現(xiàn)將其分成一個系列的筆記文章分享與你，每篇預(yù)計閱讀時間為10分鐘左右掖举。上一篇介紹了SQL Server的表表達(dá)式及集合運算，本篇會介紹透視娜庇、逆透視塔次、分組方篮。

透視

所謂透視（Pivoting）就是把數(shù)據(jù)從行的狀態(tài)旋轉(zhuǎn)為列的狀態(tài)的處理。其處理步驟為：

相信很多人在筆試或面試的時候被問到如何通過SQL實現(xiàn)行轉(zhuǎn)列或列轉(zhuǎn)行的問題励负，可能很多人當(dāng)時懵逼了藕溅，沒關(guān)系，下面我們通過例子來理解继榆。

　〗肀怼（1）準(zhǔn)備數(shù)據(jù)

--1.0 準(zhǔn)備數(shù)據(jù)

USE tempdb;

IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL

DROP TABLE dbo.Orders;

CREATE TABLE dbo.Orders

(

orderid? INT? ? ? ? NOT NULL,

orderdate DATE? ? ? NOT NULL, -- prior to SQL Server 2008 use DATETIME

empid? ? INT? ? ? ? NOT NULL,

custid? ? VARCHAR(5) NOT NULL,

qty? ? ? INT? ? ? ? NOT NULL,

CONSTRAINT PK_Orders PRIMARY KEY(orderid)

);

INSERT INTO dbo.Orders

(orderid, orderdate, empid, custid, qty)

VALUES

(30001, '20070802', 3, 'A', 10),

(10001, '20071224', 2, 'A', 12),

(10005, '20071224', 1, 'B', 20),

(40001, '20080109', 2, 'A', 40),

(10006, '20080118', 1, 'C', 14),

(20001, '20080212', 2, 'B', 12),

(40005, '20090212', 3, 'A', 10),

(20002, '20090216', 1, 'C', 20),

(30003, '20090418', 2, 'B', 15),

(30004, '20070418', 3, 'C', 22),

(30007, '20090907', 3, 'D', 30);

SELECT * FROM dbo.Orders;

　　這里使用了MS SQL2008的VALUES子句格式語法，這時2008版本的新特性略吨。如果你使用的是2005及以下版本集币，你需要多個INSERT語句。最后的執(zhí)行結(jié)果如下圖所示：

　〈渲摇（2）需求說明

　　假設(shè)我們要生成一個報表鞠苟，包含每個員工和客戶組合之間的總訂貨量。用以下簡單的分組查詢可以解決這個問題：

select

empid,custid,SUM(qty) as sumqty

from dbo.Ordersgroup by empid,custid;

　　該查詢的執(zhí)行結(jié)果如下：

　　不過秽之，假設(shè)現(xiàn)在要求要按下表所示的的格式來生成輸出結(jié)果：

　　這時偶妖，我們就需要進行透視轉(zhuǎn)換了！

　≌！（3）使用標(biāo)準(zhǔn)SQL進行透視轉(zhuǎn)換

　　Step1.分組：GROUP BY empid;

　　Step2.擴展：CASE WHEN custid='A' THEN qty END;

　　Step3.聚合：SUM(CASE WHEN custid='A' THEN qty END);

--1.1標(biāo)準(zhǔn)SQL透視轉(zhuǎn)換

select empid,

SUM(case when custid='A' then qty end) as A,

SUM(case when custid='B' then qty end) as B,

SUM(case when custid='C' then qty end) as C,

SUM(case when custid='D' then qty end) as D

from dbo.Orders

group by empid;

　　執(zhí)行結(jié)果如下圖所示：

　≈悍谩（4）使用T-SQL PIVOT運算符進行透視轉(zhuǎn)換

　　自SQL Server 2005開始引入了一個T-SQL獨有的表運算符-PIVOT，它可以對某個源表或表表達(dá)式進行操作董虱、透視數(shù)據(jù)扼鞋，再返回一個結(jié)果表。

　　PIVOT運算符同樣涉及前面介紹的三個邏輯處理階段（分組愤诱、擴展和聚合）以及同樣的透視轉(zhuǎn)換元素云头，但使用的是不同的、SQL Server原生的語法淫半。

　　下面是使用PIVOT運算符實現(xiàn)上面一樣的效果：

select

empid,A,B,C,D

from

(

select

empid,custid,qty

from dbo.Orders) as D

pivot (sum(qty) for custid in (A,B,C,D)

) as P;

　　其中溃槐，PIVOT運算符的圓括號內(nèi)要指定聚合函數(shù)（本例中SUM）、聚合元素（本例中的qty）科吭、擴展元素（custid）以及目標(biāo)列名稱的列表（本例中的A昏滴、B、C对人、D）谣殊。在PIVOT運算符的圓括號后面，可以為結(jié)果表制定一個別名牺弄。

Tip：使用PIVOT運算符一般不直接把它應(yīng)用到源表（本例中的Orders表）姻几，而是將其應(yīng)用到一個表表達(dá)式（該表表達(dá)式只包含透視轉(zhuǎn)換需要的3種元素，不包含其他屬性。）此外蛇捌，不需要為它顯式地指定分組元素抚恒，也就不需要再查詢中使用GROUP BY子句。

逆透視

所謂逆透視（Unpivoting）轉(zhuǎn)換是一種把數(shù)據(jù)從列的狀態(tài)旋轉(zhuǎn)為行的狀態(tài)的技術(shù)络拌，它將來自單個記錄中多個列的值擴展為單個列中具有相同值得多個記錄柑爸。換句話說，將透視表中的每個源行潛在地轉(zhuǎn)換成多個行盒音，每行代表源透視表的一個指定的列值表鳍。

還是通過一個栗子來理解：

　　（1）首先還是準(zhǔn)備一下數(shù)據(jù)：

USE tempdb;

IF OBJECT_ID('dbo.EmpCustOrders', 'U') IS NOT NULL

DROP TABLE dbo.EmpCustOrders;

SELECT

empid, A, B, C, DINTO dbo.EmpCustOrders

FROM

(

SELECT

empid, custid, qty

FROM dbo.Orders) AS D

PIVOT(SUM(qty) FOR custid IN(A, B, C, D)

) AS P;

SELECT * FROM dbo.EmpCustOrders;

　　下面是對這個表EmpCustOrders的查詢結(jié)果：

　∠榉獭（2）需求說明

　　要求執(zhí)行你透視轉(zhuǎn)換譬圣，為每個員工和客戶組合返回一行記錄，其中包含這一組合的訂貨量雄坪。期望的輸出結(jié)果如下圖所示：

　±迨臁（3）標(biāo)準(zhǔn)SQL進行逆透視轉(zhuǎn)換

　　Step1.生成副本：CROSS JOIN 交叉聯(lián)接生成多個副本

　　Step2.提取元素：通過CASE語句生成qty數(shù)據(jù)列

　　Step3.刪除不相關(guān)的交叉：過濾掉NULL值

select

from

(

select

empid, custid,

case custid

when 'A' then A

when 'B' then B

when 'C' then C

when 'D' then D

end as qty

from dbo.EmpCustOrders

cross join ( VALUES('A'),('B'),('C'),('D') ) as Custs(custid)

) as D

where qty is not null;

　　執(zhí)行結(jié)果如下圖所示：

　　（4）T-SQL UNPIVOT運算符進行逆透視轉(zhuǎn)換

　　和PIVOT類似维哈，在SQL Server 2005引入了一個UNPIVOT運算符绳姨，它的作用剛好和PIVOT運算符相反，即我們可以拿來做逆透視轉(zhuǎn)換工作阔挠。UNPIVOT同樣會經(jīng)歷我們上面提到的三個階段飘庄。繼續(xù)上面的栗子，我們使用UNPIVOT來進行逆透視轉(zhuǎn)換：

select

empid, custid, qty

from dbo.EmpCustOrders

unpivot (qty for custid in (A,B,C,D)) as U;

　　其中购撼，UNPIVOT運算符后邊的括號內(nèi)包括：用于保存源表列值的目標(biāo)列明（這里是qty）跪削，用于保存源表列名的目標(biāo)列名（這里是custid），以及源表列名列表（A迂求、B碾盐、C、D）揩局。同樣毫玖，在UNPIVOT括號后面也可以跟一個別名。

Tip：對經(jīng)過透視轉(zhuǎn)換所得的表再進行逆透視轉(zhuǎn)換凌盯，并不能得到原來的表付枫。因為你透視轉(zhuǎn)換只是把經(jīng)過透視轉(zhuǎn)換的值再旋轉(zhuǎn)島另一種新的格式。

分組

首先了解一下分組集：分組集就是分組（GROUP BY子句）使用的一組屬性（或列名）十气。在傳統(tǒng)SQL中励背，一個聚合查詢只能定義一個分組集。為了靈活而有效地處理分組集砸西，SQL Server 2008引入了幾個重要的新功能（他們都是GROUP BY的從屬子句，需要依賴于GROUP BY子句）：

　　（1）GROUPING SETS從屬子句

　　使用該子句芹枷，可以方便地在同一個查詢中定義多個分組集衅疙。例如下面，我們定義了4個分組集：(empid,custid),(empid),(custid)和()：

--3.1 GROUPING SETS從屬子句

select

empid,custid,SUM(qty) as sumqty

from dbo.Orders

group by

GROUPING SETS? (? ? (empid,custid),? ? (empid),? ? (custid),? ? ()? );

　　這個查詢相當(dāng)于執(zhí)行了四個group by查詢的并集鸳慈。

　”ヒ纭（2）CUBE從屬子句

　　CUBE子句為定義多個分組集提供了一種更簡略的方法，可以把CUBE子句看作是用于生成分組的冪集走芋。例如：CUBE(a,b,c)等價于GROUPING SETS[(a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),()]绩郎。下面我們用CUBE來實現(xiàn)上面的例子：

--3.2 CUEE從屬子句

select

empid,custid,SUM(qty) as sumqty

from dbo.Orders

group by cube(empid,custid);

　　（3）ROLLUP從屬子句

　　ROLLUP子句也是一種簡略的方法翁逞，只不過它與CUBE不同肋杖，它強調(diào)輸入成員之間存在一定的層次關(guān)系，從而生成讓這種層次關(guān)系有意義的所有分組集挖函。例如：CUBE(a,b,c)會生成8個可能的分組集状植，而ROLLUP則認(rèn)為3個輸入成員存在a>b>c的層次關(guān)系，所以只會生成4個分組集：(a,b,c),(a,b),(a),()怨喘。

　　下面我們假設(shè)想要按時間層次關(guān)系：訂單年份>訂單月份>訂單日津畸，以這樣的關(guān)系來定義所有分組集，并未每個分組集返回其總訂貨量必怜∪馔兀可能我們用GROUPING SETS需要4行，然后使用ROLLUP卻只需要一行：group by rollup(YEAR(orderdate),MONTH(orderdate),DAY(orderdate));

　　完整SQL查詢?nèi)缦拢?/p>

--3.3 ROLLUP從屬子句

select

YEAR(orderdate) as orderyear,

MONTH(orderdate) as ordermonth,

DAY(orderdate) as orderday,

SUM(qty) as sumqty

from dbo.Orders

group by rollup(YEAR(orderdate),

MONTH(orderdate),DAY(orderdate));

　　執(zhí)行結(jié)果如下圖所示：

　∈崆臁（4）GROUPING_ID函數(shù)

　　如果一個查詢定義了多個分組集帝簇，還想把結(jié)果行和分組集關(guān)聯(lián)起來，也就是說靠益，為每個結(jié)果行標(biāo)注它是和哪個分組集關(guān)聯(lián)的丧肴。SQL Server 2008中引入了一個GROUPING_ID函數(shù)，簡化了關(guān)聯(lián)結(jié)果行和分組集的處理胧后，可以容易地計算出每一行和哪個分組集相關(guān)聯(lián)芋浮。

　　例如，繼續(xù)上面的例子壳快，我們想要將empid,custid作為輸入：

select

grouping_id(empid,custid) as groupingset,

empid, custid, SUM(qty) as sumqty

from dbo.Ordersgroup by cube(empid,custid);

　　執(zhí)行結(jié)果中會出現(xiàn)groupingset為0,1,2,3纸巷，分別代表了empid,custid的4個可能的分組集（(empid,custid),(empid),(custid),()）。

小結(jié)

本文介紹了MS SQL Server 2008的透視眶痰、逆透視及分組瘤旨，下一篇會介紹數(shù)據(jù)修改。

參考資料

[美] Itzik Ben-Gan 著竖伯，成保棟譯存哲，《Microsoft SQL Server 2008技術(shù)內(nèi)幕：T-SQL語言基礎(chǔ)》

考慮到很多人買了這本書因宇，卻下載不了這本書的配套源代碼和示例數(shù)據(jù)庫，特意上傳到了百度云盤中祟偷，下載鏈接：https://pan.baidu.com/s/1jIryBUA

強烈建議大家閱讀完每一章節(jié)后察滑，練習(xí)一下課后習(xí)題，相信或多或少都會有一些收獲修肠。

The End

舉報

0?條評論