三个案例具体数据分析:
供应商营业额分析、网店销售趋势分析、区域销售分析
1、供应商营业额分析
eg.列出总销售额最高的10个供应商信息:
分析:案例拆解/数据定位
- 选取最终结果---供应商信息【supplier_info】
- 过滤数据---供应商总销售额计算:
单个产品总销售额=产品订单记录的消费记录之和(GROUP BY/SUM);【order_details】
供应商销售额=供应商提供的产品总销售额之和(GROUP BY/SUM)。【product_info】
- 过滤条件---销售额最高10个(ORDER BY / LIMIT)
第一步:计算每样产品的总销售额
SELECT
product_id,
SUM(sales_count * sales_price) AS 'product_total_sales'
FROM
order_details
GROUP BY
product_id;
第二步:为每一个供应商计算历史总销售额,获得总销售额前10的供应商信息
SELECT
supplier_id,
SUM(o.product_total_sales) AS 'supplier_total_sales'
FROM
product_info p
INNER JOIN (
SELECT
product_id,
SUM(sales_count * sales_price) AS 'product_total_sales'
FROM
order_details
GROUP BY
product_id
) AS o ON p.product_id = o.product_id
GROUP BY
supplier_id
ORDER BY
supplier_total_sales DESC
LIMIT 10;
第三步:提供历史销售额前10的供应商号码,公司,以及总销售额作为最终的分析结果
SELECT
s.supplier_id,s.company,op.supplier_total_sales
FROM
supplier_info s
INNER JOIN (
SELECT
supplier_id,
SUM(o.product_total_sales) AS 'supplier_total_sales'
FROM
product_info p
INNER JOIN (
SELECT
product_id,
SUM(sales_count * sales_price) AS 'product_total_sales'
FROM
order_details
GROUP BY
product_id
) AS o ON p.product_id = o.product_id
GROUP BY
supplier_id
ORDER BY
supplier_total_sales DESC
LIMIT 10
) AS op ON s.supplier_id = op.supplier_id;
2、网店销售趋势分析
eg.为每个供应商计算每年的各个季度销售额:
分析:案例拆解/数据定位
- 选取最终结果---供应商信息以及销售额
- 过滤数据---供应商每年各个季度销售额计算:
供应商每年各季度销售额=供应商提供的产品每年各季度总销售额之和(GROUP BY/SUM);【product_info】
单个产品每年各季度总销售额=每年各个季度的产品订单记录的消费记录之和(GROUP BY/SUM);【order_details】
订单记录的年份季度信息=订单日期所属年份以及季度(YEAR/CASE WHEN IF)。【order_info】
第一步:计算每个订单的年份以及季度时间信息
SELECT
order_id,
create_time,
YEAR (create_time) AS 'year',
MONTH (create_time) AS 'month',
CASE
WHEN MONTH (create_time) < 4 THEN 1
WHEN MONTH (create_time) >= 4 AND MONTH (create_time) < 7 THEN 2
WHEN MONTH (create_time) >= 7 AND MONTH (create_time) < 10 THEN 3
ELSE 4
END AS 'quarter'
FROM
order_info
第二步:计算每样产品每年每季度的销售额
SELECT
od.product_id,
oi.`year`,
oi.`quarter`,
SUM(sales_count * sales_price) AS 'product_total_sales'
FROM
order_details od
INNER JOIN (
SELECT
order_id,
create_time,
YEAR (create_time) AS 'year',
MONTH (create_time) AS 'month',
CASE
WHEN MONTH (create_time) < 4 THEN 1
WHEN MONTH (create_time) >= 4 AND MONTH (create_time) < 7 THEN 2
WHEN MONTH (create_time) >= 7 AND MONTH (create_time) < 10 THEN 3
ELSE 4
END AS 'quarter'
FROM
order_info
) AS oi ON oi.order_id = od.order_id
GROUP BY
od.product_id,
oi.`year`,
oi.`quarter`;
第三步:计算每位供应商每年每季度的总销售额
SELECT
p.supplier_id,
d.`year`,
d.`quarter`,
SUM(d.product_total_sales) AS 'supplier_total_sales'
FROM
product_info p
INNER JOIN (
SELECT
od.product_id,
oi.`year`,
oi.`quarter`,
SUM(sales_count * sales_price) AS 'product_total_sales'
FROM
order_details od
INNER JOIN (
SELECT
order_id,
create_time,
YEAR (create_time) AS 'year',
MONTH (create_time) AS 'month',
CASE
WHEN MONTH (create_time) < 4 THEN 1
WHEN MONTH (create_time) >= 4 AND MONTH (create_time) < 7 THEN 2
WHEN MONTH (create_time) >= 7 AND MONTH (create_time) < 10 THEN 3
ELSE 4
END AS 'quarter'
FROM
order_info
) AS oi ON oi.order_id = od.order_id
GROUP BY
od.product_id,
oi.`year`,
oi.`quarter`
) AS d ON d.product_id = p.product_id
GROUP BY
p.supplier_id,
d.`year`,
d.`quarter`;
第四步:提供供应商号码,公司,年份,季度,总销售额作为最终结果
SELECT
s.supplier_id,
s.company,
op.`year`,
op.`quarter`,
op.supplier_total_sales
FROM
supplier_info s
INNER JOIN (
SELECT
p.supplier_id,
d.`year`,
d.`quarter`,
SUM(d.product_total_sales) AS 'supplier_total_sales'
FROM
product_info p
INNER JOIN (
SELECT
od.product_id,
oi.`year`,
oi.`quarter`,
SUM(sales_count * sales_price) AS 'product_total_sales'
FROM
order_details od
INNER JOIN (
SELECT
order_id,
create_time,
YEAR (create_time) AS 'year',
MONTH (create_time) AS 'month',
CASE
WHEN MONTH (create_time) < 4 THEN 1
WHEN MONTH (create_time) >= 4 AND MONTH (create_time) < 7 THEN 2
WHEN MONTH (create_time) >= 7 AND MONTH (create_time) < 10 THEN 3
ELSE 4
END AS 'quarter'
FROM
order_info
) AS oi ON oi.order_id = od.order_id
GROUP BY
od.product_id,
oi.`year`,
oi.`quarter`
) AS d ON d.product_id = p.product_id
GROUP BY
p.supplier_id,
d.`year`,
d.`quarter`
) AS op ON op.supplier_id=s.supplier_id;
3、区域销售分析
eg.调取各年度、各季度的销售冠军的省份及其销售数据;
列出销售冠军省份(province),销售年份(year),销售季度(quarter),总销售额(total_sales),总完成订单数(order_count),平均每个订单销售额(avg_payment)。
分析目标拆解:
- 最终返回结果:销售冠军省份,年份,季度,总销售额,总订单数,平均每个订单销售额【总销售额/总订单数】
- 过滤数据:各年份各季度各省份销售额
- 计算各年度各季度的省份信息
- 计算各年度各季度各省份的总销售额,总订单数
- 计算各年度各季度各省份的总销售额冠军,总订单数,平均每个订单销售额
-- 第一步:计算各年度各季度的省份信息
SELECT
oi.order_id,
oi.payment_amount,
oi.customer_id,
ci.city,
ci.province,
YEAR(oi.create_time) AS 'years',
CASE
WHEN MONTH (create_time) < 4 THEN 1
WHEN MONTH (create_time) >= 4 AND MONTH (create_time) < 7 THEN 2
WHEN MONTH (create_time) >= 7 AND MONTH (create_time) < 10 THEN 3
ELSE 4
END AS 'quarter'
FROM
order_info oi
INNER JOIN customer_info ci ON oi.customer_id = ci.customer_id;
-- 第二步:计算各年份各季度各省份的总销售额,订单数量
SELECT
ci.province,
SUM(oi.payment_amount) AS 'year_quarter_sales',
COUNT(oi.order_id) AS 'year_quarter_count',
YEAR(oi.create_time) AS 'years',
CASE
WHEN MONTH (oi.create_time) < 4 THEN 1
WHEN MONTH (oi.create_time) >= 4 AND MONTH (oi.create_time) < 7 THEN 2
WHEN MONTH (oi.create_time) >= 7 AND MONTH (oi.create_time) < 10 THEN 3
ELSE 4
END AS 'quarter'
FROM
order_info oi
INNER JOIN customer_info ci ON oi.customer_id = ci.customer_id
GROUP BY ci.province,years,`quarter`
ORDER BY year_quarter_sales DESC;
-- 第三步计算各年度各季度各省的总销售额冠军
SELECT
r.province,
r.years,
r.`quarter`,
MAX(r.year_quarter_sales) AS 'total_sales',
r.total_count,
MAX(r.year_quarter_sales)/r.total_count AS 'avg_payment'
FROM
(
SELECT
ci.province,
SUM(oi.payment_amount) AS 'year_quarter_sales',
COUNT(oi.order_id) AS 'total_count',
YEAR(oi.create_time) AS 'years',
CASE
WHEN MONTH (oi.create_time) < 4 THEN 1
WHEN MONTH (oi.create_time) >= 4 AND MONTH (oi.create_time) < 7 THEN 2
WHEN MONTH (oi.create_time) >= 7 AND MONTH (oi.create_time) < 10 THEN 3
ELSE 4
END AS 'quarter'
FROM
order_info oi
INNER JOIN customer_info ci ON oi.customer_id = ci.customer_id
GROUP BY ci.province,years,`quarter`
ORDER BY year_quarter_sales DESC
) AS r
GROUP BY r.years,r.`quarter`
ORDER BY r.years;