将Teradata转换为hive sql

Question

我正在尝试将下面的teradata转换为hive，但在“row_number”附近的“qualify”行83:14处的“qualify”附近的ParseException行83:6处出现了错误。

我对蜂巢很陌生。任何帮助都将不胜感激。

select customer_id         tier_cust_id,

    support_segment     tier_suppt_seg
 from (select c.customer_id,
            c.primary_email_name,
            am_id,
            am_name, 
            c.customer_first_name ,
            c.customer_last_name ,
            c.customer_primary_residence   ,
            c.CUSTOMER_USER_GROUP,
            c.customer_flag1,
 case when c.customer_primary_residence not in ('US', 'MX', 'CA') then       'ROW'
                 when ent.cust_id = bam.cust_id then 'Enterprise Support'
                 when smb.cust_id = bam.cust_id then 'Merchant Support'
                 when am.am_name like '%Merchant Support'
                   or am.am_name like '%Business Support'
                   or am.am_name = 'Sole Proprietor'
                      then 'Business Support'
                 else 'Currently Unassigned'
             end support_segment,


            case when support_segment = 'Enterprise Support' then 10
                 when support_segment = 'Merchant Support' then 5
                 when support_segment = 'Business Support' then 1
                 when support_segment = 'ROW' then 0
             end segment_weight

      from dw_acct_mgr bam


     inner join dw_acc_mger am
        on bam.acct_mgr_id = am.am_id
       and pmod((am.flag1 / 1), 2 )= 0               
       and pmod((am.flag1 / 2) ,2) = 1                      
       and pmod((am.flag1 / 16) ,2) = 1
       and pmod((bam.acct_mgr_flag1 / 4),2) = 0     
       and pmod((bam.acct_mgr_flag1 / 8),2 )= 0     
       and am_name not in ('Enterprise Account', 
                           'SMB'
                          )


     inner join customer c
        on bam.cust_id = c.customer_id


      left outer join (select cust_id
                         from dw_acct_mgr
                        where acct_mgr_id in (select am_id
                                                from dw_acc_mger
                                               where am_name in ('Enterprise Account')
                                                 and pmod((flag1 / 1), 2) = 0                    
                                                 and pmod( (flag1 / 2) , 2) = 1                    
                                             )
                          and pmod((acct_mgr_flag1 / 4), 2) = 0            
                          and prod((acct_mgr_flag1 / 8) , 2) = 0            
                      ) ent
        on bam.cust_id = ent.cust_id


      left outer join (select cust_id
                         from dw_acct_mgr
                        where acct_mgr_id in (select am_id
                                                from dw_acc_mger
                                               where am_name in ('SMB')
                                                 and pmod((flag1 / 1), 2) = 0                      
                                                 and pmod((flag1 / 1), 2) = 1                  
                                             )
                          and pmod((acct_mgr_flag1 / 4) ,2) = 0            
                          and pmod((acct_mgr_flag1 / 8),2) = 0            
                      ) smb
        on bam.cust_id = smb.cust_id




  qualify row_number() over (partition by customer_id order by segment_weight desc, am_name asc) = 1
  ) a;

Answer 1

QUALIFY是专有的Teradata语法，您必须使用派生表重写它：

select customer_id         tier_cust_id,

    support_segment     tier_suppt_seg
 from (select c.customer_id,
            c.primary_email_name,
            am_id,
            am_name, 
            c.customer_first_name ,
            c.customer_last_name ,
            c.customer_primary_residence   ,
            c.CUSTOMER_USER_GROUP,
            c.customer_flag1,
 case when c.customer_primary_residence not in ('US', 'MX', 'CA') then       'ROW'
                 when ent.cust_id = bam.cust_id then 'Enterprise Support'
                 when smb.cust_id = bam.cust_id then 'Merchant Support'
                 when am.am_name like '%Merchant Support'
                   or am.am_name like '%Business Support'
                   or am.am_name = 'Sole Proprietor'
                      then 'Business Support'
                 else 'Currently Unassigned'
             end support_segment,


            case when support_segment = 'Enterprise Support' then 10
                 when support_segment = 'Merchant Support' then 5
                 when support_segment = 'Business Support' then 1
                 when support_segment = 'ROW' then 0
             end segment_weight,

             row_number() over (partition by customer_id order by segment_weight desc, am_name asc) as rn
      from dw_acct_mgr bam


     inner join dw_acc_mger am
        on bam.acct_mgr_id = am.am_id
       and pmod((am.flag1 / 1), 2 )= 0               
       and pmod((am.flag1 / 2) ,2) = 1                      
       and pmod((am.flag1 / 16) ,2) = 1
       and pmod((bam.acct_mgr_flag1 / 4),2) = 0     
       and pmod((bam.acct_mgr_flag1 / 8),2 )= 0     
       and am_name not in ('Enterprise Account', 
                           'SMB'
                          )


     inner join customer c
        on bam.cust_id = c.customer_id


      left outer join (select cust_id
                         from dw_acct_mgr
                        where acct_mgr_id in (select am_id
                                                from dw_acc_mger
                                               where am_name in ('Enterprise Account')
                                                 and pmod((flag1 / 1), 2) = 0                    
                                                 and pmod( (flag1 / 2) , 2) = 1                    
                                             )
                          and pmod((acct_mgr_flag1 / 4), 2) = 0            
                          and prod((acct_mgr_flag1 / 8) , 2) = 0            
                      ) ent
        on bam.cust_id = ent.cust_id


      left outer join (select cust_id
                         from dw_acct_mgr
                        where acct_mgr_id in (select am_id
                                                from dw_acc_mger
                                               where am_name in ('SMB')
                                                 and pmod((flag1 / 1), 2) = 0                      
                                                 and pmod((flag1 / 1), 2) = 1                  
                                             )
                          and pmod((acct_mgr_flag1 / 4) ,2) = 0            
                          and pmod((acct_mgr_flag1 / 8),2) = 0            
                      ) smb
        on bam.cust_id = smb.cust_id
  ) a
where rn = 1;