如何从表中创建对

igetnqfo  于 2021-06-24  发布在  Hive
关注(0)|答案(2)|浏览(225)

我有一个配置单元表,其中的数据如下所示-

每个客户都有相应的账户,目标是建立内部客户对。对是基于帐户是否有相同的出生年份或他们的名字的前3个字符是相同的。萨姆和塞缪尔。
输出结果如下-

理想情况下,不应创建aa、xx等相同的帐户对。另外,一对ac和ca都是相同的,因此只需要一个条目。姓名和出生年份密钥可以组成一对,但这里也只需要一个条目(可以是任何人)。
我应该如何处理这个问题。检验试验数据-

create table customer_account(
customer INT NOT NULL,
accounts VARCHAR(100) NOT NULL,
name VARCHAR(40) NOT NULL,
yob DATE,
);

INSERT INTO 
customer_account(customer,accounts,name,yob)
VALUES
(1,"A","John",2001),
(1,"X","Tom",1996),
(1,"C","Harry",2001),
(2,"D","Sam",1994),
(2,"F","Samuel",1995),
(3,"Z","Jake",)1994,
(3,"G","Drake",1998),
(3,"H","Arnold",1993),
(3,"K","Yang",1990)
;
k7fdbhmy

k7fdbhmy1#

您应该能够在配置单元语言中为联接使用子字符串。逻辑应该是合理的,虽然你可能需要调整它为您的需要一点。
你要做的是一元(或自)连接。下面是一个可以传递的查询类型的示例。实际上,您是在加入一个or条件,并用case语句测试该条件以获得“pair\u key”。我使用了一个内部连接,假设您只需要匹配发生的示例。

SELECT 
     t1.customer as Customer1,
     t2.customer as Customer2,
     t1.Accounts as Accounts1,
     t2.Accounts as Accounts2,
     CONCAT(t1.Accounts, t2.Accounts) as Pair_No,
     t1.Name as Name1,
     t2.Name as Name2,
     t1.YOB as YOB1,
     t2.YOB as YOB2,
     CASE
     WHEN t1.YOB = t2.YOB THEN 'YOB'
     WHEN SUBSTR(t1.Name, 3) = SUBSTR(t2.Name, 3) THEN 'Name'
     else 'Issue'
     END as Pair_Key
FROM (SELECT * FROM Table1) as t1
inner join (SELECT * FROM Table1) as t2 --instance 2 of the same table
on (SUBSTR(t1.Name, 3) = SUBSTR(t2.Name, 3) OR t1.YOB = t2.YOB)

如果没有测试数据或更多关于你走了多远的细节,这只是一个开始。
如果客户编号需要相同,只需调整为:

on (t1.Customer = t2.Customer) and (SUBSTR(t1.Name, 3) = SUBSTR(t2.Name, 3) OR t1.YOB = t2.YOB)
6tr1vspr

6tr1vspr2#

这就是你所描述的:

select t1.*, t2.name, t2.yob
from t t1 join
     t t2
     on t2.customer = t1.customer and
        (t2.yob = t1.yob or
         substr(t2.name, 1, 3) = substr(t1.name, 1, 3)
        ) and
        t2.account > t1.account;

没有必要去拿 customer 两次。如果需要“相同”对,则将最后一个条件更改为 >= .

相关问题