MSSQL LAST_VALUE: 分组取最大值最小值最后一个值
背景:
根据 特定字段 分组,获取第一次和最后一次的时间,同时要求获得最后一次的其他信息
数据
结果
脚本
语法
LAST_VALUE (expression) OVER ( [partition_clause] [order_clause] [frame_clause] )
PARTITION BY
子句:分配结果集成由一个或多个表达式指定多个分区expr1
,expr2
等LAST_VALUE()
函数被独立地施加到每个分区
ORDER BY
子句:指定LAST_VALUE()
函数运行的分区中行的逻辑顺序
frame_clause子句:
定义了所述当前分区的所述子集LAST_VALUE()
函数应用
-- 默认帧规范:从第一行开始,到结果集的当前行结束 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- 从第一行开始,到结果集的最后一行结束 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
示例
WITH V_All AS ( SELECT 1 AS UserCode, '2022-05-01' AS DateTime, 'asdasd1' AS Remark UNION ALL SELECT 1 AS UserCode, '2022-05-02' AS DateTime, 'asdasd2' AS Remark UNION ALL SELECT 1 AS UserCode, '2022-05-03' AS DateTime, 'asdasd3' AS Remark UNION ALL SELECT 2 AS UserCode, '2022-05-04' AS DateTime, 'asdasd4' AS Remark UNION ALL SELECT 2 AS UserCode, '2022-05-05' AS DateTime, 'asdasd5' AS Remark UNION ALL SELECT 2 AS UserCode, '2022-05-06' AS DateTime, 'asdasd6' AS Remark UNION ALL SELECT 3 AS UserCode, '2022-05-07' AS DateTime, 'asdasd7' AS Remark ), V_Group as ( SELECT UserCode, MIN(DateTime) MinDateTime, MAX(DateTime) MaxDateTime FROM V_All GROUP BY UserCode ), V_Value as ( SELECT DISTINCT UserCode , FIRST_VALUE(Remark) OVER (PARTITION by UserCode ORDER BY DateTime RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FIRST_VALUE , LAST_VALUE(Remark) OVER (PARTITION by UserCode ORDER BY DateTime RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) LAST_VALUE FROM V_All ) --SELECT * FROM V_All SELECT * FROM V_Group G INNER JOIN V_Value V ON G.UserCode = V.UserCode
Reference: