首页 / MYSQL / MYSQL/HIVESQL笔试题（七）：HIVESQL（七）

MYSQL/HIVESQL笔试题（七）：HIVESQL（七）

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MYSQL/HIVESQL笔试题（七）：HIVESQL（七），小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含2416字，纯文字阅读大概需要4分钟。

内容图文

题目一：计算平台的每一个用户发过多少朋友圈、获得多少点赞

已知，数据如下：

T1：10万行数据

技术分享图片

 

 

T2：1000万行数据（注：没有被点赞的日记此表不做记录）

技术分享图片

需求：请用sql计算出如下结果：

技术分享图片

题目二：处理产品版本号
版本号信息存储在数据表中，每行一个版本号。

版本号命名规则符合正则表达式：(?:[1-9]\d|[1-9])(.(?:[1-9]\d|\d)){1,2}

补充说明：

产品版本号由三个部分组成

如：9.11.2

第一部分9为主版本号，为1-99之间的数字；

第二部分11为子版本号，为0-99之间的数字；

第三部分2为阶段版本号，为0-99之间的数字（可选）；

请根据具体条件和问题，使用hive sql编程：

1.条件：T1表有100个版本号，找出其中最大的版本号。

举例：

 

技术分享图片

2.条件：T1表有1000万个版本号，给如下格式的所有版本号排序。对于相同的版本号，顺序号一致。

table_result （排序结果表） :

技术分享图片

题目三、计算用户留存
用户留存是体现平台健康程度的重要数据指标之一，请用hive sql完成如下需求：

已知：

用户信息表结构：

CREATE TABLE if NOT EXISTS tb_user (
     user_id BIGINT COMMENT "用户id",
     create_time STRING COMMENT "注册时间,如：2019-01-01 08:00:00"
)
COMMENT "用户信息表";

访问日志表结构

CREATE TABLE if NOT EXISTS tb_access_log (
     user_id BIGINT COMMENT "用户id",
     page_url STRING COMMENT "网站页面路径,用于标记不同页面",
     create_time STRING COMMENT "访问时间,如：2019-01-01 08:00:01"
)
COMMENT "用户访问日志表"
PARTITIONED by (dt STRING COMMENT "格式：yyyymmdd ,数值与create_time转化后的日期一致";

tb_user : 总量大约200万行

技术分享图片

 

 

定义：

新用户：某一日新注册的用户
新用户次1日留存：T日成为新用户，T+1日回访平台网站任意页面（T+1即T日期的第二天）
新用户次3日留存：T日成为新用户，T+3日回访平台网站任意页面
新用户次n日留存：T日成为新用户，T+n日回访平台网站任意页面
1.假设平台元旦节做了一次拉新活动，当日新增用户数约为平日的两倍，2019年1月9日，距离活动已经过去一周了**，公司希望评估一下2019年1月1日当天的全部新增用户，在接下来一周的留存情况****(即1月2日-1月8日每天的留存用户数)，要求交付的数据结果表如下，请用一个简洁的sql****计算出如下数据： **

技术分享图片

 

 


2.本次活动拉新留存情况符合预期，公司决定后续会不定期举办拉新活动，以获得更多新用户，如何设计调度任务，使得报表按天增量更新如下，帮助公司长期监控平台新用户的留存，希望保留全量历史数据，方便随时对比各期活动情况？（请给出完整的相关sql，包括建表语句，写入数据过程等）

（以下数据范例最近更新日期为：20190109）

表名：reporting_new_user_retain_day

新用户数 次1日留存用户数 次2日留存用户数 次3日留存用户数 次4日留存用户数 次5日留存用户数 次6日留存用户数 次7日留存用户数 dt（成为新用户日期，格式yyyymmdd）
技术分享图片

 

 温馨提示：答题结果建议保存为sql文件或者pdf格式，避免代码排版错乱等问题，谢谢！

SELECT DATE_FORMAT(t1.create_time, "yyyy-MM-dd"),
       count(t1.user_id),
       COUNT(CASE
                 WHEN DATEDIFF(DATE_FORMAT(t1.create_time, "yyyy-MM-dd"), t2.create_time) = 1 THEN t1.USER_id
             END) ,
       COUNT(CASE
                 WHEN DATEDIFF(DATE_FORMAT(t1.create_time, "yyyy-MM-dd"), t2.create_time) = 2 THEN t1.USER_id
             END),
       COUNT(CASE
                 WHEN DATEDIFF(DATE_FORMAT(t1.create_time, "yyyy-MM-dd"), t2.create_time) = 3 THEN t1.USER_id
             END),
       COUNT(CASE
                 WHEN DATEDIFF(DATE_FORMAT(t1.create_time, "yyyy-MM-dd"), t2.create_time) = 4 THEN t1.USER_id
             END)
FROM tb_user t1
LEFT JOIN （
SELECT user_id,
       DATE_FORMAT(create_time, "yyyy-MM-dd") AS create_time
FROM tb_access_log
GROUP BY user_id,
         DATE_FORMAT(create_time, "yyyy-MM-dd") ) t2 ON t1.user_id = t2.user_id
GROUP BY DATE_FORMAT(t1.create_time, "yyyy-MM-dd")

考虑到数据量大的问题，只算近七天的数据，之前的数据算完之后保存为历史，近7天增量更新

 

原文：https://www.cnblogs.com/qiu-hua/p/14880969.html

内容总结

以上是互联网集市为您收集整理的MYSQL/HIVESQL笔试题（七）：HIVESQL（七）全部内容，希望文章能够帮你解决MYSQL/HIVESQL笔试题（七）：HIVESQL（七）所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/1231929.html

来源：【匿名】