Phân tích và xử lý dữ liệu thương mại điện tử

Bài viết này trình bày quy trình xử lý dữ liệu từ một nền tảng thương mại điện tử đã được ẩn danh, bao gồm việc nhập dữ liệu vào cơ sở dữ liệu, thực hiện các phép biến đổi và truy vấn dữ liệu liên quan. Dữ liệu ban đầu bao gồm chín bảng, mỗi bảng chứa thông tin về người mua, người bán, và hành vi mua hàng.

Phân loại cấu trúc dữ liệu Để quản lý hiệu quả, chín bảng dữ liệu được phân loại như sau:

Dữ liệu người mua (3 bảng):

UserInfo.csv: Thông tin cơ bản về người dùng. RegionInfo.csv: Thông tin phân loại hành chính quốc gia. UserAddress.csv: Địa chỉ nhận hàng của người dùng.

Dữ liệu người bán (4 bảng):

GoodsInfo.csv: Thông tin chi tiết về sản phẩm. GoodsBrand.csv: Thông tin về thương hiệu sản phẩm. GoodsColor.csv: Thông tin về màu sắc sản phẩm. GoodsSize.csv: Thông tin về kích cỡ sản phẩm.

Dữ liệu hành vi mua hàng (2 bảng):

OrderInfo.csv: Thông tin chính về đơn hàng (kết hợp ID đơn hàng và ID sản phẩm). OrderDetail.csv: Chi tiết đơn hàng (kết hợp ID đơn hàng).

Quan hệ giữa các bảng Việc hiểu rõ mối quan hệ giữa các bảng là yếu tố then chốt. Các mối quan hệ chủ yếu là "một-nhiều" (one-to-many), đòi hỏi xác định đúng các trường khóa chính và khóa ngoại để thực hiện truy vấn đa bảng chính xác. Các câu hỏi cần đặt ra trước khi truy vấn bao gồm:

Dữ liệu cần lấy là gì? Dữ liệu đó được lưu trữ ở bảng nào? Làm thế nào để trích xuất dữ liệu, sử dụng truy vấn đa bảng, và chọn trường khóa phù hợp? Mối quan hệ của các trường khóa này ảnh hưởng như thế nào đến kết quả truy vấn?

Thiết lập cơ sở dữ liệu và nhập dữ liệu Quy trình này sử dụng MySQL 5.7 và Workbench 6.3. Các bước bao gồm: 3.1. Tạo cơ sở dữ liệu

-- Tạo cơ sở dữ liệu
CREATE DATABASE ecommerce_data;

-- Sử dụng cơ sở dữ liệu
USE ecommerce_data;

3.2. Xử lý bảng UserInfo Các trường thời gian như regtime và lastlogin ban đầu được lưu dưới dạng chuỗi (timestamp) và cần được chuyển đổi sang định dạng datetime.

-- Tạo bảng userinfo
CREATE TABLE userinfo (
    userid VARCHAR(6) NOT NULL DEFAULT '-',
    username VARCHAR(20) NOT NULL DEFAULT '-',
    userpassword VARCHAR(100) NOT NULL DEFAULT '-',
    sex INT NOT NULL DEFAULT 0,
    usermoney INT NOT NULL DEFAULT 0,
    frozenmoney INT NOT NULL DEFAULT 0,
    addressid VARCHAR(20) NOT NULL DEFAULT '-',
    regtime VARCHAR(20) NOT NULL DEFAULT '-', -- Lưu timestamp dạng chuỗi
    lastlogin VARCHAR(20) NOT NULL DEFAULT '-', -- Lưu timestamp dạng chuỗi
    lasttime DATE NOT NULL
);

-- Nhập dữ liệu từ file CSV
LOAD DATA LOCAL INFILE 'D:/rawdata/UserInfo.csv'
INTO TABLE userinfo
FIELDS TERMINATED BY ','
IGNORE 1 LINES;

-- Kiểm tra dữ liệu và cấu trúc bảng
SELECT * FROM userinfo;
SELECT COUNT(*) FROM userinfo;
DESC userinfo;

-- Vô hiệu hóa kiểm tra an toàn để cập nhật
SET SQL_SAFE_UPDATES = 0;

-- Thêm cột datetime mới và chuyển đổi dữ liệu timestamp
ALTER TABLE userinfo ADD COLUMN regtime_ DATETIME NOT NULL;
UPDATE userinfo SET regtime_ = FROM_UNIXTIME(regtime);

ALTER TABLE userinfo ADD COLUMN lastlogin_ DATETIME NOT NULL;
UPDATE userinfo SET lastlogin_ = FROM_UNIXTIME(lastlogin);

-- Xóa các cột timestamp dạng chuỗi cũ
ALTER TABLE userinfo DROP COLUMN regtime;
ALTER TABLE userinfo DROP COLUMN lastlogin;

DESC userinfo;

3.3. Xử lý bảng RegionInfo Trường pt ban đầu là chuỗi, cần được định dạng lại thành kiểu datetime.

CREATE TABLE regioninfo (
    regionid VARCHAR(4) NOT NULL DEFAULT '-',
    parentid VARCHAR(4) NOT NULL DEFAULT '-',
    regionname VARCHAR(20) NOT NULL DEFAULT '-',
    regiontype INT NOT NULL DEFAULT 0,
    agencyid INT NOT NULL DEFAULT 0,
    pt VARCHAR(9) NOT NULL DEFAULT '-' -- Lưu dạng chuỗi
);

LOAD DATA LOCAL INFILE 'D:/rawdata/regioninfo.csv'
INTO TABLE regioninfo
FIELDS TERMINATED BY ','
IGNORE 1 LINES;

SELECT * FROM regioninfo;

-- Trích xuất 8 ký tự đầu từ trường pt và định dạng lại
UPDATE regioninfo SET pt = SUBSTRING(pt, 1, 8);

ALTER TABLE regioninfo ADD COLUMN pt_ DATETIME NOT NULL;
UPDATE regioninfo SET pt_ = DATE_FORMAT(pt, '%y-%m-%d');

ALTER TABLE regioninfo DROP COLUMN pt;

DESC regioninfo;

3.4. Xử lý bảng UserAddress Tương tự, trường pt được xử lý thành định dạng datetime.

CREATE TABLE useraddress (
    addressid VARCHAR(5) NOT NULL DEFAULT '-',
    userid VARCHAR(6) NOT NULL DEFAULT '-',
    consignee VARCHAR(50) NOT NULL DEFAULT '-',
    country VARCHAR(1) NOT NULL DEFAULT '-',
    province VARCHAR(2) NOT NULL DEFAULT '-',
    city VARCHAR(4) NOT NULL DEFAULT '-',
    district VARCHAR(4) NOT NULL DEFAULT '-',
    address VARCHAR(200) NOT NULL DEFAULT '-',
    pt VARCHAR(10) NOT NULL DEFAULT '-' -- Lưu dạng chuỗi
);

LOAD DATA LOCAL INFILE 'D:/rawdata/UserAddress.csv'
INTO TABLE useraddress
FIELDS TERMINATED BY ','
IGNORE 1 LINES;

UPDATE useraddress SET pt = SUBSTRING(pt, 1, 8);

ALTER TABLE useraddress ADD COLUMN pt_ DATETIME NOT NULL;
UPDATE useraddress SET pt_ = DATE_FORMAT(pt, '%y-%m-%d');

ALTER TABLE useraddress DROP COLUMN pt;

SELECT COUNT(*) FROM useraddress;
SELECT * FROM useraddress;

3.5. Xử lý bảng GoodsInfo Các trường addtime và pt được chuyển đổi sang định dạng datetime.

CREATE TABLE goodsinfo (
    goodsid VARCHAR(6) NOT NULL DEFAULT '-',
    typeid VARCHAR(3) NOT NULL DEFAULT '-',
    markid VARCHAR(4) NOT NULL DEFAULT '-',
    goodstag VARCHAR(100) NOT NULL DEFAULT '-',
    brandtag VARCHAR(100) NOT NULL DEFAULT '-',
    customtag VARCHAR(100) NOT NULL DEFAULT '-',
    goodsname VARCHAR(100) NOT NULL DEFAULT '-',
    clickcount INT NOT NULL DEFAULT 0,
    clickcr INT NOT NULL DEFAULT 0,
    goodsnumber INT NOT NULL DEFAULT 0,
    goodsweight INT NOT NULL DEFAULT 0,
    marketprice DOUBLE NOT NULL DEFAULT 0,
    shopprice DOUBLE NOT NULL DEFAULT 0,
    addtime VARCHAR(20) NOT NULL DEFAULT '0', -- Lưu timestamp dạng chuỗi
    isonsale INT NOT NULL DEFAULT 0,
    sales INT NOT NULL DEFAULT 0,
    realsales INT NOT NULL DEFAULT 0,
    extraprice DOUBLE NOT NULL DEFAULT 0,
    goodsno VARCHAR(9) NOT NULL DEFAULT '0',
    pt VARCHAR(9) NOT NULL DEFAULT '0' -- Lưu dạng chuỗi
);

LOAD DATA LOCAL INFILE 'D:/rawdata/GoodsInfo.csv'
INTO TABLE goodsinfo
FIELDS TERMINATED BY ','
IGNORE 1 LINES;

SELECT * FROM goodsinfo;

ALTER TABLE goodsinfo ADD COLUMN addtime_ DATETIME NOT NULL;
UPDATE goodsinfo SET addtime_ = FROM_UNIXTIME(addtime);
ALTER TABLE goodsinfo DROP COLUMN addtime;

UPDATE goodsinfo SET pt = SUBSTRING(pt, 1, 8);
ALTER TABLE goodsinfo ADD COLUMN pt_ DATETIME NOT NULL;
UPDATE goodsinfo SET pt_ = DATE_FORMAT(pt, '%y-%m-%d');
ALTER TABLE goodsinfo DROP COLUMN pt;

3.6. Xử lý bảng GoodsBrand Trường pt được chuyển đổi sang định dạng datetime.

CREATE TABLE goodsbrand (
    SupplierID VARCHAR(4) NOT NULL DEFAULT '-',
    BrandType VARCHAR(100) NOT NULL DEFAULT '-',
    pt VARCHAR(9) NOT NULL DEFAULT '-' -- Lưu dạng chuỗi
);

LOAD DATA LOCAL INFILE 'D:/rawdata/GoodsBrand.csv'
INTO TABLE goodsbrand
FIELDS TERMINATED BY ','
IGNORE 1 LINES;

SELECT * FROM goodsbrand;

UPDATE goodsbrand SET pt = SUBSTRING(pt, 1, 8);
ALTER TABLE goodsbrand ADD COLUMN pt_ DATETIME NOT NULL;
UPDATE goodsbrand SET pt_ = DATE_FORMAT(pt, '%y-%m-%d');
ALTER TABLE goodsbrand DROP COLUMN pt;

3.7. Xử lý bảng GoodsColor Trường pt được chuyển đổi sang định dạng datetime.

CREATE TABLE goodscolor (
    ColorID VARCHAR(4) NOT NULL DEFAULT '-',
    ColorNote VARCHAR(20) NOT NULL DEFAULT '-',
    ColorSort INT NOT NULL DEFAULT 0,
    pt VARCHAR(9) NOT NULL DEFAULT '-' -- Lưu dạng chuỗi
);

LOAD DATA LOCAL INFILE 'D:/rawdata/goodscolor.csv'
INTO TABLE goodscolor
FIELDS TERMINATED BY ','
IGNORE 1 LINES;

SELECT * FROM goodscolor;

UPDATE goodscolor SET pt = SUBSTRING(pt, 1, 8);
ALTER TABLE goodscolor ADD COLUMN pt_ DATETIME NOT NULL;
UPDATE goodscolor SET pt_ = DATE_FORMAT(pt, '%y-%m-%d');
ALTER TABLE goodscolor DROP COLUMN pt;

3.8. Xử lý bảng GoodsSize Trường pt được chuyển đổi sang định dạng datetime.

CREATE TABLE goodssize (
    SizeID VARCHAR(4) NOT NULL DEFAULT '-',
    SizeNote VARCHAR(100) NOT NULL DEFAULT '-',
    SizeSort INT NOT NULL DEFAULT 0,
    pt VARCHAR(9) NOT NULL DEFAULT '-' -- Lưu dạng chuỗi
);

LOAD DATA LOCAL INFILE 'D:/rawdata/goodssize.csv'
INTO TABLE goodssize
FIELDS TERMINATED BY ','
IGNORE 1 LINES;

SELECT * FROM goodssize;

UPDATE goodssize SET pt = SUBSTRING(pt, 1, 8);
ALTER TABLE goodssize ADD COLUMN pt_ DATETIME NOT NULL;
UPDATE goodssize SET pt_ = DATE_FORMAT(pt, '%y-%m-%d');
ALTER TABLE goodssize DROP COLUMN pt;

3.9. Xử lý bảng OrderInfo Các trường PayTime và AddTime dạng chuỗi timestamp được chuyển đổi sang datetime.

CREATE TABLE orderinfo (
    OrderID VARCHAR(6) NOT NULL DEFAULT '-',
    UserID VARCHAR(10) NOT NULL DEFAULT '-',
    OrderState INT NOT NULL DEFAULT 0,
    PayState INT NOT NULL DEFAULT 0,
    AllotStatus INT NOT NULL DEFAULT 0,
    Consignee VARCHAR(100) NOT NULL DEFAULT '-',
    Country INT NOT NULL DEFAULT 0,
    Province INT NOT NULL DEFAULT 0,
    City INT NOT NULL DEFAULT 0,
    District INT NOT NULL DEFAULT 0,
    Address VARCHAR(100) NOT NULL DEFAULT '0',
    GoodsAmount DOUBLE NOT NULL DEFAULT 0,
    OrderAmount DOUBLE NOT NULL DEFAULT 0,
    ShippingFee INT NOT NULL DEFAULT 0,
    RealShippingFee INT NOT NULL DEFAULT 0,
    PayTool INT NOT NULL DEFAULT 0,
    IsBalancePay INT NOT NULL DEFAULT 0,
    BalancePay DOUBLE NOT NULL DEFAULT 0,
    OtherPay DOUBLE NOT NULL DEFAULT 0,
    PayTime VARCHAR(20) NOT NULL DEFAULT '0', -- Lưu timestamp dạng chuỗi
    AddTime VARCHAR(20) NOT NULL DEFAULT '0' -- Lưu timestamp dạng chuỗi
);

LOAD DATA LOCAL INFILE 'D:/rawdata/OrderInfo.csv'
INTO TABLE orderinfo
FIELDS TERMINATED BY ','
IGNORE 1 LINES;

SELECT * FROM orderinfo;

ALTER TABLE orderinfo ADD COLUMN PayTime_ DATETIME NOT NULL;
UPDATE orderinfo SET PayTime_ = FROM_UNIXTIME(PayTime);

ALTER TABLE orderinfo ADD COLUMN AddTime_ DATETIME NOT NULL;
UPDATE orderinfo SET AddTime_ = FROM_UNIXTIME(AddTime);

ALTER TABLE orderinfo DROP COLUMN PayTime;
ALTER TABLE orderinfo DROP COLUMN AddTime;

3.10. Xử lý bảng OrderDetail Nhập dữ liệu chi tiết đơn hàng.

CREATE TABLE orderdetail (
    RecID VARCHAR(7) NOT NULL DEFAULT '-',
    OrderID VARCHAR(6) NOT NULL DEFAULT '-',
    UserID VARCHAR(6) NOT NULL DEFAULT '-',
    SpecialID VARCHAR(6) NOT NULL DEFAULT '-',
    GoodsID VARCHAR(6) NOT NULL DEFAULT '-',
    GoodsPrice DOUBLE NOT NULL DEFAULT 0,
    ColorID VARCHAR(4) NOT NULL DEFAULT '-',
    SizeID VARCHAR(4) NOT NULL DEFAULT '-',
    Amount INT NOT NULL DEFAULT 0
);

LOAD DATA LOCAL INFILE 'D:/rawdata/OrderDetail.csv'
INTO TABLE orderdetail
FIELDS TERMINATED BY ','
IGNORE 1 LINES;

SELECT * FROM orderdetail;
SELECT COUNT(*) FROM orderdetail;

3.11. Kiểm tra tổng quan số lượng bản ghi

SELECT COUNT(*) FROM userinfo;      -- 1000
SELECT COUNT(*) FROM RegionInfo;    -- 3415
SELECT COUNT(*) FROM useraddress;   -- 10000
SELECT COUNT(*) FROM goodsinfo;     -- 10000
SELECT COUNT(*) FROM goodsbrand;    -- 64
SELECT COUNT(*) FROM goodscolor;    -- 2641
SELECT COUNT(*) FROM goodssize;     -- 289
SELECT COUNT(*) FROM orderinfo;     -- 3711
SELECT COUNT(*) FROM orderdetail;   -- 10000

Thống kê và truy vấn dữ liệu

4.1. Top 10 khách hàng có tổng giá trị đơn hàng cao nhất

SELECT
    userid AS KhachHang,
    SUM(orderamount) AS TongGiaTriDonHang
FROM
    orderinfo
GROUP BY
    KhachHang
ORDER BY
    TongGiaTriDonHang DESC
LIMIT 10;

4.2. Thời gian đăng nhập cuối cùng của top 10 khách hàng có tổng giá trị đơn hàng cao nhất

SELECT
    oi.userid AS KhachHang,
    SUM(oi.orderamount) AS TongGiaTriDonHang,
    ui.lastlogin_ AS ThoiGianDangNhapCuoi
FROM
    orderinfo AS oi
LEFT JOIN
    userinfo AS ui ON oi.userid = ui.userid
GROUP BY
    oi.userid
ORDER BY
    TongGiaTriDonHang DESC
LIMIT 10;

4.3. Thành phố của top 10 khách hàng có tổng giá trị đơn hàng cao nhất Sử dụng regioninfo.regionname để lấy tên thành phố, liên kết qua orderinfo.city và regioninfo.regionid.

SELECT
    oi.userid AS KhachHang,
    SUM(oi.orderamount) AS TongGiaTriDonHang,
    ri.regionname AS ThanhPho
FROM
    orderinfo AS oi
LEFT JOIN
    regioninfo AS ri ON oi.city = ri.regionid
GROUP BY
    oi.userid
ORDER BY
    TongGiaTriDonHang DESC
LIMIT 10;

4.4. Top 10 thành phố có sức mua cao nhất

SELECT
    ri.regionname AS ThanhPho,
    SUM(oi.orderamount) AS TongGiaTriDonHang
FROM
    orderinfo AS oi
LEFT JOIN
    regioninfo AS ri ON oi.city = ri.regionid
GROUP BY
    ri.regionname
ORDER BY
    TongGiaTriDonHang DESC
LIMIT 10;

4.5. Top 10 thành phố có sức mua cao nhất và tỉnh tương ứng Tạo bảng tạm để lưu trữ top 10 thành phố và mã tỉnh, sau đó kết hợp với bảng regioninfo để lấy tên tỉnh.

-- Tạo bảng tạm lưu top 10 thành phố và mã tỉnh
CREATE TEMPORARY TABLE top_cities_province AS
SELECT
    ri.regionname AS ThanhPho,
    SUM(oi.orderamount) AS TongGiaTriDonHang,
    ri.parentid AS MaTinh -- parentid của thành phố là ID của tỉnh
FROM
    orderinfo AS oi
LEFT JOIN
    regioninfo AS ri ON oi.city = ri.regionid
GROUP BY
    ri.regionname
ORDER BY
    TongGiaTriDonHang DESC
LIMIT 10;

-- Lấy tên tỉnh từ bảng regioninfo dựa trên MaTinh
SELECT
    rp.regionname AS Tinh,
    tcp.ThanhPho,
    tcp.TongGiaTriDonHang
FROM
    top_cities_province AS tcp
LEFT JOIN
    regioninfo AS rp ON tcp.MaTinh = rp.regionid
ORDER BY
    tcp.TongGiaTriDonHang DESC;

4.6. Top 10 thương hiệu bán chạy nhất Kết hợp orderdetail, goodsinfo và goodsbrand để tính toán.

SELECT
    gb.BrandType AS ThuongHieu,
    SUM(od.Amount) AS SoLuongBan
FROM
    orderdetail AS od
JOIN
    goodsinfo AS gi ON od.GoodsID = gi.GoodsID
JOIN
    goodsbrand AS gb ON gi.typeid = gb.SupplierID -- Giả định typeid trong goodsinfo là SupplierID trong goodsbrand
GROUP BY
    gb.BrandType
ORDER BY
    SoLuongBan DESC
LIMIT 10;

4.7. Top 10 màu sắc bán chạy nhất và top 10 màu sắc bán chậm nhất Sử dụng UNION để kết hợp hai truy vấn: một cho bán chạy và một cho bán chậm.

(SELECT
    gc.ColorNote AS MauSac,
    SUM(od.Amount) AS SoLuongBan
FROM
    orderdetail AS od
LEFT JOIN
    goodscolor AS gc ON od.ColorID = gc.ColorID
GROUP BY
    gc.ColorNote
ORDER BY
    SoLuongBan DESC
LIMIT 10)
UNION
(SELECT
    gc.ColorNote AS MauSac,
    SUM(od.Amount) AS SoLuongBan
FROM
    orderdetail AS od
LEFT JOIN
    goodscolor AS gc ON od.ColorID = gc.ColorID
GROUP BY
    gc.ColorNote
ORDER BY
    SoLuongBan ASC
LIMIT 10);

4.8. Doanh thu của tất cả các sản phẩm thuộc top 10 thương hiệu bán chạy nhất Chia thành các bước: tìm top 10 thương hiệu, tìm các sản phẩm thuộc các thương hiệu đó, cuối cùng tính doanh thu.

-- Bước 1: Xác định top 10 thương hiệu bán chạy nhất (dựa trên số lượng bán)
CREATE TEMPORARY TABLE top_selling_brands AS
SELECT
    gb.SupplierID AS BrandID
FROM
    orderdetail AS od
JOIN
    goodsinfo AS gi ON od.GoodsID = gi.GoodsID
JOIN
    goodsbrand AS gb ON gi.typeid = gb.SupplierID
GROUP BY
    gb.SupplierID
ORDER BY
    SUM(od.Amount) DESC
LIMIT 10;

-- Bước 2: Lấy tất cả các sản phẩm thuộc các thương hiệu này
CREATE TEMPORARY TABLE products_in_top_brands AS
SELECT
    gi.GoodsID,
    gi.GoodsName -- Thêm tên sản phẩm để dễ theo dõi
FROM
    goodsinfo AS gi
JOIN
    top_selling_brands AS tsb ON gi.typeid = tsb.BrandID;

-- Bước 3: Tính doanh thu cho các sản phẩm đã chọn
SELECT
    ptb.GoodsID,
    ptb.GoodsName,
    SUM(od.GoodsPrice * od.Amount) AS DoanhThu
FROM
    products_in_top_brands AS ptb
JOIN
    orderdetail AS od ON ptb.GoodsID = od.GoodsID
GROUP BY
    ptb.GoodsID, ptb.GoodsName
ORDER BY
    DoanhThu DESC;

4.9. Các thành phố nhận hàng của top 10 người dùng mua nhiều loại sản phẩm khác nhau nhất Sử dụng COUNT(DISTINCT GoodsID) để đếm số loại sản phẩm và GROUP_CONCAT(DISTINCT RegionName) để liệt kê các thành phố.

SELECT
    od.UserID AS NguoiDung,
    COUNT(DISTINCT od.GoodsID) AS SoLuongLoaiSanPham,
    GROUP_CONCAT(DISTINCT ri.regionname) AS CacThanhPhoNhanHang
FROM
    orderdetail AS od
LEFT JOIN
    orderinfo AS oi ON od.OrderID = oi.OrderID
LEFT JOIN
    regioninfo AS ri ON oi.City = ri.regionid
GROUP BY
    od.UserID
ORDER BY
    SoLuongLoaiSanPham DESC
LIMIT 10;

Thẻ: sql mysql Phân tích dữ liệu Thương mại điện tử Quản lý cơ sở dữ liệu

Đăng vào ngày 1 tháng 6 lúc 14:01

Thành phố Cuồng loạn

Phân tích và xử lý dữ liệu thương mại điện tử

Thẻ Phổ Biến