九个最容易出错的 Hive sql 详解及使用注意事项 （二）

6. left semi join

为什么把这个单独拿出来说，因为它和其他的 join 语句不太一样，
这个语句的作用和 in/exists 作用是一样的，是 in/exists 更高效的实现
SELECT A.* FROM A where id in (select id from B)
SELECT A.* FROM A left semi join B ON A.id=B.id
上述两个 sql 语句执行结果完全一样，只不过第二个执行效率高

注意事项：

1. left semi join 的限制是：join 子句中右边的表只能在 on 子句中设置过滤条件，在 where 子句、select 子句或其他地方过滤都不行。
2. left semi join 中 on 后面的过滤条件只能是等于号，不能是其他的。
3. left semi join 是只传递表的 join key 给 map 阶段，因此left semi join 中最后 select 的结果只许出现左表。
4. 因为 left semi join 是 in(keySet) 的关系，遇到右表重复记录，左表会跳过

7. 聚合函数中 null 值

hive支持 count(),max(),min(),sum(),avg() 等常用的聚合函数

注意事项：

聚合操作时要注意 null 值：

count(*) 包含 null 值，统计所有行数；

count(id) 不包含id为 null 的值；

min 求最小值是不包含 null，除非所有值都是 null；

avg 求平均值也是不包含 null。

以上需要特别注意，null 值最容易导致算出错误的结果

8. 运算符中 null 值

hive 中支持常用的算术运算符(+,-,*,/)  
比较运算符(>, <, =)
逻辑运算符(in, not in)
以上运算符计算时要特别注意 null 值

注意事项：

1. 每行中的列字段相加或相减，如果含有 null 值，则结果为 null

例：有一张商品表（product）

各字段含义： id (商品id)、price (价格)、dis_amount (优惠金额)

我想算每个商品优惠后实际的价格，sql如下：

select id, price - dis_amount as real_amount from product;

得到结果如下：

id=2的商品价格为 null，结果是错误的。

我们可以对 null 值进行处理，sql如下：

select id, price - coalesce(dis_amount,0) as real_amount from product;
使用 coalesce 函数进行 null 值处理下，得到的结果就是准确的
coalesce 函数是返回第一个不为空的值
如上sql：如果dis_amount不为空，则返回dis_amount，如果为空，则返回0

2. 小于是不包含 null 值，如 id < 10；是不包含 id 为 null 值的。

3. not in 是不包含 null 值的，如 city not in ('北京','上海')，这个条件得出的结果是 city 中不包含 北京，上海和 null 的城市。

9. and 和 or

在sql语句的过滤条件或运算中，如果有多个条件或多个运算，我们都会考虑优先级，如乘除优先级高于加减，乘除或者加减它们之间优先级平等，谁在前就先算谁。那 and 和 or 呢，看似 and 和 or 优先级平等，谁在前先算谁，但是，and 的优先级高于 or。

注意事项：

例：

还是一张商品表（product）

我想要统计下电器或者家具这两类中价格大于100的商品，sql如下：

select * from product where classify = '电器' or classify = '家具' and price>100

得到结果

结果是错误的，把所有的电器类型都查询出来了，原因就是 and 优先级高于 or，上面的sql语句实际执行的是，先找出 classify = '家具' and price>100 的，然后在找出 classify = '电器' 的

正确的 sql 就是加个括号，先计算括号里面的：

select * from product where (classify = '电器' or classify = '家具') and price>100