配置单元大小写导致重复行

pb3skfrl 于 2021-06-25 发布在 Hive

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

我有一个包含联系人号码的表和另一个包含“length”变量和数字列的引用表。我需要的是找到前缀名称，其中数字的前缀与引用表中的前缀匹配，但它应该是与最长前缀匹配的前缀(天哪，我希望这有道理）
到目前为止我试过的：

select a.record_type,a.number,b.prefix,b.prefix_name 
from first_table a , second_table b 
where  a.transaction_date=20180924 and case  
    when b.length=1 then substr(a.number,1,1)=b.prefix  
    when b.length=2 then substr(a.number,1,2)=b.prefix  
    when b.length=3 then substr(a.number,1,3)=b.prefix  
    when b.length=4 then substr(a.number,1,4)=b.prefix  
    when b.length=5 then substr(a.number,1,5)=b.prefix  
    when b.length=6 then substr(a.number,1,6)=b.prefix  
    when b.length=7 then substr(a.number,1,7)=b.prefix  
    when b.length=8 then substr(a.number,1,8)=b.prefix 
    when b.length=9 then substr(a.number,1,9)=b.prefix 
    when b.length=10 then substr(a.number,1,10)=b.prefix 
    when b.length=11 then substr(a.number,1,11)=b.prefix 
    when b.length=12 then substr(a.number,1,12)=b.prefix 
    when b.length=13 then substr(a.number,1,13)=b.prefix 
    when b.length=14 then substr(a.number,1,14)=b.prefix 
end

但是它仍然返回重复的结果，即：如果数字是12345，它将匹配前缀为1234和123的引用，而实际上我只需要1234。
有没有什么办法可以把这个案子分清轻重缓急？谢谢
两个表中的数据示例：example
我当前的结果和期望的结果：结果

sql Hive Database hql

来源：https://stackoverflow.com/questions/52604974/hive-case-resulting-duplicate-row

2条答案

按热度按时间

im9ewurl1#

你可以用 row_number() :

select ap.*
from (select a.record_type, a.number, p.prefix, p.prefix_name,
             row_number() over (partition by  a.record_type, a.number order by p.length desc) as seqnum
      from first_table a join
           second_table p
           on (p.length = 1 and substr(a.number, 1, 1) = p.prefix) and
              (p.length = 2 and substr(a.number, 1, 2) = p.prefix) and
              . . . 
              (p.length = 14 and substr(a.number, 1, 14) = p.prefix)    
      where a.transaction_date = 20180924 
     ) ap
where seqnum = 1;

这可以更简洁地表述为：

select ap.*
from (select a.record_type, a.number, p.prefix, p.prefix_name,
             row_number() over (partition by  a.record_type, a.number order by p.length desc) as seqnum
      from first_table a join
           second_table p
           on substr(a.number, 1, p.length) = p.prefix    
      where a.transaction_date = 20180924 
     ) ap
where seqnum = 1;

另一种方法是利用个体差异进行比较 join 在第一场比赛中停下来：

select a.record_type, a.number,
       coalesce(p14.prefix, p13.prefix, . . . , p1.prefix) as prefix,
       coalesce(p14.prefix_name, p13.prefix_name, . . . , p1.prefix_name) as prefix_name
from first_table a left join
     second_table p14
     on p14.length = 14 and substr(a.number, 1, 14) = p14.prefix left join
     second_table p13
     on p13.length = 13 and substr(a.number, 1, 13) = p13.prefix and p14.prefix is null left join
     second_table p12
     on p12.length = 12 and substr(a.number, 1, 12) = p12.prefix and p13.prefix is null left join
     . . .
     second_table p1
     on p1.length = 1 and substr(a.number, 1, 1) = p1.prefix and p2.prefix is null

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

nr9pn0ug2#

好吧，我改了，试试这个：

WITH FIRST_TABLE (RECORD_TYPE,NUM,TRANSACTION_DATE)AS (
    SELECT 'a',12345, DATE '2018-09-24' FROM DUAL
    ),
    SECOND_TABLE (PREFIX,PREFIX_NAME,LENGTH) AS(
    SELECT 12,'Type A', 2 FROM DUAL union all
    SELECT 1234,'Type B', 4 FROM DUAL 
    )
    select * from (
    SELECT A.RECORD_TYPE,A.NUM,B.PREFIX,B.PREFIX_NAME, MAX(B.PREFIX) OVER (PARTITION BY A.RECORD_TYPE,A.NUM) maxPrefix
    FROM FIRST_TABLE A ,SECOND_TABLE B
    WHERE  A.TRANSACTION_DATE=DATE '2018-09-24' 
    AND A.NUM LIKE (B.PREFIX||'%')
    )
    where PREFIX=maxPrefix;

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

我来回答

配置单元大小写导致重复行

2条答案

相关问题

热门标签

最新问答