【hive sql练习】

hive sql

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY order_condition]
[DISTRIBUTE BY distribute_condition [SORT BY sort_condition] ]
[LIMIT number]

union如何记住？

union all
union

多维度聚合分析 grouping sets/cube/roolup

SELECT os, device, city ,COUNT(*)
FROM requests
GROUP BY os, device, city GROUPING SETS((os, device), (city), ());

cube 会枚举指定列的所有可能组合作为 grouping sets，而 roolup 会以按层级聚合的方式产生 grouping sets。

GROUP BY CUBE(a, b, c)  
--等价于以下语句。  
GROUPING SETS((a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),())

GROUP BY ROLLUP(a, b, c)
--等价于以下语句。  
GROUPING SETS((a,b,c),(a,b),(a), ())

正则指定方法 不清楚

`(num|uid)?+.+` 排除 num 和 uid 字段列。

另外，where 使用正则可以如此：where A Rlike B、where A Regexp B。

lateral view

Lateral View 和表生成函数（例如Split、Explode等函数）结合使用，它能够将一行数据拆成多行数据，并对拆分后的数据进行聚合。

SELECT pageid, adid 
FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;

窗口函数

实现按指定字段的分组排序

row_number() over(partition by col order col_2)

join 本质是集合的运算

与集合的关系：
A+A∩B = A B+A∩B = B
A-A∩B A∩B B-A∩B
A∪B A+B-A∩B

注意点

1、left join、right join、full join不管on上的条件是否为真都会返回left或right表中的记录

2、排序字段(以下a,b,c)为null会导致结果不正确

row_number() over(partition by a,b,c by gmtModified desc) as rk

3、group by字段(以下a,b,c)为null，会导致结果不正确

group by a ,b ,c 

4、join字段为null会导致结果不正确

t1  left join t2 on t1.a=t2.a and t1.b=t2.b and t1.c=t2.c

5、在JOIN的时候最好保证JOIN的字段唯一，不然会出现数据膨胀，在计算聚合值的时候出现问题。

select 
* from a 
left join b 
on a.id=b.id