SQL在数据分析中的应用案例（二）

三个案例具体数据分析：

供应商营业额分析、网店销售趋势分析、区域销售分析

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)。

---

分析目标拆解：

• 最终返回结果：销售冠军省份，年份，季度，总销售额，总订单数，平均每个订单销售额【总销售额/总订单数】
• 过滤数据：各年份各季度各省份销售额

1. 计算各年度各季度的省份信息
2. 计算各年度各季度各省份的总销售额，总订单数
3. 计算各年度各季度各省份的总销售额冠军，总订单数，平均每个订单销售额

---

 

-- 第一步：计算各年度各季度的省份信息
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;