Phân tích dữ liệu thương mại điện tử, nơi kỳ lân bắt đầu - Phần 1

Trong bài này mình chỉ trình bày về cấu trúc dữ liệu và cách thiết kế cơ sở dữ liệu từ một business. Bạn xem tiếp những phân tích insights cũng như tạo reports trong những bài viết tiếp theo nhé.

Learning points

1. Mô hình hoá hoạt động của một doanh nghiệp
2. Thiết kế cơ sở dữ liệu sử dụng PgAdmin
3. Cấu hình cơ sở dữ liệu và load dữ liệu
4. Làm quen với các câu lệnh DDL & DML cơ bản

Tải dữ liệu

1. Dữ liệu mình tổng hợp được share trong phần Archive trên trang web này. Dữ liệu của series này mình share trong bài [SQLInUse] Ecommerce I Dataset
2. Hoặc bạn có thể truy cập thẳng Redash để thực hành SQL và tải dữ liệu. Chọn [SQLInUse] - Ecommerce I - PostgreSQL data source nhé.

Mô hình hoá business (Data modeling)

Để mô hình hoá được hoạt động của một business, đơn giản ta cần nhìn vào cách mà nó hoạt động, khái quát hoá các đối tượng liên quan cùng những thuộc tính của các đối tượng này. Các đối tượng trong mô hình luôn có các mối quan hệ qua lại với nhau, cần phải xác định rõ để đảm bảo tính toàn vẹn của dữ liệu và thoả mãn các yêu cầu của business. Làm rõ các điểm này từ đầu sẽ giúp cho quá trình trích xuất và quản lý sau này dễ dàng.

Một business về thương mại điện tử cơ bản có

1. sản phẩm mà họ muốn bán
2. khách hàng cần mua những sản phẩm đó và
3. những đơn hàng thể hiện hành động mua bán xảy ra

Ngoài ra trước và sau khi đơn hàng được xác nhận, các hoạt động khác như marketing để thu hút khách hàng, giao dịch thanh toán qua ngân hàng, quá trình giao nhận hàng của các bên vận chuyển, đánh giá review sản phẩm, đổi trả sản phẩm và chăm sóc khách hàng cũng cần được ghi nhận và mô hình hoá. Nhưng để đơn giản, trong series này, mình chỉ làm việc với 3 đối tượng mà mình highlight ở trên.

Các đối tượng này cũng có rất nhiều thuộc tính khác nhau có thể quan sát và ghi nhận được tuỳ vào mô hình của doanh nghiệp. Một mô hình đơn giản nhất chúng ta có thể có

1. sản phẩm
   1. mã sản phẩm (int)
   2. giá sản phẩm (float)
   3. hàng tồn kho (int)
   4. tình trạng sản phẩm (enum/text)
2. khách hàng
   1. mã khách hàng (int)
   2. ngày tham gia (timestamp)
   3. kênh tham gia (enum/text)
3. đơn hàng
   1. mã đơn hàng (int)
   2. mã khách mua hàng (int)
   3. mã sản phẩm (int)
   4. thời gian mua (timestamp)
   5. giá bán (float)
   6. số lượng bán (int)

Từ những yêu cầu của business như trên, mình cần thiết kế một hệ thống cơ sở dữ liệu để đảm bảo dữ liệu được lưu trữ, cập nhật và trích xuất hiệu quả.

Thiết kế cơ sở dữ liệu (Database design)

1. Khởi tạo cơ sở dữ liệu

Sau khi có ý tưởng vận hành của business và phát thảo sơ bộ về một cơ sở dữ liệu để chứa thông tin hoạt động của doanh nghiệp, mình cần vẽ được ERD (Entity Relation Diagram) và cấu hình được mối liên hệ giữa các đối tượng. Ở đây mình sử dụng PgAdmin để soạn thảo ERD.

Thiết kế cơ sở dữ liệu là phần khá quan trọng, cơ sở dữ liệu nhất thiết phải đảm bảo các nguyên tắc cơ bản như

• Integrity - tính toàn vẹn
• Normalization - tính chuẩn hoá
• Scalability & Flexibility - khả năng mở rộng và dễ thay đổi
• Performance - hiệu suất cao

Những thứ này sẽ trở nên rất phức tạp nếu bạn có một mô hình kinh doanh phức tạp, thực hành nhiều sẽ giúp cải thiện kỹ năng thiết kế này.

Từ ERD, mình có thể xuất các lệnh DDL (CREATE, DROP, ALTER , etc.) truy vấn để khởi tạo bảng cho các đối tượng trên. Và để tối ưu tốc độ truy vấn trên các bảng, mình cũng có thể tuỳ chỉnh thêm các indices của từng bảng như trong các câu truy vấn sau

-- This script was generated by the ERD tool in pgAdmin 4.
-- Please log an issue at https://redmine.postgresql.org/projects/pgadmin4/issues/new if you find any bugs, including reproduction steps.
BEGIN;


CREATE TABLE IF NOT EXISTS ecommerce.products
(
    id serial NOT NULL,
    inventory integer NOT NULL DEFAULT 0,
    price double precision NOT NULL DEFAULT 0,
    status text,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS ecommerce.customers
(
    id serial NOT NULL,
    created_at timestamp without time zone NOT NULL DEFAULT now(),
    channel text,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS ecommerce.orders
(
    id serial NOT NULL,
    product_id integer NOT NULL,
    customer_id integer NOT NULL,
    price double precision NOT NULL,
    quantity integer NOT NULL DEFAULT 0,
    created_at timestamp without time zone NOT NULL DEFAULT now(),
    PRIMARY KEY (id)
);

ALTER TABLE IF EXISTS ecommerce.orders
    ADD FOREIGN KEY (product_id)
    REFERENCES ecommerce.products (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;


ALTER TABLE IF EXISTS ecommerce.orders
    ADD FOREIGN KEY (customer_id)
    REFERENCES ecommerce.customers (id) MATCH SIMPLE
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
    NOT VALID;

CREATE INDEX IF NOT EXISTS idx_orders_customer_id ON orders(customer_id);
CREATE INDEX IF NOT EXISTS idx_orders_product_id ON orders(product_id);
CREATE INDEX IF NOT EXISTS idx_orders_created_at ON orders(created_at);
CREATE INDEX IF NOT EXISTS idx_customers_created_at ON customers(created_at);

END;

2. Load dữ liệu lên cơ sở dữ liệu

Có nhiều cách để load .csv files lên một cơ sở dữ liệu. Ở đây mình giới thiệu hai cách đơn giản mình thường làm nhất trên PostgreSQL.

1. Sử dụng câu lệnh DML INSERT. Có thể insert 1 hoặc nhiều records một lúc lên cơ sở dữ liệu.
2. Hoặc sau khi login vào cơ sở dữ liệu, sử dụng lệnh COPY để copy trực tiếp .csv files vào bảng đã tạo

INSERT INTO customers (id, created_at, channel)
VALUES
    (1, '2025-01-06 12:00:00', 'Facebook'),
    (2, '2025-01-06 12:01:00', 'Youtube');

\copy customers from '/tmp/customers.csv' DELIMITER ',' CSV HEADER;
\copy products from '/tmp/products.csv' DELIMITER ',' CSV HEADER;
\copy orders from '/tmp/orders.csv' DELIMITER ',' CSV HEADER;

Demo phân tích

Một câu truy vấn đơn giản để trích xuất doanh thu theo tháng của doanh nghiệp.

select
  date_trunc('month', created_at) as date,
  sum(price * quantity) as revenue
from
  orders
group by
  1