数据分析师对sql的要求,数据库分析师有前途吗

首页 > 教育培训 > 作者:YD1662023-05-03 06:01:04

数据导入的代码如下:

DROP TABLE IF EXISTS login_info; CREATE TABLE login_info( user_id VARCHAR(8), login_time DATE ) ENGINE = InnoDB DEFAULT CHARSET = utf8; INSERT INTO login_info (user_id,login_time) VALUE ('a001','2021-01-01') ,('b001','2021-01-01') ,('a001','2021-01-03') ,('a001','2021-01-06') ,('a001','2021-01-07') ,('b001','2021-01-07') ,('a001','2021-01-08') ,('a001','2021-01-09') ,('b001','2021-01-09') ,('b001','2021-01-10') ,('b001','2021-01-15') ,('a001','2021-01-16') ,('a001','2021-01-18') ,('a001','2021-01-19') ,('b001','2021-01-20') ,('a001','2021-01-23');

问题:计算每个用户登录日期间隔小于5天的次数。

输出内容包括:

结果样例如下图所示。

数据分析师对sql的要求,数据库分析师有前途吗(5)

可供参考的解题思路:本题考查LEAD()函数在处理时间间隔问题上的使用方法,观察内层的查询部分,使用LEAD()函数在原有的login_time字段的基础上创造一列新的时间字段(即该用户下一次登录日期),内层查询代码如下:

SELECT user_id
,login_time
,LEAD(login_time,1) OVER (PARTITION BY user_id ORDER BY login_time) AS next_login_time
FROM login_info;

查询结果如下图所示。

数据分析师对sql的要求,数据库分析师有前途吗(6)

在上图中可以发现,经过LEAD()函数处理后,数据会根据user_id字段分组后按照login_time字段排序。经过内层的处理后,只需在外层筛选出next_login_time与login_time字段的日期差小于5天的数据,即最终统计的目标数据,这里使用了TIMESTAMPDIFF(DAY, login_time, next_login_time)计算日期差,最后分组聚合统计不同user_id的记录个数,即每个用户登录日期间隔小于5天的次数。

涉及知识点:窗口函数、子查询、分组聚合、时间函数。

本题的SQL代码如下,供读者参考:

SELECT a.user_id ,COUNT(*) AS num FROM ( SELECT user_id ,login_time ,LEAD(login_time,1) OVER (PARTITION BY user_id ORDER BY login_time) AS next_login_time FROM login_info ) AS a WHERE TIMESTAMPDIFF(DAY, login_time, next_login_time) < 5 GROUP BY user_id;

题目3:用户购买渠道分析

现有一张用户购买信息表purchase_channel,该表记录了用户在某购物平台的购物信息,该购物平台具有网页端(web)和手机端(app)两种访问方式,表中包含如下4个字段。

purchase_channel表的数据如下表所示。

数据分析师对sql的要求,数据库分析师有前途吗(7)

数据导入代码如下:

DROP TABLE IF EXISTS purchase_channel; CREATE TABLE purchase_channel( user_id VARCHAR(8), channel VARCHAR(8), purchase_date DATE, purchase_amount INT(8) ) ENGINE = InnoDB DEFAULT CHARSET = utf8; INSERT INTO purchase_channel (user_id,channel,purchase_date,purchase_amount) VALUE ('a001','app','2021-03-14',200) ,('a001','web','2021-03-14',100) ,('a002','app','2021-03-14',400) ,('a001','web','2021-03-15',3000) ,('a002','app','2021-03-15',900) ,('a003','app','2021-03-15',1000);

问题:查询每天仅使用手机端的用户、仅使用网页端的用户和同时使用网页端和手机端(both)的不同用户人数和总购物金额,并且即使某天某渠道没有用户的购买信息,也需要展示。

输出内容包括:

purchase_date(日期)

channel(购买渠道)

sum_amount(总购买金额)

total_users(不同用户人数)

结果样例如下图所示。

数据分析师对sql的要求,数据库分析师有前途吗(8)

上一页1234下一页

栏目热文

文档排行

本站推荐

Copyright © 2018 - 2021 www.yd166.com., All Rights Reserved.