五餅二魚工作室

SQL Server 的 Window Function 是 SQL Server 2012 在 T-SQL 上非常大的一個強項，讓我們以往很多要使用 CTE 或者是 SubQuery 的處理，一下子變得非常容易。而一般來說我們會把 Window Function 分為三類 :

1. 匯總函數 ( Aggregate Functions ) : 就是大家以往會搭配 Group By 所使用的一些函數 ( 網址 ) ，像是比較常被使用的 Max , Min , Sum , Count , Avg 這一類函數。
2. 次序函數 ( Ranking Functions ) : 這個部分從 SQL Server 2005 就開始有支援 ( 網址 )，目前有四個函數 ROW_NUMBER、RANK、DENSE_RANK、NTILE
3. 分析函數 ( Analytic Functions ) : 這個部份都是 SQL Server 2012 開始新增的 ( 網址 ) ，這個部分如果要細分，可以分成大家比較常看到的 Offset 類型的四個函數 FIRST_VALUE, LAST_VALUE, LAG 和 LEAD ，以及 Distribution 類型的 CUME_DIST、PERCENTILE_CONT、PERCENTILE_DISC 和 PERCENT_RANK 四個函數

---

在使用 Window Function 的時候，基本上他的用法是 Window Function 搭配 OVER() 去使用

下面是一個範例的語法

SELECT BusinessEntityID, TerritoryID 
    ,CONVERT(varchar(20),SalesYTD,1) AS  SalesYTD
    ,DATEPART(yy,ModifiedDate) AS SalesYear
    ,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID 
                                             ORDER BY DATEPART(yy,ModifiedDate) 
                                             ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5;

 

如果想了解這些，可以參考 MSDN 或網路上的相關文章 ( 網址 1 , 2 , 3 , 4 )。而在介紹 OVER() 裡面的使用時，我們都可以看到有類似這樣的說明，「可以用 ROWS 和 RANGE 去指定一個特定的 Frame，文件上也有針對這個部分去做個說明，目前 SQL Server 2012 對於 ROWS 有完整支援，但 RANGE 只有支援 CURRENT ROW、UNBOUNDED PRECEDING 和 UNBOUNDED FOLLOWING」。

而在某次社群活動中，當介紹到這個部分的時候，有朋友提到一個之前都沒有想到的問題，「既然 ROWS 都有完整支援了，那 T-SQL 為什麼不完整支援 RANGE 呢 ? 還是 RANGE 有甚麼特別運用時機呢 ?」這個問題當下的確是難倒我了，以往真的沒有去想到這個部分，因此也就開始上網找些資料來做測試了。首先在 SQL:2011 上面會有找到一些說明，有關 Window Function 在 ANSI SQL 上的介紹，但看起來似乎沒有甚麼不同；也有找到 Sybase 文件，但也沒有甚麼特別的差異，而在 Oracle 上面，似乎就有比較明確的說明和差異了。首先看到在 PL/SQL 在 Windowing 的描述，看起來 ROWS 和 RANGE 都有完整的支援了。

只是文件中似乎沒有甚麼比較明確的說明這兩者的差異，只有這樣一段的描述

• ROWS specifies the window in physical units (rows).

• RANGE specifies the window as a logical offset.

但是單從字面上還是不是那麼容易感覺得出來這兩者的差異，而在查詢過程中，剛好看到一篇 Oracle 介紹 Analytic Function 的文章 「A Window into the World of Analytic Functions」，文章中有個範例，在這個範例就比較能比較出這兩者的差異了。當你使用 ROWS 的時候，是實際指出資料 Frame 的筆數，如同前面的範例，我們要累加自己和後面一筆資料的時候，這個時候就適合用 ROWS ；但如果像如下的範例，要加總雇每個人用雇用日期和前九十天內的薪資加總，他的範圍是由資料的值來判斷，而不一定是要加總幾筆資料，那這個時候就適合使用 RANGE。

select last_name, first_name, department_id, hire_date, salary,
   SUM (salary)
  OVER (PARTITION BY department_id ORDER BY hire_date
        RANGE 90 PRECEDING) department_total
 from employee
 order by department_id, hire_date;

因此看起來的確有不少狀況是需要使用 RANGE 來解決的，只是目前 SQL Server 2014 & 2016 都沒有聽到有這個部分的加強，因此可能目前還是都只能使用 ROWS 的方式來搭配 Window Function。