Hive中行列转换具体实现及应用
1、工作应用场景统计得到每个小时的UV、PV、IP的个数,构建如下表结构:
https://img2023.cnblogs.com/blog/2876640/202303/2876640-20230304171815000-1574287123.png
但是表中数据的存储格式不利于直接查询展示,需要进行调整:(以时间分区,去重、聚合等……对结果进行行列转换)
https://img2023.cnblogs.com/blog/2876640/202303/2876640-20230304172040449-2101509247.png
2、行转列
(1)多行转多列
https://img2023.cnblogs.com/blog/2876640/202303/2876640-20230304172503592-282908516.png
case when函数
功能:用于实现对数据的判断,根据条件,不同的情况返回不同的结果,类似于java中的switch case功能
语法:
语法一:
CASE
WHEN 条件1 THEN VALUE1
……
WHEN 条件N THEN VALUEN
ELSE 默认值 END
语法二:
CASE 列
WHEN V1 THEN VALUE1
……
WHEN VN THEN VALUEN
ELSE 默认值 END
https://img2023.cnblogs.com/blog/2876640/202303/2876640-20230304193540179-618586214.png
https://img2023.cnblogs.com/blog/2876640/202303/2876640-20230304193556651-389410084.png
(2)多行转单列
https://img2023.cnblogs.com/blog/2876640/202303/2876640-20230304193752774-658740091.png
可以指定分隔符的字符串拼接函数:concat_wa只支持string、array
https://img2023.cnblogs.com/blog/2876640/202303/2876640-20230304193951025-154979964.png
收集函数:
https://img2023.cnblogs.com/blog/2876640/202303/2876640-20230304194258140-1336920953.png
https://img2023.cnblogs.com/blog/2876640/202303/2876640-20230304194316005-1240961311.png
https://img2023.cnblogs.com/blog/2876640/202303/2876640-20230304194659879-1476046504.png
3、列转行
(1)多列转多行
https://img2023.cnblogs.com/blog/2876640/202303/2876640-20230304194947345-1136307246.png
https://img2023.cnblogs.com/blog/2876640/202303/2876640-20230304195006896-1747831100.png
https://img2023.cnblogs.com/blog/2876640/202303/2876640-20230304195055621-1478525281.png
https://img2023.cnblogs.com/blog/2876640/202303/2876640-20230304195257573-116447095.png
(2)单列转多行
https://img2023.cnblogs.com/blog/2876640/202303/2876640-20230304195409504-496972305.png
https://img2023.cnblogs.com/blog/2876640/202303/2876640-20230304195524788-1150448480.png
https://img2023.cnblogs.com/blog/2876640/202303/2876640-20230304195803923-1496947708.png
lateral view 侧视图
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!
页:
[1]