Hive sql 易错点

nvl函数：
是一个空值转换函数
NVL（表达式1，表达式2）
如果表达式1为空值，NVL返回值为表达式2的值，否则返回表达式1的值。
该函数的目的是把一个空值（null）转换成一个实际的值。
其表达式的值可以是数字型、字符型和日期型。但是表达式1和表达式2的数据类型必须为同一个类型。

SELECT    id,  latitude     FROM     t    GROUP BY    id ;  
报错提示：Line 1 Expression not in GROUP BY key 'latitude'
问题出现的原因：
1.Hive不允许直接访问非group by字段；
2.对于非group by字段，可以用Hive的collect_set函数收集这些字段，返回一个数组；
3.使用数字下标，可以直接访问数组中的元素；
解决办法:使用Hive的  collect_set(col)[0]  函数
SELECT    id,  collect_set(latitude)[0] as latitude  FROM t  GROUP BY  id ;  

left join on 后and 和 where 的区别
两者放置相同条件，之所以可能会导致结果集不同，就是因为优先级。on的优先级是高于where的。
on是在生成临时表的时候使用的条件，不管on的条件是否起到作用，都会返回左表 (table_name1) 的行。
where则是在生成临时表之后使用的条件，此时已经不管是否使用了left join了，只要条件不为真的行，全部过滤掉。

对于JOIN参与的表的关联操作，如果需要不满足连接条件的行也在我们的查询范围内的话，
我们就必需把连接条件放在ON后面，而不能放在WHERE后面
（比如左表的数据范围比连接表的范围大，连接条件放在on后面，不满足连接表的会以null形式显示，
如果放在where后面，就会把这些数据过滤掉）。
如果我们把连接条件放在了WHERE后面，那么所有的LEFT,RIGHT,等这些操作将不起任何作用，
对于这种情况，它的效果就完全等同于INNER连接。对于那些不影响选择行的条件，放在ON或者WHERE后面就可以。
记住：所有的连接条件都必需要放在ON后面，不然前面的所有LEFT,和RIGHT关联将作为摆设，而不起任何作用。

时间戳（13位数毫秒）转换成标准日期：
from_unixtime(cast(time/1000 as BIGINT),'yyyy-MM-dd') as `标准日期`
变量替换：
end_dt=datetime.datetime("-1d","yyyy-MM-dd")
start_dt=datetime.datetime("-30d","yyyy-MM-dd")

hive日期函数：to_date()，year(),month(),day(),hour(),minute(),second(),weekofyear()日期转周
select to_date('2018-12-08 10:03:01');--2018-12-08  返回日期时间字段中的日期部分

date_sub（‘yyyy-MM-dd’，n/-m）
返回初始日期n天前、m天后的日期
hive> select date_sub('2019-06-25',4);
2019-06-21
hive> select date_sub('2019-06-25',-2)
2019-06-27

解析json ：get_json_object(  ,  )
说明： 
第一个参数填写json对象变量，第二个参数使用$表示json变量标识，然后用 . 或 [] 读取对象或数组；如果输入的json字符串无效，那么返回NULL，每次只能返回一个数据项。

举例： 
data =
{
 "store":
        {
         "fruit":[{"weight":8,"type":"apple"}, {"weight":9,"type":"pear"}],  
         "bicycle":{"price":19.95,"color":"red"}
         }, 
 "email":"amy@only_for_json_udf_test.net", 
 "owner":"amy" 

1.get单层值
hive> select  get_json_object(data, '$.owner') from test;
结果：amy
2.get多层值.
hive> select  get_json_object(data, '$.store.bicycle.price') from test;
结果：19.95
3.get数组值[]
hive> select  get_json_object(data, '$.store.fruit[0]') from test;
结果：{"weight":8,"type":"apple"}

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

SQL UNION 语法
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

SQL UNION ALL 语法
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

 

 

rank()over()、dense_rank() over()、row_number() over()区别

区别
（1）rank() over()例子

（2）dense_rank() over()例子

（3）row_number() over()例子