วิเคราะห์ข้อมูลลูกค้าในร้านอาหารอย่างง่ายด้วย SQL

โปรเจคนี้เราจะใช้ SQL ดึงข้อมูลจาก restaurant.db มาตอบคำถามที่เราสนใจ อาทิ

• how many time each customer in london and new york buy
• The sum of sales that group by manu
• find what is the popular bekery in london

Set up

เราเริ่มจากการเตรียม database ให้พร้อม โดยการใช้ mock up data เข้าไป

ในโปรเจคนี้จะมี 3 Table ได้แก่

1. Customers บันทึกข้อมูลของลูกค้า : id(PRIMARY KEY) , name, city
2. Manus รายการอาหาร : manu_id(PRIMARY KEY), manu_name, price
3. Receipt ใบเสร็จ : receipt_id(PRIMARY KEY), customer_id, manu_id

.open restaurant.db
.table
.mode column


create & insert customers
CREATE TABLE IF NOT EXISTS customers (
    id int,
    name text,
    city text,
    PRIMARY KEY (id)
);

INSERT INTO customers VALUES
  (1,"Amanda","BKK"),
  (2,"Alice","New York"),
  (3,"Kara", "New York"),
  (4,"Kate", "London"),
  (5,"John","London"),
  (6,"Louise","London");

-- create & insert manus
CREATE TABLE IF NOT EXISTS manus (
    manu_id int,
    manu_name text,
    price text,
    PRIMARY KEY (manu_id)
);

INSERT INTO manus VALUES
  (1,"Cake", 10.50 ),
  (2,"Waffle", 6.15 ),
  (3,"bread", 5.00),
  (4,"Pie", 9.55 ),
  (5,"Donut", 3.20);


-- create & insert receipt
CREATE TABLE IF NOT EXISTS receipt (
    receipt_id int,
    customer_id int,
    manu_id int,
    PRIMARY KEY (receipt_id)
);

INSERT INTO receipt VALUES
  (1,6,2),(2,5,3),(3,2,4),
  (4,5,3),(5,6,4),(6,6,3),
  (7,2,1), (8,1,2), (9,1,3),
  (10,6,3), (11,5,4), (12,4,3),
  (13,1,4),(14,5,1),(15,3,1);

Finding answers

Question 1 : how many times each customer in london and new york buy

เนื่องจากเราต้องการข้อมูลลูกค้าและบันทึกใบเสร็จการซื้อ เราจะต้อง JOIN “Table customer” และ “Table receipt” โดยใช้ customer_id เป็น key

โจทย์ข้อนี้ มีการใช้ subquery สองที่ คือ

• ใน Table customer เลือกเอา name และ id แต่มีการใช้ WHERE city IN (‘London’,’New York’)) เพื่อกรองเอาแค่ลูกค้าในเมือง london และ new york ออกมา
• ใน Table receipt เลือกเอา customer_id และ receipt_id

สุดท้าย เราจะ count(receipt_id) รวมกับ Group by id เพื่อนับจำนวนใบเสร็จที่ลูกค้าแต่ละ id ซื้อ

SELECT name,
  count(receipt_id) as count
FROM (SELECT name, id FROM customers WHERE city IN ('London','New York')) AS c
JOIN (SELECT customer_id, receipt_id FROM receipt) AS r
WHERE c.id = r.customer_id
GROUP BY c.id;

ได้ผลออกมาเป็น….

ลูกค้าในเมือง London และ New York มีจำนวนจากซื้อ ดังภาพ

Question 2: The sum of sale that groub by manu

เราใช้ JOIN เพื่อรวมข้อมูลของ manu และ receipt ออกมา โดยใช้ manu_id เป็น key และใช้ group by manu_id เพื่อแยกกลุ่มตามเมนู

เราเลือก COUNT(r.manu_id) AS purchased_count เพื่อนับจำนวนที่เมนูใดๆถูกซื้อไป และหาผลรวมของเงินที่ขายได้จาก m.price * COUNT(r.manu_id)

สุดท้ายนี้ เราจะได้คอลัมน์ total มาเป็นผลรวมเงินที่ได้จากเมนูใดๆ จึงใช้คำสั่ง ORDER BY total DESC เพื่อเรียงลำดับจากน้อยไปมาก

SELECT 
  m.manu_name AS manu,
  m.price,
  COUNT(r.manu_id) AS purchased_count,
  m.price * COUNT(r.manu_id) AS total
FROM manus AS m
JOIN receipt AS r  
ON m.manu_id = r.manu_id
GROUP BY m.manu_id
ORDER BY total DESC;

ได้ผลออกมาเป็น….

Pie ให้ผลรวมของรายได้มากที่สุด ส่วน bread แม้จะขายได้มาก แต่เงินที่ได้ก็ได้เพียงอันดับสาม

Question 3: find what is the popular bekery in london

เนื่องจากข้อนี้ต้องใช้ทั้งสาม table ซึ่งการที่เราจะ query มันทุกๆครั้ง ซึ่งกับข้อมูลที่นานไปอาจจะมีจำนวนมากขึ้น ก็น่าจะใช้เวลา query นาน

เราจึงสร้าง View ที่ชื่อ cus_NewYork และ manu_London_count ขึ้นมา เป็นเหมือนการสร้าง table จำลองเอาไว้ สำหรับ query ที่ใช้งานบ่อย

• cus_NewYork : เลือกมาเฉพาะลูกค้าที่อยู่ในเมือง London
• manu_London_count : JOIN cus_NewYork เข้ากับ receipt, GROUP BY r.manu_id แยกตามเมนู และเรียงลำดับ COUNT(receipt_id) เพื่อหาว่าเมนูไหนมีคนสั่งมามากที่สุด

ซึ่งเวลาใช้งาน เราจะเอา manu_London_count มาแสดงรวมกับ manu_name

CREATE View IF NOT EXISTS cus_NewYork AS
  SELECT * FROM customers
  WHERE city = "London";

CREATE View IF NOT EXISTS manu_London_count AS
  SELECT 
    manu_id, COUNT(receipt_id) AS count
  FROM receipt AS r 
  JOIN cus_NewYork as c
  ON c.id = r.customer_id
  GROUP BY r.manu_id
  ORDER BY COUNT(receipt_id) DESC;
  
SELECT 
  m.manu_name AS name,
  London.count
FROM manu_London_count AS London
JOIN manus AS m
ON m.manu_id = London.manu_id;

ได้ผลออกมาเป็น….

Bread ถูกคนใน London ซื้อไปมากที่สุด โดยทิ้งห่างจาก Pie, Waffle, Cake มาก

จากโปรเจคจะเห็นได้ว่า เราคือ client ที่ส่ง query (คำถาม) ไปหา server เพื่อรับข้อมูลจาก databaseมา ประมวลผล เพื่อตอบคำถามที่เราสนใจ

ข้อสำคัญของการใช้งาน database ที่มีหลาย table คือการที่เราเข้าใจว่าแต่ละ record มีความสัมพันธ์ต่อกันและกันอย่างไร และเราจะสกัด insigth อะไรออกมาได้บ้าง