sql - 我如何在配置单元中为这个用例创建一个宏？

Question

我有以下带有重复 case when 语句的查询，我想为此创建宏

 select  
    *, 
    
    case when s1 between 0 and  40 then 'low'
      when s1 between 41 and  80 then 'medium'
    when s1 between 81 and  100 then 'high'
    end as first_segment , 
    
    
    case when s2 between 0 and  40 then 'low'
      when s2 between 41 and  80 then 'medium'
    when s2 between 81 and  100 then 'high'
    end as s_segement 
                      
              
      from   

table

我试过了，但它不起作用。其中 x 是一列

CREATE TEMPORARY MACRO bucket(x INT)
   case when x between 0 and  40 then 'low'
      when x between 41 and  80 then 'medium'
    when x between 81 and  100 then 'high'
    end as x+'_'+'bucket';

INT 是指列类型的内容吗？上面的宏不起作用..

ParseException line 5:8 missing EOF at 'as' near 'end'

标签： sqlhivemacroshiveql

Answer 1

在查询中添加宏别名：

CREATE TEMPORARY MACRO bucket(x INT)
   case when x between 0  and  40  then 'low'
        when x between 41 and  80  then 'medium'
        when x between 81 and  100 then 'high'
    end;

select bucket(s1) as first_segment,
       bucket(s2) as s_segement,
 from ...