业务背景
索尼公司是源自日本的跨国综合企业,以研制电子产品为主要事业,经营领域横跨消费电子产品、专业性电子产品、电子游戏、金融、娱乐等,拥有全世界的品牌知名度。目前在国内,索尼的产品主要覆盖在照相机,摄像机,电视,和智能手机四个主要领域。
My Sony会员,共分为注册会员、铜牌会员、银牌会员、金牌会员4个等级。会员的等级由"成长值"数值决定,成长值越高,会员等级越高。加入索尼会员可以享受会员尊享价,官方延保以及累积成长等多个激励。用户可以通过索尼中国在线商城,索尼产品,索尼app,微信等渠道注册。
你的任务,是将数据导入SQL后,回答下列三个问题:
- 所提供数据中,销售最好的产品分别是哪些?
- 会员和非会员在购买金额和产品类别方面,有什么主要区别?
- 不同注册渠道的会员,在购买金额和产品类别方面有什么主要区别?
提交形式: word形式提交,每一题提交你的代码+结果截图,以及你的业务结论即可。
数据查看和导入
查看数据,然后新建数据库,将数据表导入到Navicate:
用记事本打开要导入的要导入的.sql格式的库(因为不同sql文件导入的方式有所不同 )
需要手动创建一个数据库 ↓
将.sql格式数据导入到naicate(不建议使用复制方法生成表)
五张表完美导入↓
熟悉数据
第一张是销售表(sales),记录了用户产生购买行为的关键数据
包括会员ID、产品ID、交易金额、交易时间等
第二张是会员表(members),记录了注册会员的个人信息
包括了性别、年龄、手机号码、会员等级、个人婚姻状况等
第三张是产品表(product),记录了和产品相关的一系列数据
包括了产品大类、产品小类以及产品单价
解答业务问题:
问题一 :所提供数据中,销售最好的产品分别是哪些?
案例拆解:
- 最终返回结果:产品信息以及销售额
- 聚合类别:产品
- 过滤条件:销售额最高
数据定位:产品表 & 销售表
# 问题一:所提供数据中,销售最好的产品分别是哪些?
SELECT
a.`product_id`,
b.Category,
b.Base_Category,
`max_amount`
FROM
(
SELECT
product_id,
SUM(`amount`) AS `max_amount`
FROM sony_sales
GROUP BY `product_id`
) AS a
INNER JOIN
sony_product AS b
ON a.product_id = b.Product_ID
ORDER BY `max_amount` DESC
业务结论:在所有产品中,销售总额最高的是TV-Z9G series.
问题二:会员和非会员在购买金额和产品类别方面,有什么主要区别?
# 问题二:会员与非会员在购买金额和产品类别方面,有什么区别?
# 目标字段:是否会员,购买金额(amount)、产品类别
# 相关表:销售表、会员表、订单表(表间关系)
# 连接字段:会员ID(member_id)、产品ID(product_id)
# 创建新表
CREATE TABLE `mem_sales` as SELECT
s.transaction_id,
s.member_id,
CASE
WHEN m.member_id IS NOT NULL THEN 'Y'
ELSE 'N'
END AS member_flag,
s.product_id,
p.Base_Category,
p.Category,
s.quant,
s.amount
FROM sony_sales AS s
LEFT JOIN sony_members AS m
ON s.member_id = m.member_id
INNER JOIN sony_product AS p
ON s.product_id = p.product_id
# 预览表
SELECT * FROM `mem_sales`;
# 会员非会员在购买金额 —— 人均销售额和销售量
SELECT
member_flag,
AVG(quant) AS avg_quant,
AVG(amount) AS avg_mount
FROM mem_sales
GROUP BY member_flag;
# 会员非会员产品类别的区别(确实感觉一时半会没明白业务问题的含义)
SELECT
member_flag,
Category,
Base_Category,
AVG(quant),
AVG(amount)
FROM mem_sales
GROUP BY member_flag,Category,Base_Category
ORDER BY AVG(amount) DESC;
问题三:不同注册渠道的会员,在购买金额和产品类别方面有什么主要区别?
# 问题三:不同注册渠道的会员,在购买金额和产品类别方面有什么主要区别?
# 目标字段:注册渠道、购买金额、产品类别
# 需要的表:销售表(sony_sales)、会员表(sony_members)、产品类别(sony_product)
# 连接键:会员ID、产品ID
#
CREATE TABLE channel_sales AS
SELECT
s.transaction_id,
s.member_id,
m.channel,
p.Category,
p.Base_Category,
s.quant,
s.amount
FROM sony_sales AS s
INNER JOIN sony_members AS m
ON s.member_id = m.member_id
INNER JOIN sony_product AS p
ON s.product_id = p.Product_ID
# 查看全部
SELECT * FROM channel_sales
# 不同渠道会员,平均销量和平均金额
SELECT
channel,
AVG(quant) AS avg_quant,
AVG(amount) AS avg_amount,
COUNT(DISTINCT(member_id)) AS count_id
FROM channel_sales
GROUP BY channel
ORDER BY avg_amount DESC
# 不同渠道会员、类别平均销量和平均金额
SELECT
channel,
Category,
Base_Category,
AVG(quant) AS avg_quant,
AVG(amount) AS avg_amount,
COUNT(DISTINCT(member_id)) AS count_id
FROM channel_sales
GROUP BY channel,Category,Base_Category
ORDER BY avg_amount DESC
创建表的操作,其实用 LEFT JOIN 连接也就可以 ↓