json_tuple函数 的利用
首先在Hive 中查看json_tuple 函数的详细信息
desc function extended json_tuple;
出现解释：
json_tuple(jsonStr, p1, p2, …, pn) - like get_json_object, but it takes multiple names and return a tuple. All the input parameters and output column types are string. |
jsonStr,就是我们自己的json数据文件名称，p1,p2,p3…等是json 数据的参数
例题
： **json格式的电影评分数据## **
- 首先解析json
create table t_json(json string); - 加载数据到表中
select json_tuple(json,‘movie’,‘rate’,‘timeStamp’,‘uid’) as
(movie,rate,ts,uid) from tb_json limit 20;
查看建表数据
|+--------+-------+------------+------+
| movie | rate | ts | uid |
+--------+-------+------------+------+
| 1193 | 5 | 978300760 | 1 |
| 661 | 3 | 978302109 | 1 |
| 914 | 3 | 978301968 | 1 |
| 3408 | 4 | 978300275 | 1 |
| 2355 | 5 | 978824291 | 1 |
| 1197 | 3 | 978302268 | 1 |
| 1287 | 5 | 978302039 | 1 |
| 2804 | 5 | 978300719 | 1 |
| 594 | 4 | 978302268 | 1 |
| 919 | 4 | 978301368 | 1 |
+--------+-------+------------+------+| |
- 将表数据创建到新表
create table tb_movie_rate as select json_tuple
(json,‘movie’,‘rate’,‘timeStamp’,‘uid’) as (movie,rate,st,uid) from
tb_json;
以上是将建立新表到后面练习可以用
接下来就是练习题：
1、统计每部电影的平均得分
create table tb_avg_rate as select movie, avg(rate) from
tb_movie_rate group by movie;
2、统计数据中一共有多少部电影
create table tb_cout_movie as select count(distinct movie) from
tb_movie_rate;
+--------------------+
| tb_cout_movie._c0 |
+--------------------+
| 3706 |
+--------------------+
3、统计每部电影的被评分次数（热门度）
create table tb_sum_movie as select movie, count(distinct uid) from
tb_movie_rate group by movie;
+---------------------+-------------------+
| tb_sum_movie.movie | tb_sum_movie._c1 |
+---------------------+-------------------+
| 1 | 2077 |
| 10 | 888 |
| 100 | 128 |
| 1000 | 20 |
| 1002 | 8 |
| 1003 | 121 |
| 1004 | 101 |
| 1005 | 142 |
| 1006 | 78 |
| 1007 | 232 |
+---------------------+-------------------+
4、统计每个人的所有电影评分的平均值
create table tb_avg_movie as select uid, count(distinct rate) from
tb_movie_rate group by uid;
+-------------------+-------------------+
| tb_avg_movie.uid | tb_avg_movie._c1 |
+-------------------+-------------------+
| NULL | 1 |
| 1 | 5 |
| 10 | 4 |
| 100 | 5 |
| 1000 | 4 |
+-------------------+-------------------
5、查询出每个人评分最高的20部电影信息
第一步先筛选用户并且将评论排序，利用row_number函数和新窗口函数 over()。
select
*, row_number() over(partition by uid order by rate desc) rn from tb_movie_rate ;
+----------------------+---------------------+-------------------+--------------------+-----+
| tb_movie_rate.movie | tb_movie_rate.rate | tb_movie_rate.st | tb_movie_rate.uid | rn |
+----------------------+---------------------+-------------------+--------------------+-----+
| 104 | 4 | 978298486 | NULL | 1 |
| 1566 | a | 978824330 | 1 | 1 |
| 1035 | 5 | 978301753 | 1 | 2 |
| 1287 | 5 | 978302039 | 1 | 3 |
| 150 | 5 | 978301777 | 1 | 4 |
| 1 | 5 | 978824268 | 1 | 5 |
| 1028 | 5 | 978301777 | 1 | 6 |
| 1029 | 5 | 978302205 | 1 | 7 |
| 48 | 5 | 978824351 | 1 | 8 |
| 1193 | 5 | 978300760 | 1 | 9 |
| 2028 | 5 | 978301619 | 1 | 10 |
| 1270 | 5 | 978300055 | 1 | 11 |
| 1961 | 5 | 978301590 | 1 | 12 |
| 595 | 5 | 978824268 | 1 | 13 |
| 1836 | 5 | 978300172 | 1 | 14 |
| 1022 | 5 | 978300055 | 1 | 15 |
| 527 | 5 | 978824195 | 1 | 16 |
| 2804 | 5 | 978300719 | 1 | 17 |
| 2355 | 5 | 978824291 | 1 | 18 |
| 3105 | 5 | 978301713 | 1 | 19 |
+----------------------+---------------------+-------------------+--------------------+-----+
第二部在上面第一步的基础上筛选出最高分排名前20的电影，并创建一个新表存储数据。
create table tb_hight_rate as select uid,rate,rn,movie from ( select
*, row_number() over(partition by uid order by rate desc) rn from tb_movie_rate ) t where rn<=20;
+--------------------+---------------------+-------------------+----------------------+
| tb_hight_rate.uid | tb_hight_rate.rate | tb_hight_rate.rn | tb_hight_rate.movie |
+--------------------+---------------------+-------------------+----------------------+
| NULL | 4 | 1 | 104 |
| 1 | a | 1 | 1566 |
| 1 | 5 | 2 | 1035 |
| 1 | 5 | 3 | 1287 |
| 1 | 5 | 4 | 150 |
| 1 | 5 | 5 | 1 |
| 1 | 5 | 6 | 1028 |
| 1 | 5 | 7 | 1029 |
| 1 | 5 | 8 | 48 |
| 1 | 5 | 9 | 1193 |
| 1 | 5 | 10 | 2028 |
| 1 | 5 | 11 | 1270 |
| 1 | 5 | 12 | 1961 |
| 1 | 5 | 13 | 595 |
| 1 | 5 | 14 | 1836 |
| 1 | 5 | 15 | 1022 |
| 1 | 5 | 16 | 527 |
| 1 | 5 | 17 | 2804 |
| 1 | 5 | 18 | 2355 |
| 1 | 5 | 19 | 3105 |
| 1 | 4 | 20 | 1207 |
| 10 | 5 | 1 | 953 |
| 10 | 5 | 2 | 3447 |
| 10 | 5 | 3 | 3087 |
| 10 | 5 | 4 | 1584 |
| 10 | 5 | 5 | 3259 |
| 10 | 5 | 6 | 1654 |
| 10 | 5 | 7 | 1653 |
| 10 | 5 | 8 | 1580 |
| 10 | 5 | 9 | 3155 |
| 10 | 5 | 10 | 2571 |
| 10 | 5 | 11 | 595 |
| 10 | 5 | 12 | 594 |
| 10 | 5 | 13 | 1079 |
| 10 | 5 | 14 | 1148 |
| 10 | 5 | 15 | 541 |
| 10 | 5 | 16 | 2640 |
| 10 | 5 | 17 | 1198 |
| 10 | 5 | 18 | 1073 |
| 10 | 5 | 19 | 2622 |
| 10 | 5 | 20 | 1197 |
| 100 | 5 | 1 | 800 |
| 100 | 5 | 2 | 527 |
| 100 | 5 | 3 | 919 |
| 100 | 4 | 4 | 329 |
| 100 | 4 | 5 | 2406 |
| 100 | 4 | 6 | 260 |
| 100 | 4 | 7 | 1291 |
| 100 | 4 | 8 | 1197 |
| 100 | 4 | 9 | 1196 |
+--------------------+---------------------+-------------------+----------------------+
魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。
更多推荐
2
0- 0
已为社区贡献1条内容
所有评论(0)