2024年大数据最全每天一道大厂SQL题【Day31】腾讯QQ(二)按类别统计QQ号相关信息，大数据开发阿里等大厂面试题汇总

最新推荐文章于 2024-06-13 10:01:09 发布

2401_84182507

最新推荐文章于 2024-06-13 10:01:09 发布

阅读量437

点赞数 6

分类专栏：程序员文章标签：大数据面试学习

本文链接：https://blog.csdn.net/2401_84182507/article/details/138819536

版权

程序员专栏收录该内容

131 篇文章 0 订阅

订阅专栏

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化资料的朋友，可以戳这里获取

一个人可以走的很快，但一群人才能走的更远！不论你是正从事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		日期
qq	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;