开窗函数 First_Value 和 Last_Value

在Sql server 2012里面，开窗函数丰富了许多，其中带出了2个新的函数 First_Value 和 Last Value .现在来介绍一下这2个函数的应用场景.

首先分析一下First_Value()，用法是根据Partition By对数据进行分区，如果忽略Partition By ，那么默认整块数据一个区域，然后根据Order By 进行排序，取出第一个值。

;WITH CTE AS(
SELECT 1 AS ID ,'2016-06-01' AS DT,'A' AS UName,135 AS TotalAmount UNION ALL
SELECT 2 AS ID ,'2016-06-05' AS DT,'A' AS UName,148 AS TotalAmount UNION ALL
SELECT 3 AS ID ,'2016-06-02' AS DT,'B' AS UName,120 AS TotalAmount UNION ALL
SELECT 4 AS ID ,'2016-06-06' AS DT,'B' AS UName,153 AS TotalAmount UNION ALL
SELECT 5 AS ID ,'2016-06-10' AS DT,'B' AS UName,198 AS TotalAmount 
)
SELECT * ,
        FIRST_VALUE(CTE.TotalAmount) OVER (PARTITION BY CTE.UName ORDER BY CTE.ID) AS FirstDeal,
        FIRST_VALUE(CTE.DT) OVER (PARTITION BY CTE.UName ORDER BY CTE.ID) AS FirstDate
    FROM CTE

ID DT UName TotalAmount FirstDeal FirstDate
----------- ---------- ----- ----------- ----------- ----------
1 2016-06-01 A 135 135 2016-06-01
2 2016-06-05 A 148 135 2016-06-01
3 2016-06-02 B 120 120 2016-06-02
4 2016-06-06 B 153 120 2016-06-02
5 2016-06-10 B 198 120 2016-06-02

在这个场景里面，我求出了根据用户名称(UName)来进行分区，根据ID进行一个排序，求出每个用户第一次购买商品的时间以及交易的金额。如果不使用First_Value 我们也可以换另外一种写法

;WITH CTE AS(
SELECT 1 AS ID ,'2016-06-01' AS DT,'A