hive 如何通过非唯一ID加入保险单？

qq24tv8q 于 2023-10-18 发布在 Hive

关注(0)|答案(2)|浏览(162)

我在Hive里做一个请求。我有一个任务是在createDate起的两周内连接两个带有保险单的表。我有一个非唯一的密钥personno（个人ID），它在一个ID和createDate（创建保险单的日期）上有多条记录。我还有一个列applicationno，它是一个从（1到N）创建的保险单的索引，我们可以说它对所有保险单都是唯一的。我也有像保险金额这样的栏目。我需要将表1连接到表2。表1比表2长。
表1
| perl 诺|创建日期|应用程序|保险|
| --|--|--|--|
| 1 |2023年01月03日| 1 | 100 |
| 2 |2023年3月2日| 2 | 200 |
| 2 |2023年3月2日| 3 | 200 |
| 2 |2023年3月2日| 4 | 300 |
| 3 |2023年03月03日| 5 | 250 |
| 3 |2023年03月03日| 6 | 150 |
表2
| perl 诺|创建日期|应用程序|保险|
| --|--|--|--|
| 1 |2023年01月03日| 7 | 100 |
| 2 |2023年3月2日| 8 | 200 |
| 2 |2023年03月03日| 9 | 150 |
| 3 |2023年03月03日| 10 | 150 |
为了得到这样的东西，期望的结果是
| perl 诺|创建日期1|应用程序nno 1|保险1|创建日期2|应用nno 2|保险2|
| --|--|--|--|--|--|--|
| 1 |2023年01月03日| 1 | 100 |2023年01月03日| 7 | 100 |
| 2 |2023年3月2日| 2 | 200 |2023年3月2日| 8 | 200 |
| 2 |2023年3月2日| 3 | 200 |2023年03月03日| 9 | 150 |
| 2 |2023年3月2日| 4 | 300 |NULL| NULL| NULL|
| 3 |2023年03月03日| 5 | 250 |2023年03月03日| 10 | 150 |
| 3 |2023年03月03日| 6 | 150 |NULL| NULL| NULL|
我写了一个这样的请求

SELECT 
a.personno, 
a.createdate,
a.applicationno,
a.insurancesum,
b.applicationno,
b.insurancesum
FROM TABLE1 a
LEFT JOIN TABLE2 b
ON a.personno=b.personno
WHERE a.createdate Between cast(b.createdate as date)
AND DATE_ADD(cast(b.createdate as date),14);

而不是我想要的结果，我得到了类似重复答案的情况下，同一ID上的一个日期的许多行。
| perl 诺|创建日期1|应用程序nno 1|保险1|创建日期2|应用nno 2|保险2|
| --|--|--|--|--|--|--|
| 1 |2023年01月03日| 1 | 100 |2023年01月03日| 7 | 100 |
| 2 |2023年3月2日| 2 | 200 |2023年3月2日| 8 | 200 |
| 2 |2023年3月2日| 3 | 200 |2023年03月03日| 9 | 150 |
| 2 |2023年3月2日| 4 | 300 |2023年03月03日| 9 | 150 |
| 3 |2023年03月03日| 5 | 250 |2023年03月03日| 10 | 150 |
| 3 |2023年03月03日| 6 | 150 |2023年03月03日| 10 | 150 |
帮助真的很感激。

Hive

来源：https://stackoverflow.com/questions/76892369/how-can-i-join-insurance-policies-by-a-non-unique-id

2条答案

按热度按时间

js81xvg61#

SELECT 
a.personno, 
a.createdate,
a.applicationno,
a.insurancesum,
b.applicationno,
b.insurancesum
FROM (select TABLE1.*
             ,ROW_NUMBER() OVER (PARTITION BY PERSONNO ORDER BY APPLICATIONNO) AS 
RN1 from TABLE1) a
LEFT JOIN (select TABLE2.*
             ,ROW_NUMBER() OVER (PARTITION BY PERSONNO ORDER BY APPLICATIONNO) AS 
RN2 from TABLE2) b
ON a.personno=b.personno
AND a.rn1 = b.rn2
WHERE a.createdate Between cast(b.createdate as date)
AND DATE_ADD(cast(b.createdate as date),14);

赞(0）回复(0）举报 2023-10-18

hujrc8aj2#

您当前的查询是由于结果集中的重复行。您可以使用窗口函数为两个tbles中相同的personno和createDate组中的每行分配行号。然后，您可以在这些行编号的子查询上执行完全连接。
试试这个：

SELECT
    a.personno,
    a.createdate AS createdate1,
    a.applicationnno AS applicationnno1,
    a.insurancesum AS insurancesum1,
    b.createdate AS createdate2,
    b.applicationnno AS applicationnno2,
    b.insurancesum AS insurancesum2
FROM
    (SELECT *,
             ROW_NUMBER() OVER(PARTITION BY personno, createdate ORDER BY applicationnno) AS rn
      FROM TABLE1) a
LEFT JOIN
    (SELECT *,
             ROW_NUMBER() OVER(PARTITION BY personno, createdate ORDER BY applicationnno) AS rn
      FROM TABLE2) b
ON
    a.personno = b.personno
    AND a.rn = b.rn
WHERE
    a.createdate BETWEEN b.createdate AND DATE_ADD(b.createdate, 14)

主查询内的子查询将行号分配给同一个Person和CreateDate分区内的每行，其由ApplicationN0排序。这将有助于基于相同的创建顺序配对对应的plicies。然后主查询将修改后的personno和rn的子查询连接起来，并根据datate range条件过滤结果。这可确保仅匹配14天窗口内的策略。

赞(0）回复(0）举报 2023-10-18

我来回答

hive 如何通过非唯一ID加入保险单？

2条答案

相关问题

热门标签

最新问答