鋒利的SQL2014:基於視窗的位移計算,sql2014視窗位移

來源:互聯網
上載者:User

鋒利的SQL2014:基於視窗的位移計算,sql2014視窗位移
 

SQL Server 2012引入了四個位移函數:LAG和LEAD、FIRST_VALUE和LAST_VALUE,用於從當前行的某個位移量、或是一個視窗架構的開頭或結尾的行返回一個元素。

LAG和LEAD支援視窗分區和視窗排序子句,FIRST_VALUE和LAST_VALUE在支援視窗分區和視窗排序子句的基礎上,還支援視窗架構子句。

9.5.1 LAG和LEAD函數

LAG函數用於在當前行之前尋找,LEAD函數在之後尋找。函數的第一個參數(必選)指定要傳回值的列,第二個參數(可選)是位移量(如果未指定,預設為1),第三個參數(可選)是在請求的位移量沒有行返回的情況下的預設值(如果未指定,預設為NULL)。

下面的樣本使用在9.2節建立的Orders表,用於查詢僱員上一季度和下一季度的銷售額。其中,LAG函數返回上一季度銷售額,LEAD函數返回下一季度銷售值。查詢結果如表9-18所示。

SELECT EmpID, SalesYear, SalesQuarter,

 LAG(SubTotal)  OVER(PARTITION BYEmpID

                ORDER BY EmpID, SalesYear, SalesQuarter) AS PrevVal,

  SubTotalAS CurVal,

 LEAD(SubTotal) OVER(PARTITION BY EmpID

                ORDER BY EmpID, SalesYear, SalesQuarter) AS NextVal

FROM dbo.Orders;

 

由於沒有指定位移量,函數便假定為預設值1。並且沒有指定第三個參數,當沒有前一行或下一行時,便假定為預設值NULL。下面的查詢用於擷取僱員前面第二個季度和後面第二個季度的銷售額,運算式LAG(SubTotal, 2, 0)從前面第二行中擷取值,並且如果找不到行,將返回0。查詢結果如表9-19所示。

SELECT EmpID, SalesYear, SalesQuarter,

 LAG(SubTotal, 2, 0) OVER(PARTITION BY EmpID

                      ORDER BY EmpID,SalesYear, SalesQuarter) AS PrevVal,

  SubTotalAS CurVal,

 LEAD(SubTotal, 2, 0) OVER(PARTITION BY EmpID

                       ORDER BY EmpID,SalesYear, SalesQuarter) AS NextVal

FROM dbo.Orders;

表9-19                                   擷取僱員前面第二個季度和後面第二個季度的銷售額

EmpID

SalesYear

SalesQuarter

PrevVal

CurVal

NextVal

1

2013

1

0

100

300

1

2013

2

0

200

400

1

2013

3

100

300

200

1

2013

4

200

400

200

1

2014

1

300

200

100

1

2014

2

400

200

100

1

2014

3

200

100

0

1

2014

4

200

100

0

2

2013

1

0

150

350

2

2013

2

0

250

450

2

2013

3

150

350

250

2

2013

4

250

450

250

2

2014

1

350

250

150

2

2014

2

450

250

150

2

2014

3

250

150

0

2

2014

4

250

150

0

9.5.2 FIRST_VALUE和LAST_VALUE函數

FIRST_VALUE和LAST_VALUE函數分別允許從視窗架構的第一行和最後一行返回一個元素。因此,這些函數支援視窗分區、排序和架構子句。

如果希望元素來自視窗分區的第一行,應使用帶有視窗架構範圍“ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”的FIRST_VALUE。由於該架構是預設的,故而也可以省略。

如果希望元素來自視窗分區的最後一行,應使用帶有視窗架構範圍“ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING”的LAST_VALUE。需要注意的是,該架構不能省略,因為在未指定該架構的情況下會使用預設架構“ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”,這樣會導致最後一行就是當前行,即CURRENT ROW,所以即使使用了LAST_VALUE函數,也不會得到視窗的最後一行。

下面通過一個樣本來說明這兩個函數的使用方法。下面的查詢使用FIRST_VALUE函數返回僱員當年第一個季度的銷售額,使用LAST_VALUE函數返回當年最後一個季度的銷售額。查詢結果如表9-20所示。

SELECT EmpID, SalesYear, SalesQuarter,

 FIRST_VALUE(SubTotal) OVER(PARTITION BY EmpID, SalesYear

                        ORDER BY EmpID,SalesYear, SalesQuarter

                        ROWS BETWEEN UNBOUNDEDPRECEDING

                                    AND CURRENT ROW) AS FirstVal,

  SubTotalAS CurVal,

 LAST_VALUE(SubTotal) OVER(PARTITION BY EmpID, SalesYear

                       ORDER BY EmpID,SalesYear, SalesQuarter

                       ROWS BETWEEN CURRENT ROW

                                   AND UNBOUNDED FOLLOWING) AS LastVal

FROM dbo.Orders

ORDER BY EmpID, SalesYear, SalesQuarter;

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.