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

第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中数据如下：

table_act表结构如表格3

table_act表数据包含数据如表格4

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需自行修改。

加技术群讨论

点击下方卡片关注 联系我进群

或者直接私信我进群

文末SQL小技巧

提高SQL功底的思路。

1、造数据。因为有数据支撑，会方便我们根据数据结果去不断调整SQL的写法。

造数据语法既可以create table再insert into，也可以用下面的create temporary view xx as values语句，更简单。

其中create temporary view xx as values语句，SparkSQL语法支持，hive不支持。

2、先将结果表画出来，包括结果字段名有哪些，数据量也画几条。这是分析他要什么。

从源表到结果表，一路可能要走多个步骤，其实就是可能需要多个子查询，过程多就用with as来重构提高可读性。

3、要由简单过度到复杂，不要一下子就写一个很复杂的。

先写简单的select from table…,每个中间步骤都执行打印结果，看是否符合预期， 根据中间结果，进一步调整修饰SQL语句，再执行，直到接近结果表。

4、数据量要小，工具要快，如果用hive，就设置set hive.exec.mode.local.auto=true;如果是SparkSQL，就设置合适的shuffle并行度，set spark.sql.shuffle.partitions=4;