E-commerce SQL Portfolio Project

โปรเจกต์จำลองระบบฐานข้อมูล ร้านค้าออนไลน์ (E-commerce) สำหรับใช้โชว์พอร์ตโฟลิโอ/สมัครงานสาย Data/Backend ออกแบบให้ครอบคลุมทั้ง database design, data generation, และ SQL query ระดับที่ใช้ในงานจริง

ทุกไฟล์ในโปรเจกต์นี้ ทดสอบรันจริงบน PostgreSQL 16 แล้ว ไม่มี error

โครงสร้างไฟล์

ไฟล์	รายละเอียด
schema.sql	(PostgreSQL) CREATE TABLE ครบ 11 ตาราง พร้อม constraints, indexes, trigger, view
mysql_schema.sql	เวอร์ชัน MySQL/MariaDB — ใช้กับ Laragon + HeidiSQL
generate_data.py	สคริปต์ Python (ใช้ Faker) สำหรับสร้างข้อมูลตัวอย่าง → output เป็น seed_data.sql
seed_data.sql	(PostgreSQL) ข้อมูลตัวอย่างพร้อมใช้
mysql_seed_data.sql	เวอร์ชัน MySQL/MariaDB ของข้อมูลตัวอย่างชุดเดียวกัน
queries.sql	(PostgreSQL) Query ตัวอย่าง 12 แบบ
mysql_queries.sql	เวอร์ชัน MySQL/MariaDB ของ query ชุดเดียวกัน

ทุกไฟล์ทั้ง PostgreSQL และ MySQL/MariaDB ทดสอบรันจริงแล้ว ไม่มี error

วิธีเปิดใช้งานผ่าน Laragon + HeidiSQL

1. เปิด Laragon แล้วกด Start All (จะรัน MySQL/MariaDB ให้อัตโนมัติ)
2. เปิด HeidiSQL (คลิกขวาที่ Laragon tray icon > Database > HeidiSQL หรือเปิดจาก Laragon > Menu > MySQL > HeidiSQL)
3. เชื่อมต่อ session แรก (ปกติ Laragon ตั้งไว้ให้แล้ว): user root, password ว่างเปล่า, host 127.0.0.1
4. คลิกขวาที่ฝั่งซ้าย (รายชื่อ database) > Create new > Database ตั้งชื่อ เช่น ecommerce_db
5. คลิกที่ database ที่สร้าง แล้วไปแท็บ Query ด้านบน
6. เปิดไฟล์ผ่านเมนู File > Load SQL file... เลือก mysql_schema.sql แล้วกด Execute (F9) ทั้งไฟล์
7. ทำซ้ำ: โหลด mysql_seed_data.sql แล้ว Execute
8. ทำซ้ำ: โหลด mysql_queries.sql แล้ว Execute ทีละ query (เลือกข้อความ query ที่ต้องการแล้วกด F9 เพื่อรันเฉพาะอันนั้น)

ข้อควรระวัง: ไฟล์ตระกูล mysql_* ใช้กับ Laragon ได้เลย ส่วนไฟล์ schema.sql, seed_data.sql, queries.sql (ไม่มี prefix mysql_) เขียนด้วย syntax ของ PostgreSQL ซึ่ง ใช้กับ Laragon ไม่ได้ (Laragon ให้ MySQL/MariaDB เป็นค่าเริ่มต้น)

วิธีใช้งานผ่าน command line (ถ้าถนัดสาย terminal)

# 1. สร้างฐานข้อมูล
createdb ecommerce_db

# 2. สร้าง schema
psql -d ecommerce_db -f schema.sql

# 3. (ถ้าต้องการ regenerate ข้อมูล) ติดตั้ง faker แล้วรัน
pip install faker
python generate_data.py

# 4. โหลดข้อมูลตัวอย่าง
psql -d ecommerce_db -f seed_data.sql

# 5. ลองรัน query ตัวอย่าง
psql -d ecommerce_db -f queries.sql

ปรับจำนวนข้อมูลได้ที่ตัวแปร N_* ด้านบนของ generate_data.py (เช่น N_CUSTOMERS, N_ORDERS)

Skill ที่โชว์ในไฟล์ queries.sql

1. Window Function — RANK() OVER (PARTITION BY ...) หา Top N สินค้าขายดีต่อหมวดหมู่
2. Running Total / Moving Average — SUM() OVER, AVG() OVER (ROWS BETWEEN ...)
3. Recursive CTE — ไล่ต้นไม้หมวดหมู่สินค้า (WITH RECURSIVE)
4. Customer Segmentation (RFM) — Recency/Frequency/Monetary + CASE business logic
5. Self-Join — Market Basket Analysis (สินค้าที่มักซื้อคู่กัน)
6. Subquery + HAVING — หาลูกค้าที่ใช้จ่ายเกินค่าเฉลี่ย
7. Anti-Join (LEFT JOIN + IS NULL) — สินค้าขายดีแต่ไม่มีรีวิว
8. Weighted Average — Bayesian-style rating เพื่อกันรีวิวน้อยแต่คะแนนสุดโต่ง
9. Conditional Aggregation — FILTER (WHERE ...) คำนวณ cancellation rate รายเดือน
10. Multi-table Join + Date Filtering — Low stock alert พร้อมยอดขาย 30 วันล่าสุด
11. EXPLAIN ANALYZE — อ่าน query plan เพื่อ optimize
12. VIEW — reporting layer สำหรับ dashboard

Schema Highlights

• Trigger trg_deduct_stock: ตัดสต็อกอัตโนมัติทุกครั้งที่มีการ insert order_items พร้อม log ลง stock_movements และ raise exception ถ้าสต็อกไม่พอ (ทดสอบแล้วว่าทำงานถูกต้อง — สต็อกไม่มีทางติดลบ)
• Self-referencing category รองรับหมวดหมู่ย่อยไม่จำกัดชั้น
• Price snapshot: order_items.unit_price เก็บราคา ณ เวลาซื้อ ไม่ผูกกับราคาปัจจุบันของสินค้า (ป้องกันข้อมูลย้อนหลังเพี้ยนถ้าราคาสินค้าถูกแก้ทีหลัง)
• 10 indexes ครอบคลุม foreign key และ pattern การ query ที่ใช้บ่อย (filter by customer, date, status)

แนวทางต่อยอด (ถ้าอยากทำเพิ่มให้พอร์ตน่าสนใจขึ้น)

• เพิ่ม EXPLAIN ANALYZE เทียบก่อน-หลังใส่ index แบบมี screenshot ผล
• ทำ dashboard ง่ายๆ ด้วย Python (matplotlib/streamlit) ดึงจาก vw_daily_sales
• เขียน stored procedure สำหรับ "checkout flow" ที่ครอบคลุม transaction + rollback
• Deploy ขึ้น Supabase/Railway (ฟรี) แล้วแปะ live demo link ใน README