Master Windows Function trong SQL Server

Windows Function được đưa vào từ SQL Server 2005, đến SQL Server 2012 có những tăng cường, như có thêm các Analytic Windows Function, hỗ trợ Order by đối với các aggregate Windows Function. Tôi đã tìm thấy một bài viết giúp ta master các Windows Function này.

Trước hết ta tạo dữ liệu mẫu để thực hành

USE AdventureWorks2012;

GO

IF OBJECT_ID('RegionalSales', 'U') IS NOT NULL
DROP TABLE RegionalSales;

GO

CREATE TABLE RegionalSales
(
  SalesID INT NOT NULL IDENTITY PRIMARY KEY,
  SalesGroup NVARCHAR(30) NOT NULL,
  Country NVARCHAR(30) NOT NULL,
  AnnualSales INT NOT NULL
);

GO

INSERT INTO RegionalSales
  (SalesGroup, Country, AnnualSales)
VALUES
  ('North America', 'United States', 22000),
  ('North America', 'Canada', 32000),
  ('North America', 'Mexico', 28000),
  ('Europe', 'France', 19000),
  ('Europe', 'Germany', 22000),
  ('Europe', 'Italy', 18000),
  ('Europe', 'Greece', 16000),
  ('Europe', 'Spain', 16000),
  ('Europe', 'United Kingdom', 32000),
  ('Pacific', 'Australia', 18000),
  ('Pacific', 'China', 28000),
  ('Pacific', 'Singapore', 21000),
  ('Pacific', 'New Zealand', 18000),
  ('Pacific', 'Thailand', 17000),
  ('Pacific', 'Malaysia', 19000),
  ('Pacific', 'Japan', 22000);

GO

Trước tiên ta dùng đoạn script sau, lưu ý là mệnh đề Order By dưới đây chỉ được hỗ trợ từ SQL Server 2012, từ SQL Server 2008R2 trở về trước, ta không được phép dùng Order By như đoạn script dưới đây.

SELECT
  SalesGroup,
  Country,
  AnnualSales,
  COUNT(AnnualSales) OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC) AS CountryCount,
  SUM(AnnualSales) OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC) AS TotalSales,
  AVG(AnnualSales) OVER(PARTITION BY SalesGroup
    ORDER BY AnnualSales DESC) AS AverageSales
FROM
  RegionalSales;

Đây là kết quả

SalesGroup	Country	AnnualSales	CountryCount	TotalSales	AverageSales
Europe	United Kingdom	32000	1	32000	32000
Europe	Germany	22000	2	54000	27000
Europe	France	19000	3	73000	24333
Europe	Italy	18000	4	91000	22750
Europe	Greece	16000	6	123000	20500
Europe	Spain	16000	6	123000	20500
North America	Canada	32000	1	32000	32000
North America	Mexico	28000	2	60000	30000
North America	United States	22000	3	82000	27333
Pacific	China	28000	1	28000	28000
Pacific	Japan	22000	2	50000	25000
Pacific	Singapore	21000	3	71000	23666
Pacific	Malaysia	19000	4	90000	22500
Pacific	Australia	18000	6	126000	21000
Pacific	New Zealand	18000	6	126000	21000
Pacific	Thailand	17000	7	143000	20428

Nhìn cột Greece và Spain tô màu đỏ ở trên, ta thấy nó có giá trị Annual Sales cùng là 16000, và giá trị tương tứng của nó ở cột CountryCount là 6, đây là một behavior khi dùng windows function đối với duplicate value. Nhìn xuống phía dưới ta thấy Country là Australia và New Zealand cũng tương tự.

Tiếp theo, nhìn vào bảng kết quả ở trên, cột TotalSales, ta thấy giá trị của từng dòng là một giá trị tích lũy của dòng hiện tại cộng với các dòng trước đó. Ví dụ:

Giá trị của cột Germany = United Kingdom + Germany = 32000 + 22000 = 54000
France = United Kingdom + Germany + France = 54000 + 19000 = 73000

Tương tự, cột AverageSales cũng được tính toán tương tự.

Windows Function trong SQL Server 2012 hỗ trợ thêm keywork Rows và And Range. Xem ví dụ dưới đây:

SELECT SalesGroup,
       Country,
       AnnualSales,
  COUNT(AnnualSales) OVER(PARTITION BY SalesGroup
                          ORDER BY AnnualSales DESC
                          ROWS 2 PRECEDING) AS CountryCount,

  SUM(AnnualSales) OVER(PARTITION BY SalesGroup
                        ORDER BY AnnualSales DESC
                        ROWS 2 PRECEDING) AS TotalSales,

  AVG(AnnualSales) OVER(PARTITION BY SalesGroup
                        ORDER BY AnnualSales DESC
                        ROWS 2 PRECEDING) AS AverageSales
FROM RegionalSales;

Sau đây là kết quả, mỗi dòng dưới đây là kết quả của nó cộng với 2 dòng trước đó. Chịu khó đối chiếu với dữ liệu ban đầu và suy ngẫm một chút, bạn sẽ hiểu tại sao nó ra kết quả dưới đây

SalesGroup	Country	AnnualSales	CountryCount	TotalSales	AverageSales
Europe	United Kingdom	32000	1	32000	32000
Europe	Germany	22000	2	54000	27000
Europe	France	19000	3	73000	24333
Europe	Italy	18000	3	59000	19666
Europe	Greece	16000	3	53000	17666
Europe	Spain	16000	3	50000	16666
North America	Canada	32000	1	32000	32000
North America	Mexico	28000	2	60000	30000
North America	United States	22000	3	82000	27333
Pacific	China	28000	1	28000	28000
Pacific	Japan	22000	2	50000	25000
Pacific	Singapore	21000	3	71000	23666
Pacific	Malaysia	19000	3	62000	20666
Pacific	Australia	18000	3	58000	19333
Pacific	New Zealand	18000	3	55000	18333
Pacific	Thailand	17000	3	53000	17666

Xem link dưới đây cho những ví dụ khác.
https://www.simple-talk.com/sql/t-sql-programming/sql-server-2012-window-function-basics/
http://sqlmag.com/blog/window-functions-over-clause-help-make-difference