hive SQL 技巧积累

一、row_number() over(parttition by order by) 里分区字段可以是多个。

二、left join的字段可以是两个，其中一个可以是小于不一定都要等于。比如用两个表的时间做筛选，在主键id相同的情况下，可以以一个表的时间做基点，另一个表的时间小于他的做聚合，然后选出最小于他的最大时间。

三、多行聚合拼接:按照id聚合group by ，将需要选择合并的字段concat起来，作为你想要存放的字段。

1.c是去掉首尾的拼接符号，concat是普通拼接。

2.collect_set(collect_list类似，区别set去重) ,可以突破group by只能选择被聚合字段和聚合函数的限制。

SELECT id,
concat_ws('|', collect_set(str)) 
FROM t  
GROUP BY id;

这里collect_set是主要的，concat只是辅助将集合起来的元素拼接起来。

SELECT  deal_id
        ,concat(
            '['
            ,concat_ws( ',' ,collect_set( f1(inv_id,inv_name,inv_type) ) )
            ,']'
        ) AS investors
FROM    (
            SELECT  t1.deal_id AS deal_id
                    ,t1.inv_name
                    ,t1.inv_type
                    ,IF(
                        t2.legal_id IS NULL
                        ,inv_id
                        ,IF(t1.inv_type =3, t2.legal_id,inv_id)
                    ) AS inv_id
            FROM     t1
            LEFT JOIN  t2
            ON      t1.inv_id = t2.brand_id
        ) t3
GROUP BY deal_id

四、一行转多行

１、问题
hive如何将

a b 1,2,3
c d 4,5,6

变为：

a b 1
a b 2
a b 3
c d 4
c d 5
c d 6

答案

select col1, col2, col5
from tmp_bsf_test a
lateral view explode(split(col3,’,’)) b AS col5

注意：1.lateral view explode 要放在from后面，而不能放在select 里面。

           2.b一开始不知道是什么东西，列的别名col15可以理解，但是b不能理解，原来b是给打散的结果存的一个临时虚拟表，这也敲好印证了为什么1中选择不能放在select后面。

五、if(条件1，结果1，结果2)语法格式

六、cast（col1 as double）语法格式

七、null的格式一般是随列的属性而变的。如union时，为了拼接字段，有时需要加上null，这时null的属性就跟存在属性的部分一致。

但是如果直接选择 creat view view1 as select null from t ，就会报格式错误。应该将这里的null cast 成固定格式。

八、round(col1,6) 保留6位小数

九、get_json_object(col1,"$json_key")

十、split_part分(col1,"-",1)分割后取第一个结果（从1开始数而不是0）     

十一、translate  ： TRANSLATE(char, from, to)

select translate('abcdefga','abc','wo') 返回值 from dual;

https://www.iteblog.com/archives/1639.html这总结的比较全

十二、translate和regex_replace的区别

regex_replace(source_string,pattern,repalce_string) 对source_string字段中符合pattern正则格式的，替换为replace_string

translate(chars1,chars2) 用chars1来替换chars2中对应位置的字符，一一对应替换。

十三、求日期差函数

1.日期比较函数: datediff语法: datediff(string enddate,string startdate)
返回值: int
说明: 返回结束日期减去开始日期的天数。
例如：hive>select datediff('2018-12-22','2018-12-12')
结果为：1
2.日期增加函数: date_add语法: date_add(string startdate, intdays)
返回值: string
说明: 返回开始日期startdate增加days天后的日期
例如：hive>select date_add('2018-12-12',12)
结果为：2018-12-24
3.日期减少函数: date_sub语法: date_sub (string startdate,int days)
返回值: string

说明: 返回开始日期startdate减少days天后的日期。
例如：hive>select date_sub('2018-12-24',10)
结果：2018-12-14