开窗函数 First_Value 和 Last_Value
来源:互联网 发布:apache weblogic 插件 编辑:程序博客网 时间:2024/05/16 05:32
首先分析一下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 ALLSELECT 2 AS ID ,'2016-06-05' AS DT,'A' AS UName,148 AS TotalAmount UNION ALLSELECT 3 AS ID ,'2016-06-02' AS DT,'B' AS UName,120 AS TotalAmount UNION ALLSELECT 4 AS ID ,'2016-06-06' AS DT,'B' AS UName,153 AS TotalAmount UNION ALLSELECT 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' AS UName,135 AS TotalAmount UNION ALLSELECT 2 AS ID ,'2016-06-05' AS DT,'A' AS UName,148 AS TotalAmount UNION ALLSELECT 3 AS ID ,'2016-06-02' AS DT,'B' AS UName,120 AS TotalAmount UNION ALLSELECT 4 AS ID ,'2016-06-06' AS DT,'B' AS UName,153 AS TotalAmount UNION ALLSELECT 5 AS ID ,'2016-06-10' AS DT,'B' AS UName,198 AS TotalAmount )SELECT * FROM CTE a CROSS APPLY(SELECT TOP 1 a.TotalAmount AS FirstDeal,DT AS FirstDate FROM CTE WHERE a.UName = CTE.UName ORDER BY CTE.ID) AS b--或者改写成这种形式;WITH CTE AS(SELECT 1 AS ID ,'2016-06-01' AS DT,'A' AS UName,135 AS TotalAmount UNION ALLSELECT 2 AS ID ,'2016-06-05' AS DT,'A' AS UName,148 AS TotalAmount UNION ALLSELECT 3 AS ID ,'2016-06-02' AS DT,'B' AS UName,120 AS TotalAmount UNION ALLSELECT 4 AS ID ,'2016-06-06' AS DT,'B' AS UName,153 AS TotalAmount UNION ALLSELECT 5 AS ID ,'2016-06-10' AS DT,'B' AS UName,198 AS TotalAmount )SELECT a.*,b.TotalAmount AS FirstDeal,b.DT AS FirstDate FROM CTE a LEFT JOIN CTE b ON a.UName = b.UName AND NOT EXISTS(SELECT * FROM CTE WHERE b.UName = UName AND DT < b.DT)
在这三种写法里面,查询的结果是一致的,燃鹅从查询分析器分析的分析来看,查询性能,使用First_Value 的效率最高,not exists 的效率其次,使用Cross Apply 效率最低。
但是这只是从查询这么少量的测试数据反馈出来的结果,如果具体的场景需要应用,最好是结合实际情况看实际的查询计划来得出最适当的查询效果。
然后说下 Last_Value() 的用法,虽然说First_Value 和 Last_Value 一看就想两兄弟。但是!用起来真不是这样的一回事啊!
如果根据First_Value 的解释,那么Last_Value 就是根据Partition进行分区,根据Order By 进行排序返回最后的一个值。想我是这样想的,但是操作起来就不是这么一回事了。
;WITH CTE AS(SELECT 1 AS ID ,'2016-06-01' AS DT,'A' AS UName,135 AS TotalAmount UNION ALLSELECT 2 AS ID ,'2016-06-05' AS DT,'A' AS UName,148 AS TotalAmount UNION ALLSELECT 3 AS ID ,'2016-06-02' AS DT,'B' AS UName,120 AS TotalAmount UNION ALLSELECT 4 AS ID ,'2016-06-06' AS DT,'B' AS UName,153 AS TotalAmount UNION ALLSELECT 5 AS ID ,'2016-06-10' AS DT,'B' AS UName,198 AS TotalAmount )SELECT * , LAST_VALUE(CTE.TotalAmount) OVER ( PARTITION BY CTE.UName ORDER BY CTE.DT) AS LR FROM CTEID DT UName TotalAmount LR----------- ---------- ----- ----------- -----------1 2016-06-01 A 135 1352 2016-06-05 A 148 1483 2016-06-02 B 120 1204 2016-06-06 B 153 1535 2016-06-10 B 198 198
咦!?说好的根据 UName 进行分组,然后再DT进行排序区最后一个价格呢??完全不是这样子啊!!
对,这才是Last_Value的用法,实际上。在我测试的版本里面 (2012,2014), 除了根据 Partition By 进行分区,还对Order by 不一样的值产生不一样的取值。
所以,如果你想看到这个效果,我们不妨把测试样例数据修改一下,把其中2个DT改成一样的,如下面效果
;WITH CTE AS(SELECT 1 AS ID ,'2016-06-05' AS DT,'A' AS UName,135 AS TotalAmount UNION ALLSELECT 2 AS ID ,'2016-06-05' AS DT,'A' AS UName,148 AS TotalAmount UNION ALLSELECT 3 AS ID ,'2016-06-02' AS DT,'B' AS UName,120 AS TotalAmount UNION ALLSELECT 4 AS ID ,'2016-06-02' AS DT,'B' AS UName,153 AS TotalAmount UNION ALLSELECT 5 AS ID ,'2016-06-10' AS DT,'B' AS UName,198 AS TotalAmount )SELECT * , LAST_VALUE(CTE.TotalAmount) OVER ( PARTITION BY CTE.UName ORDER BY CTE.DT) AS LR FROM CTEID DT UName TotalAmount LR----------- ---------- ----- ----------- -----------1 2016-06-01 A 135 1352 2016-06-05 A 148 1483 2016-06-02 B 120 1204 2016-06-06 B 153 1535 2016-06-10 B 198 198
so 现在就看到取值是不一样的,燃鹅,确还是有一个问题,到底哪个才是Last_Value 呢??查询计划说了算~(这个我还真没验证过,请各位大神指导一下)
所以啊,不要看名字就觉得First_Value 和 Last_Value 是亲兄弟啊!!是隔壁老王的啊!!
好,本次分享到这里~
- 开窗函数 First_Value 和 Last_Value
- 分析函数——FIRST_VALUE()和LAST_VALUE()
- Oracle 分析函数 Last_value和First_Value
- 8.6 First_value和Last_value
- Oracle分析函数之first_value和last_value 分析函数详解
- 分析函数first_value()与last_value()
- 分析函数first_value()与last_value()
- oracle 分析函数 FIRST_VALUE、LAST_VALUE
- 分析函数:first_value,last_value用法
- Hive分析窗口函数之LAG,LEAD,FIRST_VALUE和LAST_VALUE
- 分析函数——first_value()与last_value()
- [Hive]窗口函数LEAD LAG FIRST_VALUE LAST_VALUE
- 分析函数--FIRST_VALUE,LAST_VALUE,LAG,LEAD,ROW_NUMBER
- SQL SERVER中FIRST_VALUE和LAST_VALUE
- last_value,first_value的用法
- HIVE分析窗口函数: LAG,LEAD,FIRST_VALUE,LAST_VALUE
- Hive分析窗口函数(四) LAG,LEAD,FIRST_VALUE,LAST_VALUE
- Hive分析窗口函数(四) LAG,LEAD,FIRST_VALUE,LAST_VALUE
- WordPress调用函数大全
- JNI层线程回调Java函数示例
- 关于datetime 和 int 之间相互转换
- 测试几个xml的问题
- Mysql 里面使用row_number() 的用法和注意
- 开窗函数 First_Value 和 Last_Value
- 关于Mysql 触发器
- POJ 1321
- oracle基础教程:Oracle应用系统结构
- 设计模式 -单例模式
- java 加解密
- NYOJ-8 一种排序【sort与qsort】
- UVA-10361 Automatic Poetry
- c# Timer_应用程序中生成定期事件