用分区左连接同一结构化表

gojuced7 于 2021-06-26 发布在 Hive

关注(0)|答案(4)|浏览(223)

我有两个相同的模式表：-

Table A : vendorname,branch,amount,region (partitioned by year,month,day)
Table B : vendorname,branch,amount,region (partitioned by year,month,day)

表a中的数据：

john,c1,112,us
 john,c2,113,uk
 john,c3,199,aus

表b中的数据：

john,c1,112,us
  john,c2,113,uk
  john,c3,99,aus
  john,c4,144,br
  john,c5,50,cr

输出：

john,c3,199,99,aus ==> mismatch for 199 and 99

需要将表a中的每条记录与表b中的每条记录进行比较。在表b中可以有其他记录。我正在尝试左连接，但做不到。
已尝试查询：

select * from (
(select vendorname,type,amount,region from A 
where vendorname='john' and  year='2018' and month='01' and day='01' ) t1
left join
(select vendorname,type,amount,region
from B
where vendorname='john' and  year='2018' and month='01' and day='01')t2
on (a.name=b.name and a.type=b.type))

但是也要为匹配的列获取空值
我不能查询整个表，因为它是分区的，我们需要从特定分区中选取数据，否则会影响性能

sql Hive partitioning left-join

来源：https://stackoverflow.com/questions/49630582/left-join-same-structured-table-with-partitioning

4条答案

按热度按时间

polkgigr1#

使用完全联接。它相当于left join和right join，将为您提供两个表中的所有数据。

select * from table1 a full join table2 b on a.vendorname=b.vendorname and a.branch=b.branch and a.amount=b.amount and a.region=b.region
where...

赞(0）回复(0）举报 2021-06-26

pwuypxnk2#

取决于所需的输出
供应商名称和分支机构匹配但金额不匹配的所有情况

select t2.*
  from ((select vendorname, branch, amount, region
           from A 
          where vendorname='john'
            and  year='2018' and month='01'
            and day='01' ) as t1
   join (select vendorname,branch,amount,region
                from B
               where vendorname='john'
                 and year='2018'
                 and month='01'
                 and day='01') as t2
  on (t1.vendorname=t2.vendorname
 and t1.branch=t2.branch))
where t1.amount <> t2.amount

供应商名称和分支机构匹配但金额不匹配的所有情况+表a中的附加记录+表b中的附加记录

select t1.*, t2.* from 
(select vendorname,branch,amount,region from A 
where vendorname='john' and  year='2018' and month='01' and day='01' ) t1
full outer join
(select vendorname,branch,amount,region
from B
where vendorname='john' and  year='2018' and month='01' and day='01')t2
on (t1.vendorname=t2.vendorname and t1.branch=t2.branch))
where t1.amount <> t2.amount or t1.amount is null or t2.amount is null

赞(0）回复(0）举报 2021-06-26

roqulrg33#

select b.* from A inner join B
on a.vendorname=b.vendorname
and a.branch=b.branch
where a.amount<>b.amount

在这里检查-http://sqlfiddle.com/#!9/f8a352/5号机组

赞(0）回复(0）举报 2021-06-26

wlsrxk514#

你能试试这个代码吗：

SELECT *
FROM tableA A full OUTER JOIN TABLEB B ON a.vendorname = B.vendorname AND  a.branch =b.branch 
WHERE EXISTS(
SELECT 1
FROM tableA A RIGHT JOIN tableB B ON a.vendorname = B.vendorname 
AND a.branch =b.branch 
AND a.amount<>b.amount 
AND a.region=b.region)

赞(0）回复(0）举报 2021-06-26

我来回答

用分区左连接同一结构化表

4条答案

相关问题

热门标签

最新问答