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: 

MySQL LAST_VALUE() 函数 

frame_clause介绍