MySQL实战案例练习

案例1. /*Columns of Table members: member_id bigint(255) PK 会员id gender varchar(255) 性别 age int(255) 年龄 city varchar(255) 所在城市 name varchar(255) 姓名 phone bigint(255) 手机号码 registration_date date 注册日期 Columns of Table product: Product_ID varchar(255) PK MySQL实战案例练习主要涉及到数据库设计、SQL查询以及数据分析的基础概念。在这个案例中，我们有四个主要的表格：members、product、push和sales，每个表格都包含不同类型的字段，用于存储不同种类的数据。 members表格用于存储会员信息，字段包括：member_id（会员ID，主键，类型为bigint）、gender（性别，varchar类型）、age（年龄，int类型）、city（所在城市，varchar类型）、name（姓名，varchar类型）、phone（手机号码，bigint类型）以及registration_date（注册日期，date类型）。 product表格存储产品信息，字段有：Product_ID（产品ID，主键，varchar类型）、Base_Category（产品大类，varchar类型）、Category（产品小类，varchar类型）和Price（产品价格，float类型）。 push表格记录推送活动，字段包括：camp_id（推送活动ID，主键，bigint类型）、camp_name（推送活动名称，varchar类型）、drop_dt（推送日期，date类型）和Cost（推送成本，float类型）。 push_detail表格则记录了推送活动的详细情况，如：promo_id（推送ID，bigint类型）、member_id（会员ID，bigint类型）、camp_id（推送活动ID，bigint类型）、read_date（阅读日期，date类型）、read_flag（是否阅读，varchar类型）、like_date（点赞日期，date类型）和like_flag（是否点赞，varchar类型）。 sales表格记录了交易记录，字段有：transaction_id（交易记录ID，主键，bigint类型）、member_id（会员ID，bigint类型）、product_id（产品ID，varchar类型）、shop_name（门店名称，varchar类型）、amount（交易金额，float类型）、transaction_date（交易日期，date类型）和quant（交易数量，int类型）。 问题在于，我们需要找出哪些微信推送活动带来了良好的销售业绩。分析思路分为以下几个步骤： 1. 明确业务目标：通过微信推送增加活动曝光，促进购买。 2. 定义关键指标：定义“微信推送带来的销售业绩”为用户在阅读推送后24小时内产生的购买行为，即sales.transaction_date < push_detail.read_date + 1。 3. 设计分析方法：通过连接push、push_detail和sales表格，筛选符合条件的交易记录，然后按camp_id分组，并计算ROI（投资回报率），ROI定义为销售额除以推送活动成本。 4. 编写SQL查询：使用SELECT语句，连接相关表格，计算ROI并按值降序排列，展示camp_id及其对应的ROI。 示例的SQL查询如下： ```sql USE xiaozao; SELECT p.camp_id, SUM(s.amount) / p.Cost AS ROI FROM push_detail pd JOIN sales s ON pd.member_id = s.member_id JOIN push p ON p.camp_id = pd.camp_id WHERE s.transaction_date < pd.read_date + 1 GROUP BY p.camp_id ORDER BY ROI DESC; ``` 案例2的sony_members表格与sony_product表格提供了额外的会员和产品信息，包括会员积分、会员等级、注册渠道等，可以用于更深入的会员行为分析和产品销售分析。 通过这些案例，我们可以学习到如何使用SQL进行数据连接、筛选、分组和计算，以及如何根据业务需求设计分析策略。这对于理解和提升在实际工作中的MySQL操作和数据分析能力具有很大的帮助。