• Python【数据分析第二阶段测试】

    Python【数据分析第二阶段测试】

    第一部分：SQL查询

    1. 数据库中有如下所示的表1（tb_user）和表2（tb_recharge），其中表2包含了2021年一季度的用户充值数据。

      表1：tb_user。

      user_iduser_name
      1张三
      2李四
      3王五
      4赵六
      5周七

      表2：tb_recharge。

      pay_monthuser_idpay_amount
      一月11000
      一月32000
      一月13000
      二月5800
      二月3900
      三月3700
      三月4600
      三月5500
      三月5400

      • 查询在2021年一季度充值最多的用户姓名。（5分）

        select t1.user_name
 from tb_user as t1 natural join tb_recharge as t2
 where t2.pay_amount in 
	(select max(pay_amount) 
    from tb_recharge);

        • 1
        • 2
        • 3
        • 4
        • 5

      • 查询在2021年一季度没有充值的用户姓名。（5分）

        select user_name
 from tb_user
 where user_id not in 
 (select user_id from tb_recharge);

        • 1
        • 2
        • 3
        • 4

      • 查看在2021年一季度连续三个月有充值行为的用户姓名。（5分）

        select t1.user_name
 from tb_user as t1 natural join tb_recharge as t2
group by t1.user_id
having count(distinct t2.pay_month) = 3;

        • 1
        • 2
        • 3
        • 4

      • 查询每个用户在2021年一月、二月、三月的充值金额，如下表所示。（5分）

        姓名一月二月三月
        张三400000
        李四000
        王五2000900700
        赵六00600
        周七0800900
        select t1.user_name as '姓名',
	sum(case t2.pay_month when '一月' then t2.pay_amount else 0 end) as '一月',
	sum(case t2.pay_month when '二月' then t2.pay_amount else 0 end) as '二月',
	sum(case t2.pay_month when '三月' then t2.pay_amount else 0 end) as '三月'
 from tb_user as t1 natural join tb_recharge as t2
 group by user_id;

        • 1
        • 2
        • 3
        • 4
        • 5
        • 6

    2. 数据库中有如下所示的“用户登录日志表”（表中仅展示了部分数据以供参考），其中，log_id是自动编号（bigint类型），user_id是用户编号（varchar类型），login_date是用户的登录日期（date类型），用户每登录一次，该表中就有一条对应的记录，每天至少有一个用户有登录行为，请完成下面的查询。

      表：tb_login_log

      log_iduser_idlogin_date
      1C100002021-5-20
      2C100012021-5-20
      3C100002021-5-20
      4C100022021-5-20
      5C100002021-5-20

      • 查询单日登录次数超过3次的用户。（10分）

        select user_id 
from tb_login_log 
group by user_id,login_date
having count(*) >3;

        • 1
        • 2
        • 3
        • 4

      • 查询2021年12月每一天的活跃用户数。（10分）

        select login_date,
	count(distinct user_id) as '活跃用户数'
from tb_login_log
where login_date >='2021-12-01' and login_date <='2021-12-31'
group by login_date;

        • 1
        • 2
        • 3
        • 4
        • 5

    第二部分：Pandas的应用

    1. 有如下所示的建库建表SQL语句，请在MySQL中完成数据库和表的创建，然后解决后续的问题。

      create database my_exam default charset utf8mb4;

use my_exam;

create table tb_product
(
prod_id varchar(50) not null comment '商品号',
category varchar(50) not null comment '种类',
price integer not null comment '价格',
primary key (prod_id)
) engine=innodb comment='产品表';

insert into tb_product values 
	('prodA', 'cateA', 100),
    ('prodB', 'cateB', 200),
    ('prodC', 'cateC', 300),
    ('prodD', 'cateD', 400);

create table tb_order
(
id integer not null auto_increment,
order_no varchar(20) not null comment '订单号',
user_id varchar(50) not null comment '用户号',
order_date date not null comment '下单日期',
store varchar(50) not null comment '店铺号',
product varchar(50) not null comment '商品号',
quantity integer not null comment '购买数量',
primary key (id)
) engine=innodb comment='订单表';

insert into tb_order 
	(order_no, user_id, order_date, store, product, quantity) 
values 
	('D001', 'customerA', '2018-01-01', 'storeA', 'prodA', 1),
    ('D001', 'customerA', '2018-01-01', 'storeA', 'prodB', 1),
    ('D001', 'customerA', '2018-01-01', 'storeA', 'prodC', 1),
    ('D002', 'customerB', '2018-01-12', 'storeB', 'prodB', 1),
    ('D002', 'customerB', '2018-01-12', 'storeB', 'prodD', 1),
    ('D003', 'customerC', '2018-01-12', 'storeC', 'prodB', 1),
    ('D003', 'customerC', '2018-01-12', 'storeC', 'prodC', 1),
    ('D003', 'customerC', '2018-01-12', 'storeC', 'prodD', 1),
    ('D004', 'customerA', '2018-01-01', 'storeD', 'prodD', 2),
    ('D005', 'customerB', '2018-01-23', 'storeB', 'prodA', 1);
    
create table tb_store
(
store_id varchar(50) not null comment '店铺号',
city varchar(20) not null comment '城市',
primary key (store_id)
) engine=innodb comment='店铺表';

insert into tb_store values 
	('storeA', 'cityA'),
    ('storeB', 'cityA'),
    ('storeC', 'cityB'),
    ('storeD', 'cityC'),
    ('storeE', 'cityD'),
    ('storeF', 'cityB');

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
      • 13
      • 14
      • 15
      • 16
      • 17
      • 18
      • 19
      • 20
      • 21
      • 22
      • 23
      • 24
      • 25
      • 26
      • 27
      • 28
      • 29
      • 30
      • 31
      • 32
      • 33
      • 34
      • 35
      • 36
      • 37
      • 38
      • 39
      • 40
      • 41
      • 42
      • 43
      • 44
      • 45
      • 46
      • 47
      • 48
      • 49
      • 50
      • 51
      • 52
      • 53
      • 54
      • 55
      • 56
      • 57
      • 58

      • 从数据库中读取三张表（产品表、订单表、店铺表），分别命名为product_df、order_df和store_df。（5分）

        import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

plt.rcParams['font.sans-serif'].insert(0, 'SimHei')
plt.rcParams['axes.unicode_minus'] = False
import pymysql
conn = pymysql.connect(
                        host='localhost',port=3307,
                        user='guest',password='zhang.123456',
                        database = 'my_exam',charset='utf8mb4')
product_df = pd.read_sql('select * from tb_product',conn,index_col="prod_id")
order_df = pd.read_sql('select * from tb_order',conn,index_col="order_no")
store_df = pd.read_sql('select * from tb_store',conn,index_col="store_id")

        • 1
        • 2
        • 3
        • 4
        • 5
        • 6
        • 7
        • 8
        • 9
        • 10
        • 11
        • 12
        • 13
        • 14

      • 连接product_df和order_df两张表并将其命名为order_product_df。（5分）

        order_product_df = order_df.merge(product_df,left_on = "product",right_on="prod_id",how = "left")

        • 1

      • 统计购买总金额不低于800的用户的总购买金额，总订单数和总购买商品数，订单号相同的算作一单。（10分）

        order_product_df["orderAmount"]= order_product_df.price * order_product_df.quantity
temp1 = pd.pivot_table(
order_product_df,
    index = "user_id",
    values = ["orderAmount","quantity","order_no"],
    aggfunc = {
    "orderAmount":"sum",
        "quantity":"sum",
        "order_no":"nunique"
    }
).reindex(columns = ["orderAmount","quantity","order_no"]
).rename(
columns = {
    "orderAmount":"总购买金额",
    "quantity":"总购买商品数",
    "order_no":"总订单数"    
}
)
temp1 = temp1[temp1["总购买金额"]>=800]
temp1

        • 1
        • 2
        • 3
        • 4
        • 5
        • 6
        • 7
        • 8
        • 9
        • 10
        • 11
        • 12
        • 13
        • 14
        • 15
        • 16
        • 17
        • 18
        • 19
        • 20

      • 统计所有城市（包含无购买记录的城市）的总店铺数，总购买人数和总购买金额。（10分）

        order_product_store_df= store_df.merge(order_product_df,left_on = "store_id",right_on ="store",how = "left")
temp2 = pd.pivot_table(
order_product_store_df,
  index = "city",
    values = ("store_id","user_id","orderAmount"),
    aggfunc = {
    "store_id":"nunique",
    "user_id":"nunique",
        "orderAmount":"sum"
    }
).rename(columns = {
"store_id":"总店铺数",
 "user_id":"总购买人数",
   "orderAmount":"总购买金额" 
})
temp2

        • 1
        • 2
        • 3
        • 4
        • 5
        • 6
        • 7
        • 8
        • 9
        • 10
        • 11
        • 12
        • 13
        • 14
        • 15
        • 16

      • 统计购买过"cateA"产品的用户和他们的平均订单金额，订单号相同的算作一单。（10分）

        cateA=order_product_df[order_product_df["category"] == "cateA"]
temp3 = pd.pivot_table(
cateA,
   index =  "user_id",
    values = ("order_no","orderAmount"),
    aggfunc = {
    "order_no":"nunique",
        "orderAmount":"sum"
    }
).rename(columns = {
    "order_no":"总订单数",
   "orderAmount":"总购买金额" 
})
temp3['平均订单金额'] = temp3["总购买金额"]/temp3["总订单数"]


        • 1
        • 2
        • 3
        • 4
        • 5
        • 6
        • 7
        • 8
        • 9
        • 10
        • 11
        • 12
        • 13
        • 14
        • 15

    第三部分：分析思维和分析工具

    1. 产品发布新版本后，DAU出现严重下滑，作为数据分析师你会如何分析下滑原因。（5分）

      答：DAU是日活用户数，在发生DAU数据严重下滑时，首先查看异常数据的真实性，在BI看板上看到的数据去和数据库或者是数仓或者EXCEL多方进行检验，确定异常数据的真实性；其次，对周期性进行衡量判断，数据是否具有周期性规律，如周，月，季度，年度等规律而产生的异常；第三，外部数据的情况，是否是竞争对手，行业信息，政策变动等影响了指标异常；第四，进行指标拆解，将大指标层层拆解，定位异常问题；最后，监控效果，跟踪和监控更新的效果，发现改进的地方并持续更新。

      • 1

    2. 写出5个你曾经使用过的DAX函数并说明其用法。（5分）

      答：1.Filter函数：筛选数据,从下个集合中拿出一个子集;
2.Calculate函数：筛选器修改上下文中的表达式;
3.Switch函数：根据值列表计算表达式；
4.Related函数：从另一个表返回一个相关值；
5.sum函数：求和；

      • 1
      • 2
      • 3
      • 4
      • 5

    3. 请说明你用过哪些Tableau中的快速表计算功能，并说明应用场景。（5分）

      答：
1.建立北极星指标时，需要求出销售额时，进行‘汇总’；
2.使用到快速表计算功能的‘计数'选项可以查看每个类别中的数量;
3.可以使用‘百分比差异’来计算每个类别所占的百分比；
4.分析排名时，可以使用‘排序’；
5.观察二八分布的时候可以使用‘合计百分比’；


      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7

    4. 根据你现在的理解，说一下数据分析师这个岗位的日常工作和现实意义。（5分）

      答：数据分析师日常工作包括数据收集、清理、分析和报告。主要负责业务部门的常规数据支撑工作，通过数据分析，针对业务工作中的薄弱环节和漏洞，做出分析报告并提出高校且可以落地的方案，资深的数据分析师可以透过数据发掘潜在的价值，为企业提供洞察，用专业知识和技能来帮助管理、发现和利用业务数据，实现更高的效率和业务增长。

      • 1
  • 相关阅读:
    JS逆向案例：破解登录密码
    C/C++：[Error] ld returned 1 exit status 解决方案
    Linux的指令和用途（持续更新）
    Vue3常用知识点总结
    元数据简析：定义及管理
    存储介质中ext4的文件排布
    关于安卓artifactory本地仓库搭建
    (转)冒泡排序及优化详解
    Java(十二)---认识异常
    你可能不太了解的前端知识
  • 原文地址：https://blog.csdn.net/weixin_47005749/article/details/130897869