每天一道大厂SQL题【Day31】腾讯QQ(二)按类别统计QQ号相关信息(1)

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

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

	- [思路分析](#_26)
	- [附表](#_45)
+ [答案获取](#_122)
+ [加技术群讨论](#_129)
+ - [文末SQL小技巧](#SQL_137)
+ [后记](#_149)

每天一道大厂SQL题【Day31】腾讯QQ(二)按类别统计QQ号相关信息

大家好，我是Maynor。相信大家和我一样，都有一个大厂梦，作为一名资深大数据选手，深知SQL重要性，接下来我准备用100天时间，基于大数据岗面试中的经典SQL题，以每日1题的形式，带你过一遍热门SQL题并给出恰如其分的解答。

一路走来，随着问题加深，发现不会的也愈来愈多。但底气着实足了不少，相信不少朋友和我一样，日积月累才是最有效的学习方式！

每日语录

学习！还是他娘地学习！

第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', '女', '四川省');


![img](https://img-blog.csdnimg.cn/img_convert/ba81546092effacca8f1a784690fc231.png)
![img](https://img-blog.csdnimg.cn/img_convert/89ac8f3b768e72e0a5a0b1a53e48544e.png)
![img](https://img-blog.csdnimg.cn/img_convert/dccff83ccd901d139c8e91328140aff6.png)

**既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！**

**由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新**

**[需要这份系统化资料的朋友，可以戳这里获取](https://bbs.csdn.net/topics/618545628)**

**

**由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新**

**[需要这份系统化资料的朋友，可以戳这里获取](https://bbs.csdn.net/topics/618545628)**