网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。
一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!
学习!还是他娘地学习!
第31题 需求二: 按类别统计QQ号相关信息
需求列表
a) 写一段SQL,统计每天不同性别的qq号个数、总消息量、平均在线时长(注意2个表的qq号类型不同),且去 掉(不包括)性别为空的qq号。
b) 写一段SQL,找出每个省份里消息量排名第一的QQ号码。
思路分析
a)
table_use表的qq号是整数类型,而table_act表的qq号是字符串类型。这样的话,你需要在连接两个表的时候,把其中一个表的qq号转换成另一个表的qq号的类型,才能正确匹配。例如,你可以用cast函数把table_use表的qq号转换成字符串类型,然后和table_act表的qq号连接。😊
另外,你想要统计每天不同性别的qq号个数、总消息量、平均在线时长,那么你需要用group by语句来按照日期和性别分组,然后用count、sum和avg函数来计算相应的指标。😊
最后,你想要去掉性别为空的qq号,那么你需要用where语句来过滤掉这些数据。😊
b)
- 首先,使用join语句把两个表按照qq号连接起来,得到每个qq号码的性别、地域和消息量。
- 然后,使用rank函数和partition by子句对每个地域里的qq号码按照消息量进行降序排名,得到每个qq号码的排名。
- 接着,使用where子句过滤掉排名不是第一的qq号码,只保留每个地域里消息量最高的qq号码。
- 最后,使用select语句选择需要的字段,包括地域、qq号码、消息量和排名。
附表
table_usera中数据如下:
日期 | qq号 | 年龄 | 性别 | 地域 |
---|---|---|---|---|
20170101 | 10000 | 20 | 女 | 广东省 |
20170101 | 20000 | 30 | 男 | 北京市 |
20170101 | 30000 | 25 | 男 | 陕西省 |
20170101 | 50000 | 18 | 女 | 广东省 |
table_act表结构如表格3
列名 | 类型 | 长度 | 允许空 | 备注 |
---|---|---|---|---|
ftime | bigint | 日期 | ||
char | qq号 | |||
msg | bigint | 10 | 消息量 | |
onlinetime | double | 10 | 在线时长(h) |
table_act表数据包含数据如表格4
日期 | qq号 | 消息量 | 在线时长(h) |
---|---|---|---|
20170220 | 10000 | 100 | 1 |
20170220 | 20000 | 102 | 0.5 |
… | … | … | … |
20170420 | 30000 | 200 | 2 |
20170420 | 40000 | 300 | 0.8 |
20170420 | 50000 | 201 | 3 |
create table if not exists table_use (
lm_date int comment '日期',
qq int comment 'QQ号',
age string comment '年龄',
sex string comment '性别',
area string comment '地域'
);
insert into table_use
values (20170101, 20000, '30', '男', '北京市'),
(20170101, 30000, '25', '男', '陕西省'),
(20170101, 40000, '18', '女', '广东省'),
(20170101, 50000, '20', '女', '四川省');
select \*
from table_use;
-- 2、表结构如下:
create table if not exists table_act (
ftime int comment ' 日 期 ', qq string comment 'QQ号',
msg int comment ' 消 息 量 ', onlinetime int comment '在线时长(h)'
);
insert into table_act
VALUES (20170220, '10000', 100, 1),
(20170220, '20000', 102, 0.5),
(20170420, '30000', 200, 2),
(20170420, '40000', 300, 0.8),
(20170420, '50000', 201, 3);
select \*
FROM table_act;
答案获取
建议你先动脑思考,动手写一写再对照看下答案,如果实在不懂可以点击下方卡片,回复:大厂sql
即可。
参考答案适用HQL,SparkSQL,FlinkSQL,即大数据组件,其他SQL需自行修改。
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,涵盖了95%以上大数据知识点,真正体系化!
由于文件比较多,这里只是将部分目录截图出来,全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频,并且后续会持续更新
目录截图出来,全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频,并且后续会持续更新**