遇到一個可以應用SQL 2005 PIVOT新功能的好例子,拿出來分享一下。

分析IIS Log時,我希望統計每一秒鐘不同執行結果(Status Code=200, 304, 401, 404, 500 ...)的次數,由IISLog匯入而成的Table可以找到LogTime與StatusCode兩個欄位。
要統計每秒不同StatusCode的數量不難,例如以下的T-SQL:

SELECT LogTime, StatusCode, Count(*) As Cnt 
FROM IISLogTable
GROUP BY LogTime, StatusCode
ORDER BY LogTime
查出的結果會像這樣:
LogTime  StatusCode Cnt
-------- ---------- -----------
06:40:05 200        5
06:40:05 302        1
06:40:06 304        1
06:40:06 200        10
06:40:07 200        5
06:40:07 500        3
06:40:08 200        11
06:40:08 404        1

但在實際應用時,我們會希望將每秒鐘的各StatusCode整合在一列中,像LogTime, StatusCode, Count200, Count302, Count304, Count401...的形式,以方便用Excel製表或繪圖,但這少不了要費一番手腳。

當使用的資料庫是SQL 2005時,我們多了一項新武器---PIVOT! 用如下的語法,就可以將以上的結果由列轉成欄,變成我們要的樣子。

SELECT * FROM 
(
    SELECT LogTime, StatusCode, COUNT(*) AS Cnt
    FROM IISLogTable
    GROUP BY LogTime, StatusCode
) AS X
PIVOT
(
    SUM(Cnt)
    FOR StatusCode IN 
    ([200],[302],[304],[401],[404],[500])
) AS PVT

查詢結果變成:

LogTime  200         302         304         401         404         500
-------- ----------- ----------- ----------- ----------- ----------- -----------
06:40:05 5           1           NULL        NULL        NULL        NULL
06:40:06 10 NULL 1 NULL NULL NULL 06:40:07 5 NULL NULL NULL NULL 3
06:40:08 11 NULL NULL NULL 1 NULL
怎樣,很省事很酷吧?

Comments

# by Robert Hu

如果要分析 IIS Log 的話可以試試微軟的 Logparser http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en 使用 Logparser + Excel 樞紐分析表可以很快的取得相同的資料,而且可以省下匯入 SQL 所需要的時間和硬碟空間

# by Jeffrey

Thanks to Robert, 看來又是把被埋沒的寶刀,使用方法有點小複雜,有空再來研究一下。

# by Eric

很不錯的新方法

# by 小魚

請問一下喔~~如果 IN裡面的值是未知的那要怎麼產生呀? IN ([200],[302],[304],[401],[404],[500]) <==這是我們已知有[200],[302],[304],[401],[404],[500]的情況,如果假設我們不知道時,那要怎麼做?謝謝. ^^

# by Jeffrey

to 小魚, 欄位名稱的部分似乎都得事先指定,我沒找到可以動態決定的例子,看來是語法天生的限制。 如果真要動態決定,使用動態組字串的方式組出SQL,再用sp_executesql執行,我猜應該是可行的。

# by 愚魚

請問 Jeffrey :   您指的 [使用動態組字串的方式組出SQL,再用sp_executesql執行] 可否再說明詳細點?謝謝!

# by Jeffrey

to 愚魚, MSDN上有個sp_executesql例子: http://msdn.microsoft.com/zh-tw/library/ms175170.aspx文中第三段範例,它先跑CURSOR列出所有DB,再對代入每個DB名稱組出指令執行DBCC CHECKDB。但要注意,這種組合指令的方式,要留意不能受使用者輸入的影響被插入惡意指令。 依我的想法,如果這些欄位名稱全由你的邏輯決定,沒有被植入惡意指令的可能,這段指令就用程式碼組裝也是無妨。

# by 愚魚

sorry 連結字串不全,找不到例子 msdn.microsoft.com/.../ms175170.aspx%E6%96%87%E4%B8%AD%E7%AC%AC%E4%B8%89%E6%AE%B5%E7%AF%84%E4%BE%8B 請問/.../中間是什麼?

# by Jeffrey

to 愚魚,見http://msdn.microsoft.com/zh-tw/library/ms175170.aspx 文中第三個範例

# by Emma

請問一下,我參考此方式試做皆出現「接近 'PIVOT' 的語法錯誤。您可能要將目前資料庫的相容性層級設成高一點的值,以啟用這項功能。請參閱預存程序 sp_dbcmptlevel 的說明。」的訊息。因為有去查過sp_dbcmptlevel,但還是不懂要去改哪邊?? 另因為有查一下,有人說如果是2000的資料庫就可能會出現上面的訊息,但我執行及資料庫所在位置皆為2005仍舊會出現這情況。

# by Jeffrey

to Emma, 掛在SQL 2005上的DB可以設定成相容於SQL 2000,此時就不能用SQL 2005才有的功能,你檢查看看是否是導致遇到該錯誤的原因。 我找到一個設定的畫面範例,你參考一下: http://blog.sqlauthority.com/2007/05/31/sql-server-2005-change-database-compatible-level-backward-compatibility-part-2-management-studio/

# by Emma

感謝解答 !! 查看後發現資料庫目前確實是設定相容於SQL 2000。

# by 小餅

請問這語法可以做到轉置嗎? 假設我的表格是如下: Status value_1 value_2 value_3 -------- ---------- ---------- ---------- 200 2 70 80 302 1 50 60 304 3 65 100 可以轉置成如下嗎? status | 200 302 304 value_1 | 2 1 3 value_2 | 70 50 65 value_3 | 80 60 100 要怎麼做?

# by Jeffrey

to 小餅,我想可以先將原表UNPIVOT成Status, Value_Name, Value格式的9筆資料,再用文章中說的做法PIVOT成你要的樣子。參考: http://technet.microsoft.com/zh-tw/library/ms177410(SQL.90).aspx

# by 無名

請教黑暗老師,若PIVOT完成後,表單右邊有辦法做加總嗎。有哪些方面可供參考呢,謝謝回復。^^

# by 無名

請問黑暗老師,若完成PIVOT之後的表單,最右側是否有可能加入總和的功能,謝謝回覆^^ 無名

# by 無名

請教黑暗老師,系統時間是否有落差 小弟打字時間是 20090818 09:15

# by Jeffrey

to 無名, 加總的工作我習慣在UI階段再產生,我猜透過適當的T-SQL SUM() GROUP BY應該可以產生想要的結果,但因為習慣在UI做掉,這部分研究有限。至於Blog時間不準的問題,因為主機放在國外,Community Server平台除了系統時區外又可以設定個別使用者時區,被搞到昏頭就自暴自棄地假裝沒看到,大家就不要太計較了,呵。

# by 小餅

to Jeffrey: 謝謝你,依照您的提示,我已經組出我的的樣子的語法了,謝謝。

# by annabel

您好~我是用另一種方式(selsct case...)把資料轉成橫向的!但是好像只有數字型態能變橫的,字串就會出現轉型失敗..一直不知道要怎麼解決><請問這種方法可以解決這種問題嗎?謝謝!!

# by annabel

您好~我是用另一種方式(selsct case...)把資料轉成橫向的!但是好像只有數字型態能變橫的,字串就會出現轉型失敗..一直不知道要怎麼解決><請問這種方法可以解決這種問題嗎?謝謝!!

# by pkupbon

版主您好: 目前有一個網站是採用Access資料庫,試了一下版主提供的語法,在MS-SQL成功試了出來。是否有可以套用在Access的PIVOT語法可以用呢?

Post a comment