为一列查找min()日期，然后使用它与日期小于该日期的其他表联接

Question

简而言之，我有两个表：

(1(pharmacy_claims(列：user_id、date_service、claim_id、record_id、prescription(

(2(medical_claims(列：user_id、date_service、provider、npi、cost(

我想在(1(中找到具有特定prescription值的user_id，找到它们最早的date_service(例如min(date_service)(，然后使用这些具有最早服务日期的user_id作为队列，从(2(中提取它们的所有相关数据。基本上，我想找到他们第一次在pharmacy_claims中开处方之前的所有medical_claims数据。

pharmacy_claims看起来像这样：

user_id | prescription | date_service
1           a             2018-05-01
1           a             2018-02-11
1           a             2019-10-11
1           b             2018-07-12
2           a             2019-01-02
2           a             2019-03-10
2           c             2018-04-11
3           c             2019-05-26

因此，例如，如果我对prescription='a'感兴趣，我只希望返回user_id 1和2，日期分别为2018-02-11和2019-01-02。然后，我想从medical_claims中提取user_id 1和2，并获取它们在这些相应日期之前的所有数据。

我尝试的方法是在pharmacy_claims表中构建一个临时表，以查询具有给定药物的user_id，然后将其返回到表中，创建一个具有date_service的user_id队列

以下是我所做的：

(1( 从主药房索赔表中提取所有相关数据：

CREATE TABLE user.temp_pharmacy_claims AS
SELECT user_id, claim_id, record_id, date_service
FROM dw.pharmacyclaims 
WHERE date_service between '2018-01-01' and '2019-08-31'

这导致约50000个user_id的

(2( 只使用user_id的一个min(date_service):创建了一个表

CREATE TABLE user.temp_pharmacy_claims_index AS
SELECT distinct user_id, min(date_service) AS Min_Date
FROM user.temp_pharmacy_claims
GROUP BY 1

(3( 创建了一个最终表格(以获得所需的队列(：

CREATE TABLE user.temp_pharmacy_claims_final_index AS
SELECT a.userid
FROM user.temp_pharmacy_claims  a
LEFT JOIN user.temp_pharmacy_claims_index b
ON a.user = b.user
WHERE  a.date_service < Min_Date

然而，当应该有几千个结果时，这会给我0个结果。这个设置正确吗？这可能不是最有效的方法，但对我来说它看起来很好，所以不确定发生了什么

Answer 1

我想您只需要一个相关的子查询：

select mc.*
from medical_claims mc
where mc.date_service < (select min(pc.date)
from pharmacy_claims pc
where pc.user_id = mc.user_id and 
pc.prescription = ?
);