如何从Employees与Online_Transactions表获取唯一用户最新登录记录

需求背景 #

• 需求目标：根据用户使用的在线平台不同，获取唯一的当前用户记录，同时判断用户近1个月是否处于活跃状态，若近1个月不活跃则需获取用户最后一次活跃时间。
• 涉及两张表：
  • Employees表：存储离职员工的离职日期与唯一ID，该ID是和在线交易表关联的外键
  • Online Transactions表：基于月度报告生成，存储从各在线平台拉取的全量用户清单，包含ID激活日期、最后登录日期、平台内角色、存储数据量等核心字段，当前共覆盖3个平台的数据：Portal、AGOL、Training
• 业务用途：基于两张表管理在线工具权限，同时统计各平台单用户的整体使用情况

现存问题 #

现有查询SQL返回大量重复记录，原因是DISTINCT作用于查询的全部字段而非仅用户Tracking ID，任意字段值不同都会被判定为独立记录，无法获取每个用户的唯一最新记录。
原查询SQL如下：

SELECT DISTINCT   
    U.EMPLOYEE_TRACKING_ID,U.LAST_NAME, U.FIRST_NAME, U.EMAIL_ADDRESS, U.PERSON_TYPE, 
    U.SERVICE_LINE, U.SUPERVISOR_NAME, U.OFFICE_LOCATION, U.OFFICE_CITY, U.OFFICE_STATE, 
    U.OFFICE_COUNTRY, U.OFFICE_POSTAL_CODE, U.ACTUAL_TERMINATION_DATETIME, O.FiscalPeriod, 
    O.Role, O.Source, O.LogDate
FROM  dbo.Users U
INNER JOIN dbo.Online_Transactions O ON U.EMPLOYEE_TRACKING_ID = O.TrackingId
WHERE O.Source ='AGOL'

待解决疑问 #

是否需要通过嵌套子查询按办公地点分组，来获取每个唯一用户对应的最新登录日期？计划针对三类平台分别创建在职、离职2种视图，共6个视图，该需求的正确实现方式是什么？

核心去重逻辑 #

不需要嵌套子查询按办公地点分组，使用窗口函数ROW_NUMBER()即可实现按用户维度取最新登录记录：按用户唯一ID分区，按登录日期倒序排序，取每个分区排名为1的记录，就是对应用户的最新登录数据。

通用基础查询示例（以AGOL平台为例） #

WITH UserLatestLogin AS (
    SELECT 
        U.EMPLOYEE_TRACKING_ID,
        U.LAST_NAME, 
        U.FIRST_NAME, 
        U.EMAIL_ADDRESS, 
        U.PERSON_TYPE, 
        U.SERVICE_LINE, 
        U.SUPERVISOR_NAME, 
        U.OFFICE_LOCATION, 
        U.OFFICE_CITY, 
        U.OFFICE_STATE, 
        U.OFFICE_COUNTRY, 
        U.OFFICE_POSTAL_CODE, 
        U.ACTUAL_TERMINATION_DATETIME, 
        O.FiscalPeriod, 
        O.Role, 
        O.Source, 
        O.LogDate,
        -- 按用户ID分区，按登录日期倒序排名
        ROW_NUMBER() OVER (PARTITION BY U.EMPLOYEE_TRACKING_ID ORDER BY O.LogDate DESC) AS rn,
        -- 新增近1个月活跃状态标记
        CASE WHEN O.LogDate >= DATEADD(MONTH, -1, GETDATE()) THEN '是' ELSE '否' END AS IsLastMonthActive
    FROM dbo.Users U
    INNER JOIN dbo.Online_Transactions O ON U.EMPLOYEE_TRACKING_ID = O.TrackingId
    WHERE O.Source = 'AGOL' -- 替换为Portal/Training即可对应不同平台
)
SELECT *
FROM UserLatestLogin
WHERE rn = 1 -- 仅保留每个用户的最新一条记录
-- 筛选在职/离职可追加对应条件：
-- 在职：AND (ACTUAL_TERMINATION_DATETIME IS NULL OR ACTUAL_TERMINATION_DATETIME > GETDATE())
-- 离职：AND ACTUAL_TERMINATION_DATETIME <= GETDATE()

6个视图创建方案 #

仅需修改上述SQL中的平台参数Source和在职/离职筛选条件，即可快速创建全部所需视图：

• AGOL平台在职用户视图：Source = 'AGOL' + 在职筛选条件
• AGOL平台离职用户视图：Source = 'AGOL' + 离职筛选条件
• Portal平台在职用户视图：Source = 'Portal' + 在职筛选条件
• Portal平台离职用户视图：Source = 'Portal' + 离职筛选条件
• Training平台在职用户视图：Source = 'Training' + 在职筛选条件
• Training平台离职用户视图：Source = 'Training' + 离职筛选条件

补充说明 #

如果同一用户同一登录日期存在多条角色不同的记录，可根据业务需求调整ROW_NUMBER()的排序规则，比如追加O.Role作为第二排序维度，或改用RANK()函数保留同日期的多条符合条件的记录。

内容的提问来源于stack exchange，提问作者JoeW