iT邦幫忙

1

[筆記][MSSQL]關於樞紐分析查詢的使用

之前在實務上有做到一個功能,大略是要統計每個人在一年當中每個月的請假天數,本來有想過用12個子查詢分別去SELECT出每個月的請假天數,但是那時候的我怎麼串都串不出想要的資料來,就去翻書看到了一個用樞紐分析查詢作出來的統計表,今天來作一下筆記。

以下使用北風資料庫的[Orders]和[Order Details]做測試:

首先我們需要得出在今年份(以1997年為例)每個月各產品的銷售額


SELECT YEAR(M.OrderDate) o_year,MONTH(M.OrderDate) o_month,
P.ProductName,SUM(D.Quantity*D.UnitPrice) total
FROM [Orders] M 
LEFT JOIN [Order Details] D ON M.OrderID=D.OrderID
LEFT JOIN [Products] P ON D.ProductID=P.ProductID
WHERE YEAR(M.OrderDate)='1997'
GROUP BY YEAR(M.OrderDate),MONTH(M.OrderDate),P.ProductName

結果會如下(紅框處等等會解釋,可以先忽略):
https://ithelp.ithome.com.tw/upload/images/20171014/20106935fCdeTZRDPw.jpg

而樞紐分析查詢要做的事情就是要將這張表依照條件,將現在為縱軸的月份轉為橫軸,而產品名稱依舊維持在縱軸,並將該月份的銷售額填進該月份(橫軸)與產品名稱(縱軸)的對應欄位中。

這樣說可能有點抽象,直接從SQL語法和結果來看會比較清楚:

--這邊是定義樞紐分析報表的欄位名稱,必須是要轉為樞紐分析表的資料表擁有的欄位,也就是欄位名稱必須和以下FROM的來源資料表內的欄位名稱相同(取別名和其他函式的用法都相同,以下是把月份以簡寫取別名,AS可省略)。
--還有要注意的是第一行的ProductName他不算是樞紐分析的資料行,可以把它當成是縱軸的欄位,而之後的月份為橫軸的欄位。

SELECT ProductName,
ISNULL([1],0) AS Jan,ISNULL([2],0) AS Feb,ISNULL([3],0) AS Mar,
ISNULL([4],0) AS Apr,ISNULL([5],0) AS May,ISNULL([6],0) AS Jun,
ISNULL([7],0) AS Jul,ISNULL([8],0) AS Aug,ISNULL([9],0) AS Sep,
ISNULL([10],0) AS Oct,ISNULL([11],0) AS Nov,ISNULL([12],0) AS Dec

--這邊的來源資料表是上一步查詢出來的當年度每個月個產品的銷售資料
FROM
(SELECT YEAR(M.OrderDate) o_year,MONTH(M.OrderDate) o_month,
P.ProductName,SUM(D.Quantity*D.UnitPrice) total
FROM [Orders] M 
LEFT JOIN [Order Details] D ON M.OrderID=D.OrderID
LEFT JOIN [Products] P ON D.ProductID=P.ProductID
WHERE YEAR(M.OrderDate)='1997'
GROUP BY YEAR(M.OrderDate),MONTH(M.OrderDate),P.ProductName) MyTable

--需要彙總的欄位,和以哪個欄位下去彙總
--在這邊我要算每個月的合計所以我就合計來源資料表內的total欄位,然後FOR月份內對應的資料下去彙總計算,所以這邊IN的內容就是第一張結果資料表的紅框處內的資料。

--這裡IN的資料會對應到上面樞紐分析報表的欄位名稱,所以在一開始SELECT的時候要先知道要FOR哪個資料作彙總,別名在另外取就好。

PIVOT
(
	SUM(Mytable.total)
	FOR MyTable.o_month
	IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS piv

ORDER BY ProductName

結果會如下:
https://ithelp.ithome.com.tw/upload/images/20171014/20106935a1oSR0nvWu.jpg

是不是變的超漂亮的!/images/emoticon/emoticon32.gif
而且SQL也不會下的很複雜,當初看到這個方法的時候一整個覺得賺到了,只是之後也沒有在用過了,如果有機會各位大大也可以試試看!

Function:
1.ISNULL(AAA,BBB)
這個函式會去判斷AAA如果是NULL的話會把他替代成BBB,BBB可以是'字串'也能是欄位。
2.YEAR(AAA)
不論AAA是什麼形態,函式會先將AAA轉為日期/時間格式,在取出年度。
3.MONTH(AAA)
不論AAA是什麼形態,函式會先將AAA轉為日期/時間格式,在取出月份。

題外話,這篇有些地方可能解釋的不太清楚,也不知道怎麼說會比較明瞭,
所以如果有疑問可以留言,我會盡力回答補充的!


2 則留言

0
shing_pascal
iT邦新手 5 級 ‧ 2017-10-16 09:04:04

平常在做產品資料統計分析時會用到!
程式很簡捷!
我寫過類似的SQL 查詢
是先分月查詢,最後再合併,
程式很常

對啊!有時候突然看到這些技術都會有種相見恨晚的感覺XD

1
尼克
iT邦高手 1 級 ‧ 2017-10-17 09:15:36

使用 PIVOT 和 UNPIVOT

-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, 
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost 
    FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;

沒想到還有另外一種反轉的方式+.+
謝謝大大分享做法!!
我會再去試試看的!!

尼克 iT邦高手 1 級 ‧ 2017-10-18 08:54:11 檢舉

你可以用關鍵字搜尋
row to column sql 還有一些其它方式。
PIVOT 是新版才提供的。針對升級至 SQL Server 2005 或更新版本的資料庫使用 PIVOT 與 UNPIVOT 時,資料庫的相容性層級必須設為 90 或更高

我還沒有去研究過資料庫相容性層級的問題.../images/emoticon/emoticon16.gif
可能我剛好是用SQLServer2012才沒有注意到這個,所以上面那關鍵字查出來的都是樞紐分析的一種哦0_0
之後要再來研究哪些用的到了/images/emoticon/emoticon13.gif

我要留言

立即登入留言