实战案例:
重点掌握如何细化分析目标以及如何拆解案例的问题
学习目标:
掌握复杂问题逐步拆解的思维和能力,让解题思路有迹可循
模拟真实工作案例,让数据分析真正落地、有效
分析目的:
- 了解合作的供应商产品的销售情况
- 提高采购效率,增进与大供应商的合作关系
- 增加公司销售额与利润
分析指标:
- 供应商的商品总营业额
- 总销售额越高,供应商的规模越大
问题驱动:找出总销售额最高的10个供应商信息
分析目标细化:
- 最终返回结果:供应商信息
- 过滤数据:供应商总销售额
- 过滤条件:销售额最高的10个
案例拆解
- 选取最终结果(SELECT)- 供应商信息
- 过滤数据- 供应商总金额计算
- 单个产品总销售额 = 产品订单记录的消费之和(GROUP BY / SUM)
- 供应商销售额 = 供应商提供的总销售额之和(GROUP BY / SUM)
- 过滤条件(ORDER BY / LIMIT)- 销售额最高的10个
数据定位:
- 提供各个供应商详细信息的供应商信息表
- 提供产品与供应商之间联系的产品信息表
- 计算出各个产品总销售额的订单信息表
深刻理解每一个表以及表与表之间的业务关系内涵:
比如说,订单明细表表征的是每一个订单中,各类产品的销售记录明细,因此可以通过产品号码聚类,用来计算每样产品的总销售额。
明确一个逻辑:
每个供应商能够提供多个产品,或者说,不同产品可能来自同一个供应商;每一条订单记录可能包括多个订单明细记录;所以要得到过滤数据:供应商总销售额,先得分别得到每个产品的总销售额,然后得到每个供应商的总销售额。然后再做排序筛选,连接表操作,得到前10总销售额的供应商信息。
分析目标:列出总销售额最高的10个供应商。
详细解答步骤:
- 计算每样产品的总销售额 —— `订单明细``表:GROUP BY / SUM
SELECT
`产品号码`,
SUM( `产品单价` * `购买数量` ) AS `产品销售额`
FROM `订单明细`
GROUP BY `产品号码`
- 为每一个供应商计算历史总销售额,获得前10的供应商信息
SELECT
a.`供应商号码`,
SUM( `产品销售额` ) AS `供应商销售额`
FROM `产品信息` AS a
INNER JOIN (
SELECT
`产品号码`,
SUM( `产品单价` * `购买数量` ) AS `产品销售额`
FROM `订单明细`
GROUP BY `产品号码` ) AS b
ON a.`产品号码` = b.`产品号码`
GROUP BY
a.`供应商号码`
ORDER BY
`供应商销售额` DESC
- 提供历史总销售额前10的供应商号码,公司、以及总销售额作为最终的分析结果。
# V2 优化版本
SELECT
c.`供应商号码`,
d.`公司`,
c.`供应商销售额`
FROM `供应商信息` AS d
INNER JOIN
(
SELECT
a.`供应商号码`,
SUM( `产品销售额` ) AS `供应商销售额`
FROM `产品信息` AS a
INNER JOIN
(
SELECT
`产品号码`,
SUM( `产品单价` * `购买数量` ) AS `产品销售额`
FROM `订单明细`
GROUP BY `产品号码` )
AS b
ON a.`产品号码` = b.`产品号码`
GROUP BY a.`供应商号码`
ORDER BY `供应商销售额` DESC
LIMIT 10
) AS c
ON c.`供应商号码` = d.`供应商号码`
# 子查询只返回需要的字段;改变连接的逻辑顺序,代码变短;
# JOIN语句改成 LEFT JOIN
—— ——— —— ——— —— ——— ———— ———— ———— —— —— —— ——— ——— ——— ——— —————
SQL代码实现:
# V1 版本
SELECT
d.`供应商号码`,
e.`公司`,
d.`供应商销售额`
FROM
(
SELECT
`供应商号码`,
SUM( `产品销售额` ) AS `供应商销售额`
FROM
(
SELECT
a.`产品号码`,
b.`供应商号码`,
a.`产品销售额`
FROM
(
SELECT `产品号码`,
SUM( `产品单价` * `购买数量` ) AS `产品销售额`
FROM `订单明细`
GROUP BY `产品号码` ) AS a
LEFT JOIN `产品信息` AS b
ON a.`产品号码` = b.`产品号码`
) AS c
GROUP BY `供应商号码`
ORDER BY `供应商销售额` DESC
LIMIT 10
) AS d
LEFT JOIN `供应商信息` AS e
ON d.`供应商号码` = e.`供应商号码`
分析结果可视化: