如何在连接两个表时只返回最近的日期

Question

我有一个医生NPI#的列表，我需要将其转换为内部客户ID#，然后使用这些客户ID来检查呼叫中心代表最后一次联系他们的时间。为此，我使用两个表，NPI_Conversion(cid、npi(和Call_Center(call_center_cid、customer_name、call_date(。我只想返回从呼叫中心联系客户的最近日期。这个查询为我提供了所需的数据，但它会返回对NPI:的每个调用

SELECT call_center_cid, cid, customer_name, caller_name, npi, call_date
FROM CALL_CENTER
LEFT JOIN NPI_CONVERSION
ON call_center_cid = cid
WHERE NPI IN ( # LIST OF RELEVANT NPI #'s)

我看到其他一些使用max(date)的帖子，所以我试着这样使用它：

SELECT call_center_cid, cid, customer_name, caller_name, npi, max(call_date) AS recent_call
FROM CALL_CENTER
LEFT JOIN NPI_CONVERSION
ON call_center_cid = cid
WHERE NPI IN ( # LIST OF RELEVANT NPI #'s)
GROUP BY 1, 2, 3, 4, 5

但这只返回每个caller_name的最大日期

Answer 1

可移植选项使用子查询进行筛选：

select cc.*, nc.npi
from call_center cc
inner join npi_conversion nc on cc.call_center_cid = np.cid
where 
nc.npi in (...)
and cc.call_date = (
select max(cc1.call_date)
from call_center cc1
where cc1.customer_name = cc.customer_name
)

这将利用call_center (customer_name, call_date)上的索引。

请注意，我将left join更改为inner join(where子句中的筛选清楚地表明了这一点(，并用它们所属的表作为所有列的前缀(这使查询更清楚地了解底层数据结构(。

如果你的数据库支持窗口功能，你也可以做：

select cc.*, np.npi
from (
select cc.*, 
rank() over(partition by customer_name order by call_center_cid desc) as rn
from call_center cc
) cc
inner join npi_conversion nc on cc.call_center_cid = np.cid
where nc.npi in (...)

Answer 2

类似于GMB的答案：

你可以在加入后应用ROW_NUMBER来获得最新的一行：

SELECT call_center_cid, cid, customer_name, caller_name, npi, call_date
FROM CALL_CENTER
LEFT JOIN NPI_CONVERSION -- RIGHT JOIN?
ON call_center_cid = cid
WHERE NPI IN ( # LIST OF RELEVANT NPI #'s)
QUALIFY -- filter latest date per cid
ROW_NUMBER()
OVER (PARTITION BY cid
ORDER BY call_date DESC) = 1

您当前的WHERE将LEFT联接转换为INNER联接，也许您希望使用RIGHT联接。