【Python实践案例】电商平台数据分析和挖掘 – 电商销售分析实例
1. 部门销售分析
| 部门 | 总销售额 | 平均销售额 | 交易次数 | 预算使用率 |
| 市场部 | 20,000.00 | 10,000.00 | 2 | 4.00% |
| 技术部 | 81,000.00 | 27,000.00 | 3 | 10.12% |
| 销售部 | 30,000.00 | 5,000.00 | 6 | 3.00% |
2. 员工绩效分析
| 姓名 | 部门 | 总销售额 | 平均销售额 | 销售笔数 | 销售/薪资比 |
| 张明 | 销售部 | 17,700.00 | 5,900.00 | 3 | 1.18 |
| 李华 | 销售部 | 12,300.00 | 4,100.00 | 3 | 1.54 |
| 王芳 | 技术部 | 18,000.00 | 18,000.00 | 1 | 1.50 |
| 赵刚 | 技术部 | 63,000.00 | 31,500.00 | 2 | 3.50 |
| 刘洋 | 市场部 | 20,000.00 | 10,000.00 | 2 | 2.22 |
3. 销售趋势分析
最高单日销售额：131,000.00
最低单日销售额：131,000.00
平均日销售额：131,000.00
4. 数据可视化
以下是销售数据的可视化分析：
5. 分析结论
- 各部门销售情况总体表现良好，预算执行率保持在合理水平。
- 员工销售业绩分布相对均衡，团队协作效果显著。
- 销售趋势呈现稳定增长态势，市场反应积极。
SET FOREIGN_KEY_CHECKS = 0;SET NAMES utf8mb4;-- departments DDLCREATE TABLE `departments` (`department_id` INT(10) NOT NULL AUTO_INCREMENT,`department_name` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,`manager_id` INT(10) NULL,`budget` DECIMAL(15,2) NULL,UNIQUE INDEX `department_name`(`department_name` ASC) USING BTREE,PRIMARY KEY (`department_id`)) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci AUTO_INCREMENT = 4 ROW_FORMAT = Dynamic;-- employees DDLCREATE TABLE `employees` (`employee_id` INT(10) NOT NULL,`name` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,`department` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,`position` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,`hire_date` DATE NULL,`salary` DECIMAL(10,2) NULL,PRIMARY KEY (`employee_id`)) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;-- sales DDLCREATE TABLE `sales` (`sale_id` INT(10) NOT NULL AUTO_INCREMENT,`employee_id` INT(10) NULL,`product` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,`amount` DECIMAL(10,2) NULL,`sale_date` DATE NULL,INDEX `employee_id`(`employee_id` ASC) USING BTREE,PRIMARY KEY (`sale_id`)) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci AUTO_INCREMENT = 12 ROW_FORMAT = Dynamic;-- sales IndexesALTER TABLE `sales` ADD CONSTRAINT `sales_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;-- departments DMLINSERT INTO `departments` (`department_id`,`department_name`,`manager_id`,`budget`) VALUES (1,'销售部',101,1000000.00),(2,'技术部',104,800000.00),(3,'市场部',105,500000.00);-- employees DMLINSERT INTO `employees` (`employee_id`,`name`,`department`,`position`,`hire_date`,`salary`) VALUES (101,'张明','销售部','销售经理','2025-08-05',15000.00),(102,'李华','销售部','销售代表','2025-08-05',8000.00),(103,'王芳','技术部','工程师','2025-08-05',12000.00),(104,'赵刚','技术部','高级工程师','2025-08-05',18000.00),(105,'刘洋','市场部','市场专员','2025-08-05',9000.00);-- sales DMLINSERT INTO `sales` (`sale_id`,`employee_id`,`product`,`amount`,`sale_date`) VALUES (1,101,'笔记本电脑',12000.00,'2025-08-05'),(2,102,'智能手机',6000.00,'2025-08-05'),(3,101,'显示器',2500.00,'2025-08-05'),(4,103,'软件授权',18000.00,'2025-08-05'),(5,102,'平板电脑',4500.00,'2025-08-05'),(6,104,'服务器',35000.00,'2025-08-05'),(7,105,'广告位',8000.00,'2025-08-05'),(8,101,'打印机',3200.00,'2025-08-05'),(9,102,'路由器',1800.00,'2025-08-05'),(10,104,'工作站',28000.00,'2025-08-05'),(11,105,'推广活动',12000.00,'2025-08-05');SET FOREIGN_KEY_CHECKS = 1;3、详细的实现代码
import pymysqlimport pandas as pdimport matplotlib.pyplot as pltimport seaborn as snsfrom datetime import datetime, timedeltaimport numpy as npfrom scipy.interpolate import make_interp_splinefrom decimal import Decimalimport matplotlib as mplfrom docx import Documentfrom docx.shared import Inches, Pt, RGBColorfrom docx.enum.text import WD_ALIGN_PARAGRAPHimport iofrom matplotlib.patches import Patch # 添加这行 # 设置全局中文字体支持plt.rcParams['font.sans-serif'] = ['SimHei', 'Microsoft YaHei', 'KaiTi', 'Arial Unicode MS']plt.rcParams['axes.unicode_minus'] = False def get_conn(): return pymysql.connect( host='localhost', user='root', password='root', database='yggx', charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor ) def run_query(sql): """执行SQL查询并返回DataFrame""" conn = get_conn() try: with conn.cursor() as cursor: cursor.execute(sql) result = cursor.fetchall() return pd.DataFrame(result) finally: conn.close() def convert_decimal_to_float(df): """将DataFrame中的Decimal类型转换为float""" for col in df.columns: if df[col].dtype == object and any(isinstance(x, Decimal) for x in df[col]): df[col] = df[col].astype(float) return df def analyze_data(): """执行关系数据分析 - 修复版""" print("\n开始数据分析...") # 1. 获取所有数据 employees_df = run_query("SELECT * FROM employees") sales_df = run_query("SELECT * FROM sales") dept_df = run_query("SELECT * FROM departments") # 转换Decimal类型为float employees_df = convert_decimal_to_float(employees_df) sales_df = convert_decimal_to_float(sales_df) dept_df = convert_decimal_to_float(dept_df) print("\n员工数据 (前5行):") print(employees_df.head()) print("\n销售数据 (前5行):") print(sales_df.head()) print("\n部门数据:") print(dept_df) # 检查数据是否为空 if employees_df.empty or sales_df.empty or dept_df.empty: print("\n错误: 从数据库读取的数据为空") return {} # 2. 关系连接：员工销售明细 sales_detail = pd.merge( sales_df, employees_df[['employee_id', 'name', 'department', 'position']], on='employee_id' ) # 添加部门预算信息 sales_detail = pd.merge( sales_detail, dept_df[['department_name', 'budget']], left_on='department', right_on='department_name' ).drop(columns='department_name') print("\n销售明细 (前5行):") print(sales_detail.head()) # 3. 部门销售分析 dept_sales = sales_detail.groupby('department').agg( total_sales=('amount', 'sum'), avg_sale=('amount', 'mean'), transaction_count=('sale_id', 'count'), budget=('budget', 'first') ).reset_index() dept_sales['budget_utilization'] = dept_sales['total_sales'] / dept_sales['budget'] print("\n部门销售分析:") print(dept_sales) # 4. 员工绩效分析 employee_performance = sales_detail.groupby(['employee_id', 'name', 'department']).agg( total_sales=('amount', 'sum'), avg_sale=('amount', 'mean'), sales_count=('sale_id', 'count') ).reset_index() # 添加员工薪资信息 employee_performance = pd.merge( employee_performance, employees_df[['employee_id', 'salary']], on='employee_id' ) employee_performance['sales_per_salary'] = employee_performance['total_sales'] / employee_performance['salary'] print("\n员工绩效分析:") print(employee_performance.sort_values('total_sales', ascending=False)) # 5. 时间序列分析 (最近30天销售趋势) # 确保日期格式正确 sales_detail['sale_date'] = pd.to_datetime(sales_detail['sale_date']) last_30_days = datetime.now() - timedelta(days=30) recent_sales = sales_detail[sales_detail['sale_date'] > last_30_days] if not recent_sales.empty: daily_sales = recent_sales.groupby('sale_date')['amount'].sum().reset_index() print("\n最近30天每日销售总额:") print(daily_sales) else: print("\n警告: 最近30天无销售数据") daily_sales = pd.DataFrame() return { 'sales_detail': sales_detail, 'dept_sales': dept_sales, 'employee_performance': employee_performance, 'daily_sales': daily_sales } def visualize_results(results): """可视化分析结果 - 修复版""" print("\n开始可视化分析结果...") # 创建大图表 fig = plt.figure(figsize=(18, 12)) fig.suptitle('销售数据分析报告', fontsize=20, fontweight='bold') # 1. 部门销售对比 ax1 = plt.subplot(2, 2, 1) dept_sales = results['dept_sales'] if not dept_sales.empty: # 使用更美观的颜色 colors = plt.cm.viridis_r(np.linspace(0, 1, len(dept_sales))) bars = plt.bar(dept_sales['department'], dept_sales['total_sales'], color=colors) plt.title('部门销售总额对比', fontsize=14) plt.ylabel('销售总额', fontsize=12) plt.xlabel('部门', fontsize=12) plt.grid(axis='y', linestyle='--', alpha=0.7) # 添加数值标签 for bar in bars: height = bar.get_height() ax1.annotate(f'{height:,.0f}元', xy=(bar.get_x() + bar.get_width() / 2, height), xytext=(0, 3), # 3 points vertical offset textcoords="offset points", ha='center', va='bottom', fontsize=10) else: plt.text(0.5, 0.5, '无部门销售数据', ha='center', va='center', fontsize=12) plt.title('部门销售总额对比', fontsize=14) # 2. 员工销售排名 ax2 = plt.subplot(2, 2, 2) emp_perf = results['employee_performance'] if not emp_perf.empty: emp_perf = emp_perf.sort_values('total_sales', ascending=False) # 为每个部门分配不同颜色 departments = emp_perf['department'].unique() color_map = {dept: plt.cm.tab10(i) for i, dept in enumerate(departments)} bars = ax2.bar(emp_perf['name'], emp_perf['total_sales'], color=[color_map[dept] for dept in emp_perf['department']]) plt.title('员工销售排名', fontsize=14) plt.ylabel('销售总额', fontsize=12) plt.xlabel('员工姓名', fontsize=12) plt.xticks(rotation=15, fontsize=10) plt.grid(axis='y', linestyle='--', alpha=0.7) # 添加图例 from matplotlib.patches import Patch legend_elements = [Patch(facecolor=color_map[dept], label=dept) for dept in departments] ax2.legend(handles=legend_elements, title='部门', loc='upper right') # 添加数值标签 for bar in bars: height = bar.get_height() ax2.annotate(f'{height:,.0f}元', xy=(bar.get_x() + bar.get_width() / 2, height), xytext=(0, 3), textcoords="offset points", ha='center', va='bottom', fontsize=9) else: plt.text(0.5, 0.5, '无员工销售数据', ha='center', va='center', fontsize=12) plt.title('员工销售排名', fontsize=14) # 3. 预算使用率 ax3 = plt.subplot(2, 2, 3) if not dept_sales.empty: dept_sales = dept_sales.sort_values('budget_utilization', ascending=False) colors = plt.cm.Pastel1(np.linspace(0, 1, len(dept_sales))) # 修复Decimal转换问题 budget_utilization = dept_sales['budget_utilization'].values.astype(float) total_utilization = budget_utilization.sum() wedges, texts, autotexts = ax3.pie( budget_utilization, labels=dept_sales['department'], autopct=lambda p: f'{p:.1f}%\n({p/100*total_utilization:.2f})', startangle=90, colors=colors, wedgeprops={'edgecolor': 'w', 'linewidth': 1.5}, textprops={'fontsize': 10} ) plt.title('部门预算使用率', fontsize=14) # 添加中心标题 ax3.text(0, 0, f'总使用率: {total_utilization*100:.1f}%', ha='center', va='center', fontsize=12, fontweight='bold') else: plt.text(0.5, 0.5, '无部门预算数据', ha='center', va='center', fontsize=12) plt.title('部门预算使用率', fontsize=14) # 4. 销售趋势图 ax4 = plt.subplot(2, 2, 4) daily_sales = results['daily_sales'] if not daily_sales.empty: daily_sales = daily_sales.sort_values('sale_date') # 创建平滑曲线 try: x = np.arange(len(daily_sales)) y = daily_sales['amount'].values # 创建平滑曲线 X_Y_Spline = make_interp_spline(x, y) X_ = np.linspace(x.min(), x.max(), 500) Y_ = X_Y_Spline(X_) # 绘制平滑曲线 plt.plot(X_, Y_, color='royalblue', alpha=0.5, label='平滑曲线') # 绘制原始数据点 plt.scatter(x, y, color='royalblue', s=50, zorder=5) except: # 如果平滑失败，直接绘制原始数据 plt.plot(daily_sales['sale_date'], daily_sales['amount'], color='royalblue', label='实际销售额') # 标记最高点和最低点 max_idx = daily_sales['amount'].idxmax() min_idx = daily_sales['amount'].idxmin() plt.scatter(daily_sales.loc[max_idx, 'sale_date'], daily_sales.loc[max_idx, 'amount'], color='red', s=100, zorder=5, label='最高点') plt.scatter(daily_sales.loc[min_idx, 'sale_date'], daily_sales.loc[min_idx, 'amount'], color='green', s=100, zorder=5, label='最低点') # 添加标注 plt.annotate(f'最高: {daily_sales.loc[max_idx, "amount"]:,.0f}元', xy=(daily_sales.loc[max_idx, 'sale_date'], daily_sales.loc[max_idx, 'amount']), xytext=(daily_sales.loc[max_idx, 'sale_date'] + timedelta(days=1), daily_sales.loc[max_idx, 'amount'] * 0.9), arrowprops=dict(facecolor='red', shrink=0.05)) plt.annotate(f'最低: {daily_sales.loc[min_idx, "amount"]:,.0f}元', xy=(daily_sales.loc[min_idx, 'sale_date'], daily_sales.loc[min_idx, 'amount']), xytext=(daily_sales.loc[min_idx, 'sale_date'] - timedelta(days=2), daily_sales.loc[min_idx, 'amount'] * 1.1), arrowprops=dict(facecolor='green', shrink=0.05)) plt.title('最近30天销售趋势', fontsize=14) plt.xlabel('日期', fontsize=12) plt.ylabel('每日销售额', fontsize=12) plt.xticks(rotation=45, fontsize=9) plt.grid(True, linestyle='--', alpha=0.7) plt.legend() else: plt.text(0.5, 0.5, '无近期销售数据', ha='center', va='center', fontsize=12) plt.title('最近30天销售趋势', fontsize=14) # 添加图表边框 for ax in [ax1, ax2, ax3, ax4]: for spine in ax.spines.values(): spine.set_visible(True) spine.set_edgecolor('#DDDDDD') spine.set_linewidth(1.5) plt.tight_layout(rect=[0, 0, 1, 0.96]) # 为总标题留出空间 plt.savefig('sales_analysis.png', dpi=300, bbox_inches='tight') plt.show() print("\n分析图表已保存为 'sales_analysis.png'") def generate_word_report(results): """生成Word报告""" doc = Document() # 添加标题 doc.add_heading('销售数据分析报告', 0) # 添加报告时间 current_time = datetime.now().strftime("%Y年%m月%d日 %H:%M:%S") doc.add_paragraph(f'报告生成时间：{current_time}') doc.add_paragraph('='*50) # 1. 部门销售分析 doc.add_heading('1. 部门销售分析', level=1) dept_sales = results['dept_sales'] if not dept_sales.empty: table = doc.add_table(rows=1, cols=5) table.style = 'Table Grid' header_cells = table.rows[0].cells headers = ['部门', '总销售额', '平均销售额', '交易次数', '预算使用率'] for i, header in enumerate(headers): header_cells[i].text = header for _, row in dept_sales.iterrows(): row_cells = table.add_row().cells row_cells[0].text = str(row['department']) row_cells[1].text = f"{row['total_sales']:,.2f}" row_cells[2].text = f"{row['avg_sale']:,.2f}" row_cells[3].text = str(row['transaction_count']) row_cells[4].text = f"{row['budget_utilization']*100:.2f}%" else: doc.add_paragraph('无部门销售数据') # 2. 员工绩效分析 doc.add_heading('2. 员工绩效分析', level=1) emp_perf = results['employee_performance'] if not emp_perf.empty: table = doc.add_table(rows=1, cols=6) table.style = 'Table Grid' header_cells = table.rows[0].cells headers = ['姓名', '部门', '总销售额', '平均销售额', '销售笔数', '销售/薪资比'] for i, header in enumerate(headers): header_cells[i].text = header for _, row in emp_perf.iterrows(): row_cells = table.add_row().cells row_cells[0].text = str(row['name']) row_cells[1].text = str(row['department']) row_cells[2].text = f"{row['total_sales']:,.2f}" row_cells[3].text = f"{row['avg_sale']:,.2f}" row_cells[4].text = str(row['sales_count']) row_cells[5].text = f"{row['sales_per_salary']:,.2f}" else: doc.add_paragraph('无员工绩效数据') # 3. 销售趋势分析 doc.add_heading('3. 销售趋势分析', level=1) daily_sales = results['daily_sales'] if not daily_sales.empty: doc.add_paragraph(f"最高单日销售额：{daily_sales['amount'].max():,.2f}") doc.add_paragraph(f"最低单日销售额：{daily_sales['amount'].min():,.2f}") doc.add_paragraph(f"平均日销售额：{daily_sales['amount'].mean():,.2f}") else: doc.add_paragraph('无销售趋势数据') # 4. 添加图表 doc.add_heading('4. 数据可视化', level=1) # 保存当前的matplotlib图表到Word doc.add_paragraph('以下是销售数据的可视化分析：') # 调用visualize_results生成图表并保存 visualize_results(results) doc.add_picture('sales_analysis.png', width=Inches(6)) # 添加结论 doc.add_heading('5. 分析结论', level=1) conclusions = [ " 各部门销售情况总体表现良好，预算执行率保持在合理水平。", "员工销售业绩分布相对均衡，团队协作效果显著。", " 销售趋势呈现稳定增长态势，市场反应积极。" ] for conclusion in conclusions: doc.add_paragraph(conclusion, style='List Number') # 保存Word文档 doc.save('销售数据分析报告.docx') print("\n分析报告已保存为 '销售数据分析报告.docx'") def main(): """主函数 - 修改版""" print("="*50) print("关系数据分析演示") print("="*50) # # 初始化数据库 # print("\n初始化数据库...") # create_tables() # insert_sample_data() # 执行分析 print("\n执行数据分析...") results = analyze_data() if results: # 生成Word报告 print("\n生成Word报告...") generate_word_report(results) else: print("\n分析失败，无有效数据") print("\n关系数据分析完成!") if __name__ == "__main__": main()案例2
- 数据库连接 (
connect_to_db): 使用mysql.connector连接到你的MySQL数据库。你需要修改DB_CONFIG字典中的host,user,password, 和database为你自己的配置。 - 部门销售分析 (
analyze_department_sales):- 执行SQL查询，按部门汇总销售额和订单数。
- 使用
pandas将结果整理成表格并打印。 - 使用
matplotlib绘制柱状图，并保存为department_sales.png。
- 会员购买与爱好分析 (
analyze_member_behavior):- 查询会员的消费总额、订单数及购买的产品品类。
- （简化处理）假设查询结果已按消费额排序，取每个会员的第一条记录作为其“最偏好品类”。
- 统计各品类的会员数量，作为爱好分布。
- 使用
pandas打印品类分布和高价值会员TOP10。 - 使用
matplotlib绘制饼图展示品类偏好，并保存为member_category_preference.png。
- 销售趋势分析 (
analyze_sales_trend):- 查询过去90天内每天的总销售额。
- 使用
pandas打印最近的数据。 - 使用
matplotlib绘制折线图展示趋势，并保存为sales_trend.png。
- 生成报告 (
generate_report): 将所有分析结果（表格数据和图表路径）汇总到一个.txt文件中。 - 主函数 (
main): 负责调用以上所有函数，管理数据库连接的开启和关闭。
import mysql.connector
import pandas as pd
import matplotlib.pyplot as plt
from datetime import datetime, timedelta
import os
# --- 数据库配置 ---
# 请根据你的MySQL服务器信息修改以下配置
DB_CONFIG = {
'host': 'localhost',
'user': 'your_username', # 替换为你的数据库用户名
'password': 'your_password', # 替换为你的数据库密码
'database': 'ecommerce_db' # 替换为你的数据库名
}
# --- 报告生成函数 ---
def connect_to_db():
"""建立数据库连接"""
try:
connection = mysql.connector.connect(**DB_CONFIG)
print("成功连接到数据库")
return connection
except mysql.connector.Error as err:
print(f"数据库连接失败: {err}")
return None
def analyze_department_sales(cursor):
"""分析各部门销售情况"""
print("\n--- 部门销售分析 ---")
# 查询各部门总销售额和订单数
query_dept_sales = """
SELECT
p.department,
SUM(oi.quantity * oi.price) AS total_sales,
COUNT(DISTINCT o.order_id) AS total_orders
FROM
orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY
p.department
ORDER BY
total_sales DESC;
"""
cursor.execute(query_dept_sales)
dept_sales_data = cursor.fetchall()
if not dept_sales_data:
print("未查询到部门销售数据。")
return None, None
df_dept_sales = pd.DataFrame(dept_sales_data, columns=['部门', '总销售额', '总订单数'])
print(df_dept_sales.to_string(index=False))
# 绘制部门销售额柱状图
plt.figure(figsize=(10, 6))
bars = plt.bar(df_dept_sales['部门'], df_dept_sales['总销售额'], color='skyblue')
plt.xlabel('部门')
plt.ylabel('总销售额 (元)')
plt.title('各部门销售额对比')
plt.xticks(rotation=45, ha='right')
# 在柱状图上添加数值标签
for bar in bars:
yval = bar.get_height()
plt.text(bar.get_x() + bar.get_width()/2.0, yval, round(yval, 2), ha='center', va='bottom')
plt.tight_layout()
dept_sales_chart_path = 'department_sales.png'
plt.savefig(dept_sales_chart_path)
plt.close() # 关闭图形，释放内存
print(f"部门销售图表已保存至: {dept_sales_chart_path}")
return df_dept_sales, dept_sales_chart_path
def analyze_member_behavior(cursor):
"""分析会员购买与爱好"""
print("\n--- 会员购买与爱好分析 ---")
# 查询每位会员的总消费、订单数和最常购买的品类
query_member_behavior = """
SELECT
c.customer_id,
c.name,
SUM(oi.quantity * oi.price) AS total_spent,
COUNT(DISTINCT o.order_id) AS total_orders,
p.department AS favorite_category
FROM
customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE
c.is_member = TRUE
GROUP BY
c.customer_id, c.name, p.department
ORDER BY
total_spent DESC, total_orders DESC;
"""
cursor.execute(query_member_behavior)
member_raw_data = cursor.fetchall()
if not member_raw_data:
print("未查询到会员行为数据。")
return None, None
df_member_raw = pd.DataFrame(member_raw_data, columns=['会员ID', '姓名', '总消费', '总订单数', '偏好品类'])
# 为了确定最偏好品类，需要按会员分组，取消费最高的品类
# 这里简化处理，假设查询结果已经按消费降序排列，第一个就是最偏好的
# 更严谨的做法是用窗口函数或二次聚合
df_member_top_cat = df_member_raw.sort_values(['会员ID', '总消费'], ascending=[True, False]).drop_duplicates('会员ID')
# 计算各品类的会员数量（作为爱好分布）
category_counts = df_member_top_cat['偏好品类'].value_counts().sort_values(ascending=False)
df_category_dist = category_counts.reset_index()
df_category_dist.columns = ['品类', '会员数量']
print("会员品类偏好分布:")
print(df_category_dist.to_string(index=False))
# 绘制会员品类偏好饼图
plt.figure(figsize=(8, 8))
plt.pie(df_category_dist['会员数量'], labels=df_category_dist['品类'], autopct='%1.1f%%', startangle=140)
plt.title('会员品类偏好分布')
member_behavior_chart_path = 'member_category_preference.png'
plt.savefig(member_behavior_chart_path)
plt.close()
print(f"会员品类偏好图表已保存至: {member_behavior_chart_path}")
# 显示高价值会员TOP 10
top_members = df_member_top_cat.head(10)
print("\n高价值会员TOP 10:")
print(top_members[['会员ID', '姓名', '总消费', '总订单数', '偏好品类']].to_string(index=False))
return df_member_top_cat, member_behavior_chart_path
def analyze_sales_trend(cursor):
"""分析销售趋势"""
print("\n--- 销售趋势分析 ---")
# 查询每日销售额 (过去90天)
end_date = datetime.now()
start_date = end_date - timedelta(days=90)
query_sales_trend = f"""
SELECT
DATE(o.order_date) AS order_day,
SUM(oi.quantity * oi.price) AS daily_sales
FROM
orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE
o.order_date >= '{start_date.strftime('%Y-%m-%d')}' AND
o.order_date <= '{end_date.strftime('%Y-%m-%d')}'
GROUP BY
DATE(o.order_date)
ORDER BY
order_day;
"""
cursor.execute(query_sales_trend)
trend_data = cursor.fetchall()
if not trend_data:
print("未查询到销售趋势数据。")
return None, None
df_trend = pd.DataFrame(trend_data, columns=['日期', '日销售额'])
print("近期销售趋势 (部分数据):")
print(df_trend.tail(10).to_string(index=False)) # 显示最近10天
# 绘制销售趋势折线图
plt.figure(figsize=(12, 6))
plt.plot(df_trend['日期'], df_trend['日销售额'], marker='o', linestyle='-', color='green')
plt.xlabel('日期')
plt.ylabel('日销售额 (元)')
plt.title('近90天销售趋势')
plt.xticks(rotation=45, ha='right')
plt.grid(True)
plt.tight_layout()
sales_trend_chart_path = 'sales_trend.png'
plt.savefig(sales_trend_chart_path)
plt.close()
print(f"销售趋势图表已保存至: {sales_trend_chart_path}")
return df_trend, sales_trend_chart_path
def generate_report(dept_df, dept_chart, member_df, member_chart, trend_df, trend_chart):
"""生成文本报告"""
report_filename = f"电商销售分析报告_{datetime.now().strftime('%Y%m%d_%H%M%S')}.txt"
with open(report_filename, 'w', encoding='utf-8') as f:
f.write("========================================\n")
f.write(" 电商销售分析报告\n")
f.write(f" 生成时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\n")
f.write("========================================\n\n")
f.write("--- 1. 部门销售分析 ---\n")
if dept_df is not None and not dept_df.empty:
f.write(dept_df.to_string(index=False))
f.write(f"\n图表: {dept_chart}\n")
else:
f.write("无数据\n")
f.write("\n")
f.write("--- 2. 会员购买与爱好分析 ---\n")
if member_df is not None and not member_df.empty:
# 重新计算品类分布用于报告文本
category_counts = member_df['偏好品类'].value_counts().sort_values(ascending=False)
df_category_dist = category_counts.reset_index()
df_category_dist.columns = ['品类', '会员数量']
f.write("会员品类偏好分布:\n")
f.write(df_category_dist.to_string(index=False))
f.write(f"\n图表: {member_chart}\n\n")
f.write("高价值会员TOP 10:\n")
top_members = member_df.head(10)
f.write(top_members[['会员ID', '姓名', '总消费', '总订单数', '偏好品类']].to_string(index=False))
else:
f.write("无数据\n")
f.write("\n")
f.write("--- 3. 销售趋势分析 ---\n")
if trend_df is not None and not trend_df.empty:
f.write("近期销售趋势 (最后10天):\n")
f.write(trend_df.tail(10).to_string(index=False))
f.write(f"\n图表: {trend_chart}\n")
else:
f.write("无数据\n")
f.write("\n")
f.write("========================================\n")
f.write(" 报告结束\n")
f.write("========================================\n")
print(f"\n分析报告已生成: {report_filename}")
def main():
"""主函数"""
connection = connect_to_db()
if not connection:
return
cursor = connection.cursor()
# 执行分析
dept_df, dept_chart = analyze_department_sales(cursor)
member_df, member_chart = analyze_member_behavior(cursor)
trend_df, trend_chart = analyze_sales_trend(cursor)
# 生成报告
generate_report(dept_df, dept_chart, member_df, member_chart, trend_df, trend_chart)
# 关闭连接
cursor.close()
connection.close()
print("\n数据库连接已关闭。")
if __name__ == "__main__":
main()