网站前台架构,施工程找工程做哪个网站好,如何创建个人博客网站,湖南seo网站策划目录
1 需求描述
2 数据准备
3 问题分析
4 小结
如果觉得本文对你有帮助#xff0c;想进一步学习SQL语言这门艺术的#xff0c;那么不妨也可以选择去看看我的博客专栏 #xff0c;部分内容如下#xff1a;
数字化建设通关指南
专栏 原价99#xff0c;现在活动价59…目录
1 需求描述
2 数据准备
3 问题分析
4 小结
如果觉得本文对你有帮助想进一步学习SQL语言这门艺术的那么不妨也可以选择去看看我的博客专栏 部分内容如下
数字化建设通关指南
专栏 原价99现在活动价59.9按照阶梯式增长直到恢复原价。 1 需求描述
需求表如下 以上数据中goods_type列假设26代表是广告现在有个需求想获取每个用户每次搜索下非广告类型的商品位置自然排序如果下效果 2 数据准备
create table goods as(select stack(8,1, hadoop, 10, 1,1, hive, 12, 2,1, sqoop, 26, 3,1, hbase, 10, 4,1, spark, 13, 5,1, flink, 26, 6,1, kafka, 14, 7,1, oozie, 10, 8) as (user_id, goods_name, goods_type, rk)); 3 问题分析
在数据分析和处理的过程中我们经常会遇到包含NULL值的数据。在Hive中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的输出列。 Hive 高版本中order by时也可以像oracle那样指定NULLS LAST、NULLS FIRST 控制。
验证
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; 可以看到此时id按照正序排序时NULL值排在最后。
通过上面的铺垫我们给出本题SQL如下
select user_id,goods_name,goods_type,rk,casewhen goods_type 26 thenrow_number() over (partition by user_id order by case when goods_type 26 then rk end nulls last)end as rk2
from goods t
order by rk; 上述SQL解释
此处要注意在Hive中先执行的是窗口函数然后才是case when 语句这一点一定要注意否则容易理解错。因此上述SQL先对 goods_type 26 时rk进行正序排序等于26的为NULL被放在最后然后执行case when 语句goods_type 26时取上述排好的顺序等于26的置为NULL这样得到最终的结果。
如果hive中或其他数据库不支持NULLS LAST特性的我们也可以采用动态分组的方法达到类似效果具体SQL如下
select user_id, goods_name, goods_type, rk, casewhen goods_type ! 26 thenrow_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。 另外也可以采用UNION ALL的解法但此时需要扫描表2次性能较差不是最好的解法 SQL如下
select user_id, goods_name, goods_type, rk, row_number() over (partition by user_id order by rk) as naturl_rank
from goods
where goods_type ! 26
union all
select user_id, goods_name, goods_type, rk, null as naturl_rank
from goods
where goods_type 26
4 小结
本文通过案例分析了SQL中Order by语句后NULLS LAST特性的使用方法及技巧NULL值在排序时往往给问题带来了不便及困扰但可以通过NULLS FIRST 及NULLS LAST来控制给问题的解决带来了方便。同时此题需要注意case when等条件语句then 中 使用分析函数时先执行的是分析函数最后执行case when语句与我们通常理解的顺序不一样。
如果觉得本文对你有帮助想进一步学习SQL语言这门艺术的那么不妨也可以选择去看看我的博客专栏 部分内容如下
数字化建设通关指南
专栏 原价99现在活动价59.9按照阶梯式增长直到恢复原价。
专栏主要内容1SQL进阶实战技巧 可以参考如下教程具体链接如下
SQL很简单可你却写不好也许这才是SQL最好的教程
上面链接中的文章及技巧会不定期更新。
2数仓建模实战技巧和个人心得 1新人入职新公司后应如何快速了解业务 2以业务视角看宽表化建设 3) 维度建模 or 关系型建模 4业务模型与数据模型有什么区别业务阶段的模型该如何建设 5业务指标体系该如何建设指标体系该如何维护指标平台应如何建设指标体系 该由谁来搭建 6如何优雅设计DWS层DWS层模型好坏该如何评价 7指标发生异常该如何排查应从哪些方面入手寻找问题点 8 数据架构的选择mpp or hadoop? 9数仓团队应如何体现自己的业务价值讲好数据故事 10BI与大数据有什么关系BI与信息化、数字化之间有什么关系BI与报表之间的关 系 11数据部门如何与业务部门沟通并规划指引业务需求
文章不限于以上内容有新的想法也会及时更新到该专栏。
具体专栏链接如下 数字化建设通关指南_莫叫石榴姐的博客-CSDN博客
