sql - 获取唯一的当前用户，具体取决于他们使用的在线平台

Question

我们的目标是衡量他们在上个月是否仍然活跃，如果不是上个月用户上次活跃的时间。我们正在存储两个表，Employees 和 Online Transactions。在线交易表是根据月度报告构建的，我们在月度报告中访问在线平台并获取所有用户的列表，以及他们的 ID 激活日期和上次登录时间。我们获得其他对我们很重要的数据，例如他们在在线平台中扮演什么角色以及他们存储了多少数据。

Employee 表包含离开公司的员工的终止日期，以及唯一的 ID。唯一 id 是我们加入 Online 表的内容。

我们使用这两个表来管理在线工具的访问以及报告每个平台的单个用户对平台的总体使用情况。使用了三种不同的平台，Portal、AGOL 或 Training。Online transactions 表将所有三个存储在同一个表中。

问题是我不了解获得所需结果的正确方法。我附上了示例代码，我知道结果集不是我想要的。当然，由于这是 PII，我必须清理我的示例结果以删除信息，但仍然显示我遇到的问题。

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

在示例结果集中，您可以看到有重复记录，因为我不仅将跟踪 ID 作为不同的一部分，因此导致每个字段的不同值都被考虑。

我是否需要进行某种类型的内部选择来了解不同的用户，按办公室和他们报告的最新日志日期？例如，我们的记录可以追溯到 3.5 年，但我想要的是他们最后一次登录时的所有唯一用户。我可以删除当前用户的终止日期并保留终止日期，以便我可以删除已离开的用户。我以为我会做一系列视图来获取每个场景，因此总共会有 6 个，每个 Online 类型一个，第二个用于终止与否？

如果有人可以帮助我学习如何做到这一点。

EMPLOYEE_TRACKING_ID,LAST_NAME,FIRST_NAME,EMAIL_ADDRESS,PERSON_TYPE,SERVICE_LINE,SUPERVISOR_NAME,OFFICE_LOCATION,OFFICE_CITY,OFFICE_STATE,OFFICE_COUNTRY,OFFICE_POSTAL_CODE,ACTUAL_TERMINATION_DATETIME,FiscalPeriod,Role,Source,LogDate
111483,Name4,User4,[e-mail],Employee,NULL,,,Melbourne,VIC,Australia,3008,4/30/2021,FY2020-Q4-08,AECOM PUBLISHER,AGOL,2020-09-02 00:00:00.000
111483,Name4,User4,[e-mail],Employee,NULL,,,Melbourne,VIC,Australia,3008,4/30/2021,FY2020-Q4-09,AECOM PUBLISHER,AGOL,2020-09-30 00:00:00.000
111483,Name4,User4,[e-mail],Employee,NULL,,,Melbourne,VIC,Australia,3008,4/30/2021,FY2021-Q1-11,AECOM PUBLISHER,AGOL,2021-02-02 00:00:00.000
111483,Name4,User4,[e-mail],Employee,NULL,,,Melbourne,VIC,Australia,3008,4/30/2021,FY2021-Q1-12,AECOM PUBLISHER,AGOL,2021-03-01 00:00:00.000
111483,Name4,User4,[e-mail],Employee,NULL,,,Melbourne,VIC,Australia,3008,4/30/2021,FY2021-Q2-01,AECOM PUBLISHER,AGOL,2021-05-01 00:00:00.000
111483,Name4,User4,[e-mail],Employee,NULL,,,Melbourne,VIC,Australia,3008,4/30/2021,FY2021-Q2-02,AECOM PUBLISHER,AGOL,2020-09-02 00:00:00.000
111483,Name4,User4,[e-mail],Employee,NULL,,,Melbourne,VIC,Australia,3008,4/30/2021,FY2021-Q2-03,AECOM PUBLISHER,AGOL,2021-01-04 00:00:00.000
113311,Name3,User3,[e-mail],Employee,NULL,,,Sydney,NSW,Australia,2000,5/21/2021,FY2020-Q3-06,AECOM COLLECTOR,AGOL,2020-09-02 00:00:00.000
14001627,Name1,User1,[e-mail],Employee,NULL,,,Melbourne,VIC,Australia,3008,3/3/2021,FY2021-Q1-12,AECOM COLLECTOR,AGOL,2021-02-02 00:00:00.000
14001627,Name1,User1,[e-mail],Employee,NULL,,,Melbourne,VIC,Australia,3008,3/3/2021,FY2021-Q2-01,AECOM COLLECTOR,AGOL,2021-04-01 00:00:00.000
14001627,Name1,User1,[e-mail],Employee,NULL,,,Melbourne,VIC,Australia,3008,3/3/2021,FY2021-Q2-02,AECOM COLLECTOR,AGOL,2020-09-02 00:00:00.000
14007604,Name2,User2,[e-mail],Employee,NULL,,,Newcastle upon Tyne,POST-TWR,United Kingdom,NE1 2HF,9/30/2020,FY2020-Q2-03,AECOM COLLECTOR,AGOL,2020-09-02 00:00:00.000

标签： sqlsql-servertsql

Answer 1

看起来您的 Online_Transactions 表为您的用户提供了多个条目？

您可以使用 CROSS APPLY 来获取一行注释 ORDER BY 以获取最新的

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,
T.FiscalPeriod, T.Role, T.Source, T.LogDate
FROM dbo.Users AS U
CROSS APPLY (
    SELECT TOP 1 O.FiscalPeriod, O.Role, O.Source, O.LogDate
    FROM dbo.Online_Transactions AS O
    WHERE O.TrackingId = U.EMPLOYEE_TRACKING_ID
    AND O.Source = 'AGOL'
    ORDER BY O.LogDate DESC
) AS T

这也可以通过内部选择子查询来完成，但我喜欢这种语法。您可能想将 CROSS APPLY 更改为 OUTER APPLY 只是为了查看结果，但我通常使用类似于 INNER JOIN 的 CROSS APPLY，因为它只会显示与 Online_Transaction 匹配的用户。