Skip to Content

Tự động hóa & Machine Learning: Tự động dự đoán doanh thu từ dữ liệu trong SQL Server bằng mô hình Python

 Tự động hóa & Machine Learning: Tự động dự đoán doanh thu từ dữ liệu trong SQL Server bằng mô hình Python

Tự động dự đoán doanh thu từ dữ liệu trong SQL Server bằng mô hình Python

Giới thiệu

Trong thời đại số hóa hiện nay, việc dự đoán doanh thu dựa trên dữ liệu lịch sử đã trở thành một nhu cầu thiết yếu của các doanh nghiệp. Bằng cách kết hợp sức mạnh của SQL Server để lưu trữ dữ liệu và Python để xây dựng các mô hình Machine Learning, chúng ta có thể tạo ra một hệ thống dự đoán doanh thu tự động, cung cấp những thông tin giá trị cho việc ra quyết định kinh doanh.

Bài viết này sẽ hướng dẫn chi tiết cách xây dựng một hệ thống dự đoán doanh thu tự động bằng cách kết hợp SQL Server và các mô hình Machine Learning trong Python.

Tổng quan về quy trình

Quy trình dự đoán doanh thu tự động bao gồm các bước chính sau:

  1. Thu thập và chuẩn bị dữ liệu từ SQL Server
  2. Phân tích và tiền xử lý dữ liệu với Python
  3. Xây dựng và huấn luyện mô hình Machine Learning
  4. Đánh giá và tối ưu hóa mô hình
  5. Triển khai mô hình để dự đoán tự động

1. Thu thập và chuẩn bị dữ liệu từ SQL Server

Cấu trúc cơ sở dữ liệu

Đầu tiên, chúng ta cần có một cơ sở dữ liệu chứa thông tin về doanh thu lịch sử:

-- Tạo bảng dữ liệu doanh thu
CREATE TABLE DuLieuDoanhThu (
    ID INT PRIMARY KEY IDENTITY(1,1),
    NgayThang DATE NOT NULL,
    SanPham NVARCHAR(100) NOT NULL,
    DanhMuc NVARCHAR(50) NOT NULL,
    KhuVuc NVARCHAR(50) NOT NULL,
    Kenh NVARCHAR(50) NOT NULL,
    SoLuong INT NOT NULL,
    DonGia DECIMAL(18,2) NOT NULL,
    DoanhThu DECIMAL(18,2) NOT NULL,
    ChiPhiMarketing DECIMAL(18,2) NULL,
    ChiPhiVanChuyen DECIMAL(18,2) NULL,
    NgayTao DATETIME DEFAULT GETDATE()
);

Truy xuất dữ liệu từ SQL Server

-- Stored procedure để lấy dữ liệu huấn luyện mô hình
CREATE PROCEDURE sp_LayDuLieuHuanLuyen
    @TuNgay DATE,
    @DenNgay DATE
AS
BEGIN
    SELECT 
        YEAR(NgayThang) AS Nam,
        MONTH(NgayThang) AS Thang,
        DAY(NgayThang) AS Ngay,
        DATEPART(dw, NgayThang) AS ThuTrongTuan,
        DanhMuc,
        KhuVuc,
        Kenh,
        SUM(DoanhThu) AS TongDoanhThu,
        SUM(SoLuong) AS TongSoLuong,
        SUM(ChiPhiMarketing) AS TongChiPhiMarketing,
        AVG(DonGia) AS GiaTrungBinh
    FROM 
        DuLieuDoanhThu
    WHERE 
        NgayThang BETWEEN @TuNgay AND @DenNgay
    GROUP BY
        YEAR(NgayThang),
        MONTH(NgayThang),
        DAY(NgayThang),
        DATEPART(dw, NgayThang),
        DanhMuc,
        KhuVuc,
        Kenh
    ORDER BY
        Nam, Thang, Ngay;
END;

2. Phân tích và tiền xử lý dữ liệu với Python

import pandas as pd
import numpy as np
import pyodbc
from sklearn.preprocessing import OneHotEncoder, StandardScaler
from sklearn.model_selection import train_test_split

# Kết nối đến SQL Server
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=yourserver;DATABASE=yourdatabase;UID=youruser;PWD=yourpassword')

# Lấy dữ liệu từ SQL Server
query = """
EXEC sp_LayDuLieuHuanLuyen @TuNgay = '2022-01-01', @DenNgay = '2023-12-31'
"""
df = pd.read_sql(query, conn)

# Kiểm tra giá trị thiếu
print(f"Số lượng giá trị thiếu trong mỗi cột:\n{df.isnull().sum()}")

# Xử lý giá trị thiếu
df['TongChiPhiMarketing'].fillna(df['TongChiPhiMarketing'].mean(), inplace=True)

# Tạo thêm các đặc trưng (feature engineering)
df['NgayLe'] = 0  # Có thể cập nhật bằng danh sách các ngày lễ
df['TyLeChiPhiMarketing'] = df['TongChiPhiMarketing'] / df['TongDoanhThu']
df['MuaVu'] = df['Thang'].apply(lambda x: 'Xuan' if x in [1, 2, 3] else 
                                         'Ha' if x in [4, 5, 6] else 
                                         'Thu' if x in [7, 8, 9] else 'Dong')

# Mã hóa các biến phân loại (one-hot encoding)
categorical_cols = ['DanhMuc', 'KhuVuc', 'Kenh', 'MuaVu']
encoder = OneHotEncoder(sparse=False, drop='first')
encoded_features = encoder.fit_transform(df[categorical_cols])
encoded_feature_names = encoder.get_feature_names_out(categorical_cols)

# Kết hợp các đặc trưng đã mã hóa với dữ liệu gốc
encoded_df = pd.DataFrame(encoded_features, columns=encoded_feature_names)
df_final = pd.concat([df.drop(categorical_cols, axis=1).reset_index(drop=True), 
                      encoded_df.reset_index(drop=True)], axis=1)

# Phân chia dữ liệu thành tập huấn luyện và tập kiểm tra
X = df_final.drop('TongDoanhThu', axis=1)
y = df_final['TongDoanhThu']
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)

# Chuẩn hóa dữ liệu
scaler = StandardScaler()
X_train_scaled = scaler.fit_transform(X_train)
X_test_scaled = scaler.transform(X_test)

3. Xây dựng và huấn luyện mô hình Machine Learning

from sklearn.linear_model import LinearRegression, Ridge, Lasso
from sklearn.ensemble import RandomForestRegressor, GradientBoostingRegressor
from sklearn.svm import SVR
from sklearn.metrics import mean_squared_error, r2_score, mean_absolute_error
import joblib

# Huấn luyện mô hình hồi quy tuyến tính
linear_model = LinearRegression()
linear_model.fit(X_train_scaled, y_train)

# Huấn luyện mô hình Random Forest
rf_model = RandomForestRegressor(n_estimators=100, random_state=42)
rf_model.fit(X_train_scaled, y_train)

# Huấn luyện mô hình Gradient Boosting
gb_model = GradientBoostingRegressor(n_estimators=100, learning_rate=0.1, random_state=42)
gb_model.fit(X_train_scaled, y_train)

# Dictionary để lưu các mô hình
models = {
    'Linear Regression': linear_model,
    'Random Forest': rf_model,
    'Gradient Boosting': gb_model
}

# Đánh giá các mô hình trên dữ liệu kiểm tra
for name, model in models.items():
    y_pred = model.predict(X_test_scaled)
    mse = mean_squared_error(y_test, y_pred)
    rmse = np.sqrt(mse)
    mae = mean_absolute_error(y_test, y_pred)
    r2 = r2_score(y_test, y_pred)
    
    print(f"Mô hình: {name}")
    print(f"  - MSE: {mse:.2f}")
    print(f"  - RMSE: {rmse:.2f}")
    print(f"  - MAE: {mae:.2f}")
    print(f"  - R^2: {r2:.4f}")
    print("")

# Lưu mô hình tốt nhất (giả sử là Gradient Boosting)
best_model = models['Gradient Boosting']
joblib.dump(best_model, 'best_model.pkl')
joblib.dump(scaler, 'scaler.pkl')
joblib.dump(encoder, 'encoder.pkl')

4. Đánh giá và tối ưu hóa mô hình

from sklearn.model_selection import GridSearchCV
import matplotlib.pyplot as plt

# Tối ưu hóa siêu tham số của mô hình Gradient Boosting
param_grid = {
    'n_estimators': [50, 100, 200],
    'learning_rate': [0.01, 0.05, 0.1, 0.2],
    'max_depth': [3, 5, 7],
    'min_samples_split': [2, 5, 10]
}

grid_search = GridSearchCV(
    estimator=GradientBoostingRegressor(random_state=42),
    param_grid=param_grid,
    cv=5,
    scoring='neg_mean_squared_error',
    n_jobs=-1
)

grid_search.fit(X_train_scaled, y_train)

print(f"Tham số tốt nhất: {grid_search.best_params_}")
print(f"MSE tốt nhất: {-grid_search.best_score_:.2f}")

# Lưu mô hình tối ưu
optimized_model = grid_search.best_estimator_
joblib.dump(optimized_model, 'optimized_model.pkl')

# Phân tích mức độ quan trọng của các đặc trưng
feature_importance = optimized_model.feature_importances_
sorted_idx = np.argsort(feature_importance)
plt.figure(figsize=(10, 8))
plt.barh(range(len(sorted_idx)), feature_importance[sorted_idx], align='center')
plt.yticks(range(len(sorted_idx)), np.array(X.columns)[sorted_idx])
plt.title('Mức độ quan trọng của các đặc trưng')
plt.savefig('feature_importance.png')

# Vẽ biểu đồ so sánh giữa giá trị thực tế và dự đoán
y_pred = optimized_model.predict(X_test_scaled)
plt.figure(figsize=(10, 6))
plt.scatter(y_test, y_pred, alpha=0.5)
plt.plot([y_test.min(), y_test.max()], [y_test.min(), y_test.max()], 'r--')
plt.xlabel('Doanh thu thực tế')
plt.ylabel('Doanh thu dự đoán')
plt.title('So sánh giữa doanh thu thực tế và dự đoán')
plt.savefig('actual_vs_predicted.png')

5. Triển khai mô hình để dự đoán tự động

Sau khi đã huấn luyện và tối ưu hóa mô hình, chúng ta cần tạo một ứng dụng để tự động dự đoán doanh thu. Dưới đây là một ví dụ về cách tạo một dịch vụ dự đoán tự động:

import pandas as pd
import joblib
import pyodbc
import numpy as np
from datetime import datetime, timedelta
import schedule
import time

def predict_revenue():
    # Kết nối đến SQL Server
    conn = pyodbc.connect('DRIVER={SQL Server};SERVER=yourserver;DATABASE=yourdatabase;UID=youruser;PWD=yourpassword')
    cursor = conn.cursor()
    
    # Lấy dữ liệu mới nhất để dự đoán
    query_historical = """
    EXEC sp_LayDuLieuHuanLuyen @TuNgay = DATEADD(DAY, -90, GETDATE()), @DenNgay = GETDATE()
    """
    df_historical = pd.read_sql(query_historical, conn)
    
    # Tạo dữ liệu cho dự đoán 30 ngày tiếp theo
    today = datetime.now()
    prediction_dates = [today + timedelta(days=i) for i in range(1, 31)]
    
    # Tạo DataFrame cho dự đoán
    prediction_data = []
    for date in prediction_dates:
        # Lấy dữ liệu trung bình của các ngày tương tự trong quá khứ
        similar_days_query = f"""
        SELECT AVG(TongDoanhThu) as AvgRevenue, AVG(TongSoLuong) as AvgQuantity, 
               AVG(TongChiPhiMarketing) as AvgMarketing, AVG(GiaTrungBinh) as AvgPrice
        FROM DuLieuDoanhThu
        WHERE DATEPART(dw, NgayThang) = {date.weekday() + 1}
        AND MONTH(NgayThang) = {date.month}
        """
        avg_data = pd.read_sql(similar_days_query, conn)
        
        # Thêm vào dữ liệu dự đoán
        for category in df_historical['DanhMuc'].unique():
            for region in df_historical['KhuVuc'].unique():
                for channel in df_historical['Kenh'].unique():
                    prediction_data.append({
                        'Nam': date.year,
                        'Thang': date.month,
                        'Ngay': date.day,
                        'ThuTrongTuan': date.weekday() + 1,
                        'DanhMuc': category,
                        'KhuVuc': region,
                        'Kenh': channel,
                        'TongSoLuong': avg_data['AvgQuantity'].values[0],
                        'TongChiPhiMarketing': avg_data['AvgMarketing'].values[0],
                        'GiaTrungBinh': avg_data['AvgPrice'].values[0],
                        'NgayLe': 0,  # Cập nhật nếu là ngày lễ
                        'TyLeChiPhiMarketing': avg_data['AvgMarketing'].values[0] / max(1, avg_data['AvgRevenue'].values[0]),
                        'MuaVu': 'Xuan' if date.month in [1, 2, 3] else 
                                 'Ha' if date.month in [4, 5, 6] else 
                                 'Thu' if date.month in [7, 8, 9] else 'Dong'
                    })
    
    df_prediction = pd.DataFrame(prediction_data)
    
    # Tải mô hình và các công cụ xử lý dữ liệu
    model = joblib.load('optimized_model.pkl')
    scaler = joblib.load('scaler.pkl')
    encoder = joblib.load('encoder.pkl')
    
    # Xử lý dữ liệu dự đoán giống như dữ liệu huấn luyện
    categorical_cols = ['DanhMuc', 'KhuVuc', 'Kenh', 'MuaVu']
    encoded_features = encoder.transform(df_prediction[categorical_cols])
    encoded_feature_names = encoder.get_feature_names_out(categorical_cols)
    
    encoded_df = pd.DataFrame(encoded_features, columns=encoded_feature_names)
    df_final = pd.concat([df_prediction.drop(categorical_cols, axis=1).reset_index(drop=True), 
                        encoded_df.reset_index(drop=True)], axis=1)
    
    # Chuẩn hóa dữ liệu
    X_pred = scaler.transform(df_final)
    
    # Dự đoán
    y_pred = model.predict(X_pred)
    
    # Gắn kết quả dự đoán vào dữ liệu
    df_prediction['DoanhThuDuDoan'] = y_pred
    
    # Lưu kết quả dự đoán vào SQL Server
    for index, row in df_prediction.iterrows():
        insert_query = f"""
        INSERT INTO DuLieuDuDoanDoanhThu (
            NgayDuDoan, NgayThang, DanhMuc, KhuVuc, Kenh, DoanhThuDuDoan
        ) VALUES (
            GETDATE(), 
            '{row['Nam']}-{row['Thang']}-{row['Ngay']}', 
            '{row['DanhMuc']}', 
            '{row['KhuVuc']}', 
            '{row['Kenh']}', 
            {row['DoanhThuDuDoan']}
        )
        """
        cursor.execute(insert_query)
    
    conn.commit()
    conn.close()
    
    print(f"Dự đoán doanh thu hoàn tất vào: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")

# Lập lịch chạy dự đoán mỗi tuần
schedule.every().monday.at("01:00").do(predict_revenue)

# Chạy một lần để kiểm tra
predict_revenue()

# Vòng lặp chờ lịch
while True:
    schedule.run_pending()
    time.sleep(60)

6. Tạo báo cáo tự động với kết quả dự đoán

Để trình bày kết quả dự đoán một cách trực quan, chúng ta có thể sử dụng thư viện matplotlib và seaborn để tạo các biểu đồ và sau đó gửi báo cáo qua email:

import matplotlib.pyplot as plt
import seaborn as sns
import pandas as pd
import pyodbc
from datetime import datetime
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.image import MIMEImage

def generate_forecast_report():
    # Kết nối đến SQL Server
    conn = pyodbc.connect('DRIVER={SQL Server};SERVER=yourserver;DATABASE=yourdatabase;UID=youruser;PWD=yourpassword')
    
    # Lấy dữ liệu dự đoán gần nhất
    query = """
    SELECT 
        NgayThang, DanhMuc, KhuVuc, Kenh, DoanhThuDuDoan
    FROM 
        DuLieuDuDoanDoanhThu
    WHERE 
        NgayDuDoan = (SELECT MAX(NgayDuDoan) FROM DuLieuDuDoanDoanhThu)
    ORDER BY 
        NgayThang
    """
    df = pd.read_sql(query, conn)
    
    # Tổng hợp dữ liệu theo ngày
    daily_forecast = df.groupby('NgayThang')['DoanhThuDuDoan'].sum().reset_index()
    
    # Tổng hợp dữ liệu theo danh mục
    category_forecast = df.groupby('DanhMuc')['DoanhThuDuDoan'].sum().reset_index()
    
    # Tổng hợp dữ liệu theo khu vực
    region_forecast = df.groupby('KhuVuc')['DoanhThuDuDoan'].sum().reset_index()
    
    # Tạo biểu đồ dự báo theo thời gian
    plt.figure(figsize=(12, 6))
    plt.plot(daily_forecast['NgayThang'], daily_forecast['DoanhThuDuDoan'], marker='o')
    plt.title('Dự báo doanh thu trong 30 ngày tới')
    plt.xlabel('Ngày')
    plt.ylabel('Doanh thu dự báo')
    plt.grid(True)
    plt.tight_layout()
    plt.savefig('daily_forecast.png')
    
    # Tạo biểu đồ dự báo theo danh mục
    plt.figure(figsize=(10, 6))
    sns.barplot(x='DanhMuc', y='DoanhThuDuDoan', data=category_forecast)
    plt.title('Dự báo doanh thu theo danh mục sản phẩm')
    plt.xlabel('Danh mục')
    plt.ylabel('Tổng doanh thu dự báo')
    plt.xticks(rotation=45)
    plt.tight_layout()
    plt.savefig('category_forecast.png')
    
    # Tạo biểu đồ dự báo theo khu vực
    plt.figure(figsize=(10, 6))
    sns.barplot(x='KhuVuc', y='DoanhThuDuDoan', data=region_forecast)
    plt.title('Dự báo doanh thu theo khu vực')
    plt.xlabel('Khu vực')
    plt.ylabel('Tổng doanh thu dự báo')
    plt.tight_layout()
    plt.savefig('region_forecast.png')
    
    # Gửi báo cáo qua email
    send_report_email()
    
    print("Báo cáo dự báo đã được tạo và gửi thành công.")

def send_report_email():
    # Cấu hình email
    sender_email = "your_email@gmail.com"
    receiver_email = "recipient@company.com"
    password = "your_password"
    
    # Tạo tin nhắn
    msg = MIMEMultipart()
    msg['Subject'] = f'Báo cáo dự báo doanh thu - {datetime.now().strftime("%Y-%m-%d")}'
    msg['From'] = sender_email
    msg['To'] = receiver_email
    
    # Thêm nội dung
    text = '''
    Kính gửi Ban lãnh đạo,
    
    Đính kèm là báo cáo dự báo doanh thu 30 ngày tới được tạo tự động bởi hệ thống ML của chúng ta.
    
    Các điểm chính:
    - Dự báo doanh thu tổng cộng trong 30 ngày tới
    - Phân tích doanh thu theo danh mục sản phẩm
    - Phân tích doanh thu theo khu vực
    
    Xem file đính kèm để biết chi tiết.
    
    Trân trọng,
    Hệ thống dự báo tự động
    '''
    msg.attach(MIMEText(text, 'plain'))
    
    # Đính kèm hình ảnh
    for image_file in ['daily_forecast.png', 'category_forecast.png', 'region_forecast.png']:
        with open(image_file, 'rb') as fp:
            img = MIMEImage(fp.read())
            img.add_header('Content-Disposition', 'attachment', filename=image_file)
            msg.attach(img)
    
    # Gửi email
    with smtplib.SMTP_SSL('smtp.gmail.com', 465) as server:
        server.login(sender_email, password)
        server.send_message(msg)

# Chạy báo cáo
generate_forecast_report()

Kết luận

Kết hợp SQL Server và các mô hình Machine Learning trong Python tạo nên một hệ thống dự đoán doanh thu mạnh mẽ, có khả năng tự động hóa cao. Hệ thống này không chỉ giúp doanh nghiệp dự đoán doanh số trong tương lai mà còn cung cấp những thông tin chi tiết về xu hướng doanh thu theo nhiều chiều khác nhau như danh mục sản phẩm, khu vực địa lý, kênh bán hàng.

Với việc tự động hóa toàn bộ quy trình từ thu thập dữ liệu, huấn luyện mô hình, đến tạo báo cáo, doanh nghiệp có thể tiết kiệm thời gian và nguồn lực đáng kể, đồng thời nâng cao khả năng ra quyết định dựa trên dữ liệu.

Bằng cách liên tục cải thiện và tối ưu hóa mô hình qua thời gian, doanh nghiệp sẽ có được một công cụ dự báo ngày càng chính xác, hỗ trợ đắc lực cho việc lập kế hoạch kinh doanh và chiến lược phát triển trong tương lai. 1

/* Tối ưu font, khoảng cách và màu chủ đạo */ body { font-family: 'Inter', sans-serif; color: #2e3a59; } h1, h2, h3 { color: #2a7a4d; /* màu xanh giống Docusaurus */ font-weight: 700; } a { color: #2a7a4d; text-decoration: none; } a:hover { text-decoration: underline; } /* Bo tròn và đổ bóng cho khối nội dung */ .card, .oe_structure { border-radius: 12px; box-shadow: 0 4px 12px rgba(0,0,0,0.05); padding: 1.5rem; }