📈 Phân tích dữ liệu kết hợp: Tích hợp SQL Server với Jupyter Notebook cho phân tích dữ liệu định kỳ

1# Cài đặt Jupyter và các thư viện phân tích dữ liệu
2pip install jupyter pandas numpy matplotlib seaborn
3
4# Cài đặt thư viện để kết nối với SQL Server
5pip install pyodbc sqlalchemy

1jupyter notebook

1import pandas as pd
2import numpy as np
3import matplotlib.pyplot as plt
4import seaborn as sns
5import pyodbc
6from sqlalchemy import create_engine
7import urllib
8import warnings
9from datetime import datetime, timedelta
10
11# Bỏ qua các cảnh báo không cần thiết
12warnings.filterwarnings('ignore')
13
14# Cấu hình biểu đồ
15plt.style.use('ggplot')
16sns.set(style="whitegrid")
17%matplotlib inline
18
19# Tăng giới hạn hiển thị số cột/hàng của DataFrame
20pd.set_option('display.max_columns', None)
21pd.set_option('display.max_rows', 100)

1def get_connection_pyodbc():
2    server = 'your_server_name'
3    database = 'your_database'
4    username = 'your_username'
5    password = 'your_password'
6    driver = '{ODBC Driver 17 for SQL Server}'
7    
8    connection_string = f'DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}'
9    
10    try:
11        conn = pyodbc.connect(connection_string)
12        print("Kết nối thành công đến SQL Server!")
13        return conn
14    except Exception as e:
15        print(f"Lỗi kết nối: {e}")
16        return None

1def get_connection_sqlalchemy():
2    server = 'your_server_name'
3    database = 'your_database'
4    username = 'your_username'
5    password = 'your_password'
6    driver = 'ODBC Driver 17 for SQL Server'
7    
8    params = urllib.parse.quote_plus(f'DRIVER={{{driver}}};SERVER={server};DATABASE={database};UID={username};PWD={password}')
9    engine = create_engine(f'mssql+pyodbc:///?odbc_connect={params}')
10    
11    try:
12        conn = engine.connect()
13        print("Kết nối thành công đến SQL Server bằng SQLAlchemy!")
14        return engine
15    except Exception as e:
16        print(f"Lỗi kết nối SQLAlchemy: {e}")
17        return None

1pip install ipython-sql

1%load_ext sql
2%config SqlMagic.displaylimit = 100  # Giới hạn số hàng hiển thị
3%config SqlMagic.feedback = False    # Tắt phản hồi chi tiết
4
5# Cấu hình kết nối
6connection_string = 'mssql+pyodbc://username:password@server_name/database_name?driver=ODBC+Driver+17+for+SQL+Server'
7%sql $connection_string

1%%sql
2SELECT TOP 10 *
3FROM Sales
4ORDER BY OrderDate DESC

1# Xác định khoảng thời gian báo cáo
2report_date = datetime.now()
3start_date = (report_date - timedelta(days=30)).strftime('%Y-%m-%d')
4end_date = report_date.strftime('%Y-%m-%d')
5
6# Tiêu đề báo cáo
7report_title = f"Báo cáo doanh số bán hàng từ {start_date} đến {end_date}"
8
9# Tạo thư mục lưu báo cáo nếu chưa tồn tại
10import os
11report_dir = "reports"
12if not os.path.exists(report_dir):
13    os.makedirs(report_dir)

1# Kết nối đến SQL Server
2conn = get_connection_pyodbc()
3
4# Truy vấn dữ liệu bán hàng trong khoảng thời gian báo cáo
5query = f"""
6SELECT 
7    s.OrderDate,
8    p.ProductName,
9    c.CategoryName,
10    r.RegionName,
11    s.Quantity,
12    s.UnitPrice,
13    s.Quantity * s.UnitPrice AS SalesAmount
14FROM 
15    Sales s
16    JOIN Products p ON s.ProductID = p.ProductID
17    JOIN Categories c ON p.CategoryID = c.CategoryID
18    JOIN Regions r ON s.RegionID = r.RegionID
19WHERE 
20    s.OrderDate BETWEEN '{start_date}' AND '{end_date}'
21ORDER BY
22    s.OrderDate
23"""
24
25sales_df = pd.read_sql(query, conn)
26conn.close()
27
28print(f"Đã truy vấn {len(sales_df)} bản ghi dữ liệu bán hàng.")
29sales_df.head()

1# Chuyển đổi OrderDate thành datetime nếu chưa phải
2if not pd.api.types.is_datetime64_any_dtype(sales_df['OrderDate']):
3    sales_df['OrderDate'] = pd.to_datetime(sales_df['OrderDate'])
4
5# Tạo các cột thời gian hữu ích
6sales_df['Year'] = sales_df['OrderDate'].dt.year
7sales_df['Month'] = sales_df['OrderDate'].dt.month
8sales_df['MonthName'] = sales_df['OrderDate'].dt.strftime('%b')
9sales_df['Day'] = sales_df['OrderDate'].dt.day
10sales_df['WeekDay'] = sales_df['OrderDate'].dt.weekday
11sales_df['WeekDayName'] = sales_df['OrderDate'].dt.strftime('%a')
12
13# Tổng hợp số liệu chính
14total_sales = sales_df['SalesAmount'].sum()
15avg_order_value = sales_df['SalesAmount'].mean()
16total_orders = len(sales_df['OrderDate'].unique())
17total_products = len(sales_df['ProductName'].unique())
18
19print(f"Tổng doanh số: {total_sales:,.2f}")
20print(f"Giá trị đơn hàng trung bình: {avg_order_value:,.2f}")
21print(f"Tổng số đơn hàng: {total_orders}")
22print(f"Tổng số sản phẩm đã bán: {total_products}")

1# Tạo các biểu đồ phân tích
2
3# 1. Doanh số theo ngày
4plt.figure(figsize=(12, 6))
5daily_sales = sales_df.groupby('OrderDate')['SalesAmount'].sum().reset_index()
6plt.plot(daily_sales['OrderDate'], daily_sales['SalesAmount'], marker='o', linestyle='-', color='blue', alpha=0.7)
7plt.title('Doanh số bán hàng theo ngày', fontsize=16)
8plt.xlabel('Ngày', fontsize=12)
9plt.ylabel('Doanh số (VND)', fontsize=12)
10plt.grid(True, alpha=0.3)
11plt.xticks(rotation=45)
12plt.tight_layout()
13plt.show()
14
15# 2. Top 10 sản phẩm bán chạy nhất
16plt.figure(figsize=(12, 8))
17top_products = sales_df.groupby('ProductName')['SalesAmount'].sum().sort_values(ascending=False).head(10)
18ax = top_products.plot(kind='barh', color='skyblue')
19plt.title('Top 10 sản phẩm bán chạy nhất', fontsize=16)
20plt.xlabel('Doanh số (VND)', fontsize=12)
21plt.ylabel('Sản phẩm', fontsize=12)
22plt.grid(axis='x', alpha=0.3)
23
24# Thêm giá trị doanh số lên cột
25for i, v in enumerate(top_products):
26    ax.text(v + 0.1, i, f'{v:,.0f}', va='center', fontsize=10)
27
28plt.tight_layout()
29plt.show()
30
31# 3. Doanh số theo danh mục sản phẩm
32plt.figure(figsize=(10, 6))
33category_sales = sales_df.groupby('CategoryName')['SalesAmount'].sum().sort_values(ascending=False)
34plt.pie(category_sales, labels=category_sales.index, autopct='%1.1f%%', startangle=90, 
35        shadow=True, wedgeprops={'edgecolor': 'white'}, textprops={'fontsize': 12})
36plt.title('Tỷ lệ doanh số theo danh mục sản phẩm', fontsize=16)
37plt.axis('equal')
38plt.tight_layout()
39plt.show()
40
41# 4. Doanh số theo khu vực
42plt.figure(figsize=(10, 6))
43region_sales = sales_df.groupby('RegionName')['SalesAmount'].sum().sort_values(ascending=False)
44ax = region_sales.plot(kind='bar', color=sns.color_palette('viridis', len(region_sales)))
45plt.title('Doanh số bán hàng theo khu vực', fontsize=16)
46plt.xlabel('Khu vực', fontsize=12)
47plt.ylabel('Doanh số (VND)', fontsize=12)
48plt.grid(axis='y', alpha=0.3)
49plt.tight_layout()
50plt.show()

1# Tính toán tương quan giữa các biến số
2numeric_cols = ['Quantity', 'UnitPrice', 'SalesAmount']
3correlation = sales_df[numeric_cols].corr()
4
5plt.figure(figsize=(8, 6))
6sns.heatmap(correlation, annot=True, cmap='coolwarm', linewidths=0.5)
7plt.title('Ma trận tương quan giữa các biến số', fontsize=14)
8plt.tight_layout()
9plt.show()
10
11# Phân phối số lượng sản phẩm trong mỗi đơn hàng
12plt.figure(figsize=(10, 6))
13sns.histplot(data=sales_df, x='Quantity', bins=20, kde=True)
14plt.title('Phân phối số lượng sản phẩm trong mỗi đơn hàng', fontsize=14)
15plt.xlabel('Số lượng', fontsize=12)
16plt.ylabel('Tần suất', fontsize=12)
17plt.grid(True, alpha=0.3)
18plt.tight_layout()
19plt.show()

1jupyter nbconvert --to python sales_report.ipynb

1import sys
2import argparse
3
4# Phân tích đối số dòng lệnh
5parser = argparse.ArgumentParser(description='Tạo báo cáo doanh số bán hàng')
6parser.add_argument('--start-date', help='Ngày bắt đầu (YYYY-MM-DD)')
7parser.add_argument('--end-date', help='Ngày kết thúc (YYYY-MM-DD)')
8parser.add_argument('--output', help='Đường dẫn xuất báo cáo')
9args = parser.parse_args()
10
11# Cập nhật tham số báo cáo
12if args.start_date:
13    start_date = args.start_date
14# ... và tương tự cho các tham số khác

1# Mở crontab để chỉnh sửa
2crontab -e
3
4# Thêm dòng sau để chạy script vào 6 giờ sáng mỗi thứ Hai
50 6 * * 1 /usr/bin/python3 /path/to/sales_report.py --output /path/to/reports/ >> /path/to/logs/sales_report.log 2>&1

1# Tạo lịch chạy task mỗi thứ Hai lúc 6 giờ sáng
2$action = New-ScheduledTaskAction -Execute "python" -Argument "C:\path\to\sales_report.py --output C:\path\to\reports\"
3$trigger = New-ScheduledTaskTrigger -Weekly -DaysOfWeek Monday -At 6am
4Register-ScheduledTask -Action $action -Trigger $trigger -TaskName "WeeklySalesReport" -Description "Báo cáo doanh số hàng tuần"

1import smtplib
2from email.mime.multipart import MIMEMultipart
3from email.mime.text import MIMEText
4from email.mime.application import MIMEApplication
5
6def send_report_email(report_file, recipients):
7    # Cấu hình email
8    sender = "your_email@example.com"
9    password = "your_password"
10    subject = f"Báo cáo doanh số bán hàng {start_date} đến {end_date}"
11    
12    # Tạo thông điệp
13    msg = MIMEMultipart()
14    msg['From'] = sender
15    msg['To'] = ", ".join(recipients)
16    msg['Subject'] = subject
17    
18    # Thêm nội dung
19    body = f"""
20    Kính gửi,
21    
22    Đính kèm là báo cáo doanh số bán hàng từ {start_date} đến {end_date}.
23    
24    Tổng doanh số: {total_sales:,.2f}
25    Tổng số đơn hàng: {total_orders}
26    
27    Trân trọng,
28    Hệ thống báo cáo tự động
29    """
30    msg.attach(MIMEText(body, 'plain'))
31    
32    # Đính kèm báo cáo
33    with open(report_file, "rb") as f:
34        attachment = MIMEApplication(f.read(), _subtype="pdf")
35        attachment.add_header('Content-Disposition', 'attachment', filename=os.path.basename(report_file))
36        msg.attach(attachment)
37    
38    # Gửi email
39    try:
40        server = smtplib.SMTP_SSL('smtp.gmail.com', 465)
41        server.login(sender, password)
42        server.send_message(msg)
43        server.quit()
44        print(f"Đã gửi báo cáo đến: {', '.join(recipients)}")
45    except Exception as e:
46        print(f"Lỗi khi gửi email: {e}")
47
48# Gọi hàm gửi báo cáo
49recipients = ["manager@example.com", "director@example.com"]
50send_report_email(f"{report_dir}/sales_report_{end_date}.pdf", recipients)

1import ipywidgets as widgets
2from IPython.display import display
3
4# Tạo widget chọn danh mục
5categories = [cat for cat in sales_df['CategoryName'].unique()]
6category_dropdown = widgets.Dropdown(
7    options=categories,
8    description='Danh mục:',
9    style={'description_width': 'initial'}
10)
11
12# Tạo widget chọn khu vực
13regions = [reg for reg in sales_df['RegionName'].unique()]
14region_dropdown = widgets.Dropdown(
15    options=regions,
16    description='Khu vực:',
17    style={'description_width': 'initial'}
18)
19
20# Hiển thị widgets
21display(category_dropdown, region_dropdown)
22
23# Hàm lọc dữ liệu dựa trên lựa chọn
24def filter_data(category, region):
25    conn = get_connection_pyodbc()
26    query = f"""
27    SELECT 
28        s.OrderDate,
29        p.ProductName,
30        c.CategoryName,
31        r.RegionName,
32        s.Quantity,
33        s.UnitPrice,
34        s.Quantity * s.UnitPrice AS SalesAmount
35    FROM 
36        Sales s
37        JOIN Products p ON s.ProductID = p.ProductID
38        JOIN Categories c ON p.CategoryID = c.CategoryID
39        JOIN Regions r ON s.RegionID = r.RegionID
40    WHERE 
41        c.CategoryName = '{category}'
42        AND r.RegionName = '{region}'
43        AND s.OrderDate BETWEEN '{start_date}' AND '{end_date}'
44    ORDER BY
45        s.OrderDate
46    """
47    filtered_df = pd.read_sql(query, conn)
48    conn.close()
49    return filtered_df
50
51# Tạo nút cập nhật
52update_button = widgets.Button(description='Cập nhật phân tích')
53
54# Hàm xử lý khi nhấn nút
55def on_update_clicked(b):
56    category = category_dropdown.value
57    region = region_dropdown.value
58    filtered_df = filter_data(category, region)
59    
60    # Hiển thị kết quả
61    print(f"Đã tìm thấy {len(filtered_df)} bản ghi cho danh mục '{category}' và khu vực '{region}'")
62    
63    # Vẽ biểu đồ
64    plt.figure(figsize=(12, 6))
65    daily_sales = filtered_df.groupby('OrderDate')['SalesAmount'].sum().reset_index()
66    plt.plot(daily_sales['OrderDate'], daily_sales['SalesAmount'], marker='o', linestyle='-')
67    plt.title(f'Doanh số bán hàng: {category} - {region}', fontsize=16)
68    plt.xlabel('Ngày', fontsize=12)
69    plt.ylabel('Doanh số (VND)', fontsize=12)
70    plt.grid(True, alpha=0.3)
71    plt.xticks(rotation=45)
72    plt.show()
73
74update_button.on_click(on_update_clicked)
75display(update_button)

1CREATE PROCEDURE GetSalesByDateRange
2    @StartDate DATE,
3    @EndDate DATE
4AS
5BEGIN
6    SELECT 
7        s.OrderDate,
8        p.ProductName,
9        c.CategoryName,
10        r.RegionName,
11        s.Quantity,
12        s.UnitPrice,
13        s.Quantity * s.UnitPrice AS SalesAmount
14    FROM 
15        Sales s
16        JOIN Products p ON s.ProductID = p.ProductID
17        JOIN Categories c ON p.CategoryID = c.CategoryID
18        JOIN Regions r ON s.RegionID = r.RegionID
19    WHERE 
20        s.OrderDate BETWEEN @StartDate AND @EndDate
21    ORDER BY
22        s.OrderDate
23END

1def call_stored_procedure(conn, proc_name, params=None):
2    cursor = conn.cursor()
3    if params:
4        cursor.execute(f"EXEC {proc_name} {params}")
5    else:
6        cursor.execute(f"EXEC {proc_name}")
7    
8    # Chuyển đổi kết quả thành DataFrame
9    columns = [column[0] for column in cursor.description]
10    data = cursor.fetchall()
11    df = pd.DataFrame.from_records(data, columns=columns)
12    
13    cursor.close()
14    return df
15
16# Gọi stored procedure
17conn = get_connection_pyodbc()
18sales_df = call_stored_procedure(
19    conn, 
20    "GetSalesByDateRange", 
21    f"@StartDate = '{start_date}', @EndDate = '{end_date}'"
22)
23conn.close()