需求：表如下

以上数据中，goods_type列，假设26代表是广告，现在有个需求，想获取每个用户每次搜索下非广告类型的商品位置自然排序，如果下效果：

在数据分析和处理的过程中，我们经常会遇到包含NULL值的数据。在Hive中，NULL值的处理需要特别的注意，因为它们可能会影响查询的结果，甚至导致分析结果的不准确。本文通过案例将指导你如何在Hive中高效处理NULL值问题，确保数据分析的准确性和可靠性。

理解NULL值：在Hive中，NULL表示缺失的或未知的值。它与空字符串或零值不同，因此在进行数据处理时需要特别注意。




检测NULL值：使用IS NULL或IS NOT NULL操作符可以帮助你检测字段中的NULL值。例如：




SELECT * FROM table_name WHERE column_name IS NULL;




避免NULL值影响聚合：在使用聚合函数（如SUM、AVG等）时，NULL值通常会被忽略。但如果你想要将NULL值考虑在内，可以使用COALESCE或NVL函数来为NULL值指定一个默认值。




使用COALESCE和NVL函数：这两个函数可以帮助你将NULL值转换为一个具体的值。例如，你可以将所有的NULL值转换为0或一个空字符串，这样就可以在计算中包含这些值。




处理JOIN中的NULL值：当使用JOIN语句时，如果JOIN的列中存在NULL值，可能会导致某些行不出现在结果集中。为了解决这个问题，你可以使用外连接（LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN）来保证这些行的出现。




NULL值与ORDER BY：order by 时，desc NULL 值排在首位，ASC时NULL值排在末尾 可以通过NULLS LAST、NULLS FIRST 控制




创建表时处理NULL值：在创建表时，可以为表中的列指定默认值。这样，当插入缺失值时，Hive会自动使用默认值代替NULL。




NULL值与Lateral view outer:Lateral view outer,当table function不输出任何一行时，对应的输入行在Lateral view结果中依然保留，且所有table function输出列为null。Lateral View Outer 是Hive 中的一个特性，用于处理table function的结果。当table function没有输出时，使用OUTER关键字可以确保原始行仍保留在结果集中，用NULL填充table function的输出列。

create table test_null_last(
  id int comment '学生id',
  name string comment '学生姓名'
) comment '学生信息表';
 
insert into table test_null_last
(id,name) values
(1, "xiaoming"),
(2, "xiaohei"),
(3, "xiaohong"),
(4, "xiaobai"),
(5, "xiaolv"),
(null, "aaaaa");

order by id 正序排序

select id,
       name,
       row_number() over ( order by id ) as rk
from test_null_last;

此时NULL值排在首位，如果希望正序排序，且NULL值排在最后，可以通过指定NULLS LAST来控制。

select id,
       name,
       row_number() over ( order by id NULLS last ) as rk
from test_null_last;

select user_id
     , goods_name
     , goods_type
     , rk
     , case
           when goods_type != 26 then
               row_number() over (partition by if(goods_type != 26, user_id, rand()) order by rk) end naturl_rank
from goods
order by rk

此时采用一分为二的思想将NULL单独分组排序，最终通过CASE WHEN的形式将获得结果置为NULL。