Yaygın pencere işlevleri

Orta Düzey SQL Server

Ginger Grant

Instructor

FIRST_VALUE() ve LAST_VALUE()

FIRST_VALUE() penceredeki ilk değeri döndürür
LAST_VALUE() penceredeki son değeri döndürür

FIRST_VALUE

T-SQL'de FIRST_VALUE() ve LAST_VALUE()

FIRST_VALUE ve LAST_VALUE için ORDER BY zorunludur

-- Sütunları seçin
SELECT SalesPerson, SalesYear, CurrentQuota, 
    -- Her penceredeki ilk değer
       FIRST_VALUE(CurrentQuota) 
       OVER (PARTITION BY SalesYear ORDER BY ModifiedDate) AS StartQuota, 
    -- Her penceredeki son değer 
       LAST_VALUE(CurrentQuota) 
       OVER (PARTITION BY SalesYear ORDER BY ModifiedDate) AS EndQuota, 
       ModifiedDate as ModDate
FROM SaleGoal

Sonuçlar

+------------+----------+------------+----------+----------+----------+
|SalesPerson |SalesYear |CurrentQuota|StartQuota| EndQuota |ModDate   |
+------------+----------+------------+----------+----------+----------+
|Bob         |2011      |28000.00    |28000.00  |91000.00  |2011-04-16|
|Bob         |2011      |7000.00     |28000.00  |91000.00  |2011-07-17| 
|Bob         |2011      |91000.00    |28000.00  |91000.00  |2011-10-17|
|Bob         |2012      |140000.00   |140000.00 |107000.00 |2012-01-15|
|Bob         |2012      |70000.00    |140000.00 |107000.00 |2012-04-15|
|Bob         |2012      |154000.00   |140000.00 |107000.00 |2012-07-16|
|Bob         |2012      |107000.00   |140000.00 |107000.00 |2012-10-16|
...
+------------+----------+------------+----------+----------+----------+

LEAD() ile sonraki değeri alma

Sonraki satırdaki değeri sorgulamayı sağlar
LEAD() ile NextQuota sütunu oluşturulur
Satırları sıralamak için ORDER BY gerekir

T-SQL'de LEAD()

SELECT SalesPerson, SalesYear, CurrentQuota, 
-- Sonraki satırdan değerleri almak için pencere işlevi oluşturun
       LEAD(CurrentQuota) 
       OVER (PARTITION BY SalesYear ORDER BY ModifiedDate) AS NextQuota, 
       ModifiedDate AS ModDate
FROM SaleGoal

+------------+----------+------------+------------+----------+
|SalesPerson |SalesYear |CurrentQuota|NextQuota   | ModDate  |
+------------+----------+------------+------------+----------+
|Bob         |2011      |28000.00    |367000.00   |2011-04-15|
|Mary        |2011      |367000.00   |556000.00   |2011-04-16| 
|Mary        |2011      |556000.00   |7000.00     |2011-07-15| 
|Bob         |2011      |7000.00     |NULL        |2011-07-17|
|Bob         |2012      |70000.00    |502000.00   |2012-01-15|
|Mary        |2012      |502000.00   |154000.00   |2012-01-16| 
...
+------------+----------+------------+------------+----------+

LAG() ile önceki değeri alma

Önceki satırdaki değeri sorgulamayı sağlar
LAG() ile PreviousQuota sütunu oluşturulur
Satırları sıralamak için ORDER BY gerekir

T-SQL'de LAG()

SELECT SalesPerson, SalesYear, CurrentQuota, 
-- Önceki satırdan değerleri almak için pencere işlevi oluşturun
       LAG(CurrentQuota) 
       OVER (PARTITION BY SalesYear ORDER BY ModifiedDate) AS PreviousQuota, 
       ModifiedDate AS ModDate
FROM SaleGoal

+------------+----------+------------+-------------+----------+
|SalesPerson |SalesYear |CurrentQuota|PreviousQuota |ModDate  |
+------------+----------+------------+-------------+----------+
|Bob         |2011      |28000.00    |NULL         |2011-04-15|
|Mary        |2011      |367000.00   |28000.00     |2011-04-16| 
|Mary        |2011      |556000.00   |367000.00    |2011-07-15|
|Bob         |2011      |7000.00.00  |556000.00    |2011-07-17|
|Bob         |2012      |7000.00     |NULL         |2012-01-15|
|Mary        |2012      |502000.00   |7000.00      |2012-01-16|
...
+------------+----------+------------+------------+----------+

Haydi pratik yapalım!

Orta Düzey SQL Server