Hive Sql实现高难度的 sql 需求

最新推荐文章于 2022-11-11 22:00:00 发布

Wu_Candy

最新推荐文章于 2022-11-11 22:00:00 发布

阅读量561

点赞数

分类专栏： SQL 文章标签： hive sql 数据仓库

本文链接：https://blog.csdn.net/weixin_41754309/article/details/125246438

版权

本文通过一个实例展示了如何使用Hive SQL查询每个用户相邻两次浏览时间差小于三分钟的次数。解题过程中详细解析了正则替换、日期格式化、窗口函数row_number()以及时间差计算等关键步骤，适合对Hive SQL感兴趣的读者学习。

摘要由CSDN通过智能技术生成

题目:

(1).前置条件：

有以下数据集I，表查询结果如下图所示，设置该表为表：test_user_scan。

(2).题目要求：

使用 hive sql 查询出每个用户相邻两次浏览时间之差小于三分钟的次数。

预期结果:

解题思路:

(1). 子查询G 作为 left join 的主表，主要是为了获取所有的user_id

查询结果如下：

user_id   scan_time
1         2022-01-07 21:13:07
1         2022-01-07 21:15:25
1         2022-01-07 21:17:44
2         2022-01-13 21:14:09
2         2022-01-13 21:18:19
2         2022-01-13 21:20:36
3         2022-01-21 21:16:51
4         2022-01-02 21:17:22
4         2022-01-16 22:22:09
4         2022-01-30 15:15:44
4         2022-01-30 15:17:57

(2). 子查询H 作为 left join 的副表，主要是为了统计每个用户相邻两次浏览时间之差小于三分钟的总次数。

查询结果如下：

user_id     cnt
1           2
2           1
4           1

子查询H = 子查询C join 子查询D

(C=D, 使用C join D进行自关联，是为了处理：“相邻两次”和“浏览时间之差小于三分钟”的逻辑。)

子查询C，查询结果如下(与子查询D查询结果一致)：

user_id   scan_time                rn
1         2022-01-07 21:13:07      1
1         2022-01-07 21:15:25      2
1         2022-01-07 21:17:44      3
2         2022-01-13 21:14:09      1
2         2022-01-13 21:18:19      2
2         2022-01-13 21:20:36      3
3         2022-01-21 21:16:51      1
4         2022-01-02 21:17:22      1
4         2022-01-16 22:22:09      2
4         2022-01-30 15:15:44      3
4         2022-01-30 15:17:57      4

子查询D，查询结果如下：

user_id   scan_time              rn 
1         2022-01-07 21:13:07     1
1         2022-01-07 21:15:25     2
1         2022-01-07 21:17:44     3
2         2022-01-13 21:14:09     1
2         2022-01-13 21:18:19     2
2         2022-01-13 21:20:36     3
3         2022-01-21 21:16:51     1
4         2022-01-02 21:17:22     1
4         2022-01-16 22:22:09     2
4         2022-01-30 15:15:44     3
4         2022-01-30 15:17:57     4

(3). 最后使用子查询G 的结果 left join 子查询H 的结果，查询结果如预期结果所示

使用 user_id 作为关联条件，并对 cnt 为 null 的数据进行 nvl 判断转换为0，最后使用 user_id 和 cnt 进行分组过滤重复数据

解题方式一:

适用于不用创建物理表的情况下

使用数据集I、A、E替代物理表：test_user_scan，直