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]