时间戳

首页 > 解决方案 > SQL - 从历史表中返回最近的行

sql - SQL - 从历史表中返回最近的行

问题描述

我正在使用 Salesforce 潜在客户和联系人数据（“人员”）并解析记录所有权历史，以查看某种类型的用户当前是否拥有或以前拥有这些人中的一个。

在某些情况下，一个人或多个人可能拥有记录，在这种情况下，我想返回最近的所有者信息（基于分配日期）

这是我的 SQL，它为每个线索/联系人记录返回重复的行。

with history as (
  select 
    id, 
    leadid as lead_or_contact_id, 
    createddate, 
    field, 
    oldvalue, 
    newvalue 
    from salesforce_leadhistory
  union
  select
    id, 
    contactid as lead_or_contact_id, 
    createddate,
    field, 
    oldvalue, 
    newvalue
  from salesforce_contacthistory
)

select 
  h.createddate as assignment_date,
  h.lead_or_contact_id,
  coalesce(old_user.name, new_user.name) as user_name,
  coalesce(old_user.id, new_user.id) as user_id,
  coalesce(old_user_role.name, new_user_role.name) as user_role
from history h
left join salesforce_user new_user on new_user.id = h.newvalue
left join salesforce_userrole new_user_role on new_user_role.id = new_user.userroleid
left join salesforce_user old_user on old_user.id = h.oldvalue
left join salesforce_userrole old_user_role on old_user_role.id = old_user.userroleid
where 
  field = 'Owner'
  and ( old_user_role.name like 'SDR%' or new_user_role.name like 'SDR%' )

例如，这将返回引用同一个人记录的两行：

assignment_date                 lead_or_contact_id  user_name        user_id    user_role
2020-01-01T00:42:37.000+00:00   00QXYZ              Joe Jones   123        SDR - EMEA
2020-10-14T03:25:39.000+00:00   00QXYZ              Max Clark   456        SDR - USA

在这种情况下，我希望返回第二行中的所有数据（分配日期为 2020 年 10 月 14 日，Max Clark 的用户名，user_id 456 和 user_role “SDR - USA”，因为这是最近的分配历史。

我怎样才能做到这一点？

标签： sql

解决方案

您可以使用row_number()

select * from
(
select 
  h.createddate as assignment_date,
  h.lead_or_contact_id,
  coalesce(old_user.name, new_user.name) as user_name,
  coalesce(old_user.id, new_user.id) as user_id,
  coalesce(old_user_role.name, new_user_role.name) as user_role,row_number() over(partition by h.lead_or_contact_id order by h.createddate desc) as rn
from history h
left join salesforce_user new_user on new_user.id = h.newvalue
left join salesforce_userrole new_user_role on new_user_role.id = new_user.userroleid
left join salesforce_user old_user on old_user.id = h.oldvalue
left join salesforce_userrole old_user_role on old_user_role.id = old_user.userroleid
where 
  field = 'Owner'
  and ( old_user_role.name like 'SDR%' or new_user_role.name like 'SDR%' )
)A where rn=1

推荐阅读