Tag: SQL

ในธุรกิจหลายๆเจ้าไม่ว่าจะเป็นร้านอาหาร, ร้านกาแฟ, retail หรือจะเป็นบริษัทต่างๆที่มีพนักงาน มี product/service ที่ต้องขายให้กับลูกค้า เกือบจะทุกวงการจะต้องมีการเก็บข้อมูลทุกอย่างเอาไว้เพื่อเป็นหลักฐาน

ประเด็นคือเราเคยจัดการกับข้อมูลเหล่านี้ให้เป็นระเบียบและเป็นระบบหรือเปล่า มีข้อมูลเยอะแต่เคยเอาข้อมูลเหล่านั้นมาวิเคราะห์เพื่อสร้าง value ให้กับธุรกิจเราต่อมั้ย

สำหรับธุรกิจขนาดเล็กอาจจะไม่จำเป็นต้องทำฐานข้อมูล (database) ก็ได้ แต่ในธุรกิจขนาดกลางจนถึงขนาดใหญ่น่าจะต้องมีอย่างแน่นอน

ถ้าธุรกิจไม่ว่าจะขนาดไหนหากไม่มี database ก็เหมือนไม่มี resource ที่จะอาศัยอยู่ได้หรืออยู่ได้แบบลำบาก

ในยุคที่มีเทคโนโลยีและ AI ที่เริ่มมาไกลมากๆ คงจะเสียเปรียบที่ธุรกิจเราจะเติบโตขึ้นมากๆเมื่อเทียบกับคู่แข่งเจ้าอื่นที่พร้อมจะปรับตัวให้ทันโลก

ดังนั้น วันนี้เราจะมาให้ทุกคนเห็นวิธีสร้าง/จัดการ/วิเคราะห์ database อย่างง่ายกัน เพื่อที่ทุกคนจะสามารถไปปรับใช้กับธุรกิจหรือชีวิตของตัวเองได้

1. ออกแบบ database ให้ดี
2. สร้างไฟล์ database
3. ทำความรู้จักกับ SQL
4. สร้าง table เพื่อเก็บ data
   1. สร้าง table สำหรับเก็บข้อมูล
   2. Modify กับ table ที่เรามีง่ายๆด้วย ALTER
   3. Insert ข้อมูลลง database
   4. Update ข้อมูลเพิ่มเติม
   5. Delete data ที่เราไม่ต้องการ
   6. Delete table ที่เราไม่ต้องการ
5. เจาะ data หา insights!
   1. การ join tables โดยใช้ WHERE clause
   2. Subqueries
   3. Common Table Expression (CTEs)
6. More posts

---

ออกแบบ database ให้ดี

การออกแบบ database ให้ดีก็เหมือนออกแบบบ้านให้ดีซักหลังนึง เราอยากจะมีบ้านที่มีโครงสร้างแข็งแรง อยู่กับเราได้นานๆไม่ว่าจะเจอสภาพอากาศแบบไหนก็ทนได้

***เราก็ต้องเข้าใจก่อนด้วยว่าผู้ที่จะเข้ามาอาศัยในบ้านหลังนี้เป็นคนแบบไหน daily routine เป็นอย่างไร เพื่อให้การออกแบบตอบโจทย์ลูกบ้านด้วย

ดังนั้น สิ่งสำคัญเป็นอันดับแรกเลยคือ ทำความเข้าใจธุรกิจของตัวเองซะก่อนว่า ภายในธุรกิจเรามีโครงสร้างและการทำงานภายในและภายนอกองค์กรเป็นอย่างไร

จากนั้นเราก็มาออกแบบว่าใน database เราจะเก็บข้อมูลอะไรบ้าง ควรแบ่งข้อมูลให้ดี เหมือนกับการ partition บ้านออกเป็นแต่ละห้อง ถ้าแบ่งไม่ดีเวลาเราจะหาของชิ้นไหนก็หาไม่เจอ ดังนั้นบ้านหลังนี้จะเป็นระเบียบหรือรกก็ขึ้นอยู่กับเราแล้วนะ

ในบทความนี้เราลองสร้าง 4 tables เพื่อให้เห็นภาพ ยิ่งธุรกิจเราใหญ่มีความซับซ้อนมาก จำนวน tables ก็เยอะตามไปด้วยนะ

• menus เพราะลูกค้ามาร้านเราก็อยากมาสั่งเครื่องดื่ม ขนมใช่มั้ย เป็น table ที่ขาดไม่ได้เลย
• staffs ร้านจะขาดพนักงานไม่ได้เลย ไม่อย่างนั้นใครจะเป็นช่วยรันงานในร้านให้เรา ;-;
• customers และร้านจะขาดลูกค้าไม่ได้เช่นกัน ลูกค้าคือพระเจ้า กำไรธุรกิจจะมากหรือน้อยก็ขึ้นอยู่กับตัวแปรนี้เลย
• transactions สิ่งที่เก็บข้อมูลระหว่างลูกค้า รายการที่ลูกค้าสั่ง และพนักงานที่รับผิดชอบ ก็คือ บิล/ใบเสร็จที่จะเป็นหลักฐานให้เราเช็คย้อนหลังได้

แล้วทุกคนล่ะ คิดว่าในธุรกิจเกี่ยวกับคาเฟ่ ร้านกาแฟควรมี table อะไรเพิ่มเติมอีกนะ

โดยสิ่งที่ช่วยให้เราเห็นภาพรวมของ database เราได้ดีขึ้น นั่นคือ ER diagram ย่อมาจาก Etity-Relationship diagram

• Etity ก็หมายถึง table ทั้งหมดใน database
• Relationship ก็คือ ความสัมพันธ์ระหว่าง table หนึ่งกับ table หนึ่ง ซึ่งความสัมพันธ์ก็มีหลายแบบ โดยส่วนใหญ่จะเป็นแบบ one-to-many

ER diagram แสดงความสัมพันธ์สำหรับทุก table ในไฟล์ my_cafe.db

Note: แต่ละ table ควรจะมีหนึ่งคอลัมน์เป็น id ของ table นั้นๆสำหรับการ join tables เข้าด้วยกัน

ทุกคนสามารถลองออกแบบ database ของตัวเองได้ง่ายๆที่ dbdiagram.io บนเว็บ browser ฟรีๆ ใช้งานง่าย ทำเสร็จโหลดออกมาเป็นไฟล์ .pdf หรือ .png ไปใช้งานต่อได้เลย

---

สร้างไฟล์ database

เราจะสร้าง database ผ่านเว็บ browser ที่มีชื่อว่า replit.com กัน ใช้งานฟรีด้วย (แต่สร้างได้แค่ 3 projects น้า)

สมัคร account เสร็จแล้ว กด Create Repl แล้วเลือก SQLite ได้เลย

Keyword ที่ใช้ในการสร้างไฟล์ database คือ .open แล้วตามด้วยชื่อไฟล์ โดยนามสกุลไฟล์เป็น .db เช่น

.open ports_coffee.db

หรือใครอยากจะลองติดตั้ง software ลง local computer / laptop เป็นของตัวเอง เราขอแนะนำเป็น DB Browser for SQLite โหลดใช้ได้ฟรีทั้ง Windows / macOS เลย

---

ทำความรู้จักกับ SQL

SQL ย่อมาจาก Structured Query Language เป็นภาษาที่เราใช้สำหรับสื่อสารระหว่างเรากับ database เพื่อจัดการและดึงข้อมูลที่เราต้องการออกมาจาก database

โดย SQL จะมีอยู่หลายแบบ ไม่ว่าจะเป็น SQLite, MySQL, PostgreSQL, Microsoft SQL Server เป็นต้น โดยตัวที่เราเลือกจะเป็น SQLite ซึ่งเป็น standard SQL เหมาะสำหรับผู้เริ่มต้นเรียนภาษา SQL

---

สร้าง table เพื่อเก็บ data

สร้าง table สำหรับเก็บข้อมูล

ถ้า เราบอกว่า database เปรียบเสมือนบ้าน 1 หลัง แล้ว table กับ data ก็เป็นเหมือนห้องต่างๆภายในบ้าน และ เฟอร์นิเจอร์ในแต่ละห้องที่ถูก partition แล้วนั่นเอง

ดังนั้น database หลังนี้จะดีหรือไม่ดีก็ขึ้นอยู่กับการวางแผนของเราด้วย ก็เหมือนกับบ้านของเรา ถ้าแบ่งสัดส่วนแต่ละห้องได้เหมาะสมและเลือกเฟอร์นิเจอร์ได้ดี บ้านเราก็น่าอยู่ ใช้งานได้ไปอีกนาน

โดย syntax ในการสร้าง table เราจะใช้ keyword ดังนี้

CREATE TABLE table_name (
   column_name1 data_type,
   column_name2 data_type,
   .
   .
   .
);

Data type ที่เราใช้กันเป็นส่วนใหญ่ ได้แก่

• int คือ จำนวนเต็ม (Integer)
• real / float คือ จำนวนที่สามารถมีทศนิยมได้ ในทางคณิตศาสตร์ คือ Real Number
• text / varchar คือ ข้อความซึ่งอยู่ภายใต้เครื่องหมาย double quote (“…….”)
• date / datetime คือ วันและเวลา

-- create table
CREATE TABLE menus (
  menu_id int PRIMARY KEY,
  name text,
  price real,
  category text
);

CREATE TABLE staffs (
  staff_id int PRIMARY KEY,
  name text,
  position text
);

CREATE TABLE customers (
  customer_id int PRIMARY KEY,
  name text
);

CREATE TABLE transactions (
  transaction_id int PRIMARY KEY,
  customer_id int,
  staff_id int,
  menu_id int,
  quantity int,
  invoicedate date 
);

Modify กับ table ที่เรามีง่ายๆด้วย ALTER

ALTER เป็น keyword ที่ใช้สำหรับปรับแต่ง table ได้ เช่น add new columns, rename tables, rename columns

1. เพิ่มคอลัมน์ใหม่ใน table ที่มีอยู่

Syntax ในการ add new columns

ALTER TABLE table_name
ADD column_name_to_added data_type;

ตัวอย่างการใช้งาน เช่น เราอยากจะเพิ่ม column hiredate ลงไปใน table staffs

ALTER TABLE staffs
ADD hiredate date;

2. เปลี่ยนชื่อ table ที่มีอยู่

Syntax ในการ rename tables

ALTER TABLE old_table_name
RENAME TO new_table_name;

ตัวอย่างการใช้งาน เช่น เราอยากเปลี่ยนชื่อ table customers เป็น employees

ALTER TABLE customers
RENAME TO employees;

3. เปลี่ยนชื่อคอลัมน์ที่มีอยู่

Syntax ในการ rename columns

ALTER TABLE table_name
RENAME old_column_name TO new_column_name;

ตัวอย่างการใช้งาน เช่น เราอยากเปลี่ยนชื่อ column name ใน table customers เป็น nickname

ALTER TABLE customers
RENAME name TO nickname;

Insert ข้อมูลลง database

ขั้นต่อไป ถ้าสร้างบ้าน แบ่งห้องแล้ว แต่ไม่มีเฟอร์นิเจอร์ก็อยู่ไม่ได้สิใช่มั้ย
ดังนั้น เราจะมาใส่ data ลงไปในแต่ละ table กัน โดยการใช้คำว่า INSERT INTO กัน

Syntax ในการเพิ่มข้อมูลลง table

INSERT INTO table_name VALUES 
(row1_value1, row1_value2, ...),
(row2_value1, row2_value2, ...),
.
.
.

ตัวอย่างการเพิ่มข้อมูลเมนูทั้งหมดในคาเฟ่ของเรา

-- insert data
INSERT INTO menus VALUES 
  (1,'Single Espresso',60,'coffee'),
  (2,'Double Espresso',70,'coffee'),
  (3,'Hot Americano',80,'coffee'),
  (4,'Iced Americano',90,'coffee'),
  (5,'Hot Latte',90,'coffee'),
  (6,'Iced Latte',100,'coffee'),
  (7,'Hot Cappuccino',90,'coffee'),
  (8,'Iced Cappuccino',100,'coffee'),
  (9,'Hot Mocha',100,'coffee'),
  (10,'Iced Mocha',110,'coffee'),
  (11,'Hot Caramel Macchiato',100,'coffee'),
  (12,'Iced Caramel Macchiato',110,'coffee'),
  (13,'Thai Milk Tea',90,'non_coffee'),
  (14,'Matcha Latte',100,'non_coffee'),
  (15,'Hojicha Latte',100,'non_coffee'),
  (16,'Dark Chocolate',100,'non_coffee'),
  (17,'Yuzulemonade Sparkling',100,'non_coffee'),
  (18,'Honey Lemon Tea',100,'non_coffee'),
  (19,'Honey Lemon Sparkling',100,'non_coffee'),
  (20,'Dirty',110,'special_drink'),
  (21,'Orangano',100,'special_drink'),
  (22,'Dalgona Latte',120,'special_drink'),
  (23,'Sesame Latte',120,'special_drink'),
  (24,'Einspanner',120,'special_drink'),
  (25,'Canele',75,'dessert'),
  (26,'Butter Croissant',80,'dessert'),
  (27,'Butter Chocolate Croissant',95,'dessert'),
  (28,'Croffle',85,'dessert'),
  (29,'Lemon Cake',95,'dessert'),
  (30,'Choc Chip Cookie',85,'dessert'),
  (31,'Pain Aux Raisin',95,'dessert'),
  (32,'Pain Aux Chocolate',95,'dessert');

ตัวอย่างการเพิ่มข้อมูลพนักงานทุกคนในคาเฟ่

INSERT INTO staffs VALUES 
  (1,'Pon','CEO'),
  (2,'James','Manager'),
  (3,'Tuanote','Senior Barista'), 
  (4,'Ice','Senior Barista'),
  (5,'Bee','Accounting'),
  (6,'Fern','RD'),
  (7,'Nut','Marketing'),
  (8,'Ling','HR');

ตัวอย่างการเพิ่มข้อมูลลูกค้าทุกคนในคาเฟ่

INSERT INTO customers VALUES
  (1,'Tan'),
  (2,'Peach'),
  (3,'Jane'),
  (4,'Dith'),
  (5,'Kao'),
  (6,'Wa'),
  (7,'Tee'),
  (8,'Pan'),
  (9,'New'),
  (10,'Toey'),
  (11,'Fai'),
  (12,'Dew'),
  (13,'Boss'),
  (14,'Ta');

ตัวอย่างการเพิ่มข้อมูลรายการสั่งซื้อทุกรายการในคาเฟ่

INSERT INTO transactions VALUES
  (1,9,5,10,1,'2024-06-22'),
  (2,10,2,15,3,'2024-06-22'),
  (3,4,4,28,1,'2024-06-22'),
  (4,2,4,13,3,'2024-06-22'),
  (5,13,5,11,1,'2024-06-22'),
  (6,8,4,5,1,'2024-06-22'),
  (7,8,5,12,1,'2024-06-23'),
  (8,8,4,24,2,'2024-06-23'),
  (9,3,4,32,3,'2024-06-23'),
  (10,1,4,4,2,'2024-06-23'),
  (11,2,5,27,1,'2024-06-23'),
  (12,3,4,20,2,'2024-06-24'),
  (13,7,2,17,1,'2024-06-24'),
  (14,13,3,30,1,'2024-06-24'),
  (15,8,2,9,1,'2024-06-24'),
  (16,13,4,27,3,'2024-06-25'),
  (17,8,4,12,2,'2024-06-25'),
  (18,1,2,12,1,'2024-06-25'),
  (19,1,3,14,1,'2024-06-27'),
  (20,5,4,19,3,'2024-06-28'),
  (21,9,2,1,1,'2024-06-28'),
  (22,14,1,26,2,'2024-06-28'),
  (23,14,4,3,1,'2024-06-29'),
  (24,4,4,5,3,'2024-06-29'),
  (25,12,4,20,3,'2024-06-29'),
  (26,12,1,26,3,'2024-06-29'),
  (27,6,3,21,3,'2024-06-29'),
  (28,7,2,21,1,'2024-06-30'),
  (29,2,1,19,1,'2024-06-30'),
  (30,14,4,22,1,'2024-06-30');

หากต้องการแก้ไขข้อมูลหลังจากขั้นตอนนี้ ให้ใช้ keyword ในหัวข้อถัดไป

Update ข้อมูลเพิ่มเติม

ในบางครั้ง เราก็อาจจะใส่ข้อมูลผิด เช่น พิมพ์ชื่อพนักงานผิดไปตัวอักษรนึง กรอกราคาสินค้าผิด ทุกอย่างล้วนเกิดขึ้นได้จาก human error

Syntax ในการ update data ใน table

UPDATE table_name
SET column_name1 = value1, column_name2 = value2
WHERE condition;

เช่น เราต้องการเปลี่ยนชื่อเมนูจาก Hot Latte เป็น Hot Caffe Latte ซึ่งเป็นข้อมูลจาก table menus ที่มี menu_id = 5 พร้อมปรับขึ้นราคาจาก 90 บาทเป็น 95 บาท

UPDATE menus
SET name = "Hot Caffe Latte", price = 95
WHERE menu_id = 5;

Delete data ที่เราไม่ต้องการ

Syntax ในการลบข้อมูลออกจาก table

DELETE FROM table_name WHERE condition;

เช่น ณ วันนี้มีพนักงานคนหนึ่งที่ชื่อ Fern แผนก RD ลาออก เราเลยต้องนำข้อมูลของพนักงานคนนี้ออกจาก table staffs

DELETE FROM staffs WHERE name = "Fern"

Delete table ที่เราไม่ต้องการ

สร้าง table ได้ ก็ต้องลบได้ บางครั้งเราอาจจะเผลอสร้าง table นั้นขึ้นมาโดยไม่ได้ตั้งใจ ดังนั้น เราจึงขอแนะนำวิธีการลบ table ที่ easy มากๆแค่ keyword เดียวเลย นั่นคือ

DROP TABLE table_name;

ตัวอย่างเช่น เราจะสร้าง table ที่ชื่อว่า branch ไปใส่ใน database เฉยเลย ทั้งที่จริงคาเฟ่เรามีแค่สาขาเดียว ร้องไห้แล้ว ;-;

CREATE TABLE branches (
  branch_id int PRIMARY KEY,
  name text,
  address text,
  street text,
  sub_district text,
  district text,
  province text,
  country text,
  latitude real,
  longitude real
);

DROP TABLE branches

เห็นมั้ยว่าง่ายนิดเดียวเอง หมายถึง ทิ้งของที่เค้าให้มาง่าย แต่สร้างความสัมพันธ์ให้เค้ากลับมาหาอะยาก เดี๋ยวๆๆๆๆเริ่มออกทะเลไปไกลละ ;-;

---

เจาะ data หา insights!

เมื่อเรามีบ้านที่มั่นคงแข็งแรง มีห้องและเฟอร์นิเจอร์ต่างๆพร้อมใช้งานกันแล้ว หลังจากนี้ก็ถึงเวลาสร้างครอบครัวที่มีความสุขและอบอุ่นกันแล้ว นั่นก็คือ การสร้าง value กลับมาให้ธุรกิจของเรา โดยการหา insight จาก data นั่นเอง

และ insight ที่ดี มักเริ่มต้นจากการตั้งคำถามที่ดี โดยเทคนิคที่เราจะใช้หา insight กันในครั้งนี้ เราจะใช้อยู่ด้วยกัน 3 เทคนิคด้วยกัน

การ join tables โดยใช้ WHERE clause

โดยปกติการ join tables ใน SQL เราจะใช้คำว่า JOIN ON เป็นหลัก ตัวอย่างเช่น

SELECT * FROM customers AS cu 
JOIN transactions AS tr
ON cu.customer_id = tr.customer_id

แต่เราสามารถปรับ syntax ด้านบนมาเป็นตัวอย่าง code ด้านล่างได้

SELECT * 
FROM customers AS cu,
     transactions AS tr
WHERE cu.customer_id = tr.customer_id

Code ของเราสั้นลงเยอะเลย แถมได้ result เหมือนกันเป๊ะ!
ต่อจากนี้เดี๋ยวเรามาหา insight จาก data ที่เรา mock up ไว้แล้วด้วยวิธีนี้กัน

1. เมนูที่ขายดีที่สุด 3 อันดับแรกของคาเฟ่นี้

สิ่งที่เราอยากจะได้ใน query result นี้คือ ชื่อเมนู (menus.name) และ ยอดขายรวมทั้งหมดในแต่ละเมนู

แต่เนื่องจาก ยอดขายทั้งหมด (total sales) จะเกิดจากผลรวม (SUM) ของราคาขาย (menus.price) x จำนวนที่ขายได้ (transactions.quantity)

ทำให้เราต้อง join table menus กับ table transaction ด้วยคีย์ที่เป็น menu_id นั่นเอง และจับกลุ่มยอดขายตามชื่อเมนู เรียงข้อมูลจากมากไปน้อยตามยอดขาย

-- Top 3 Most Popular Menus at the cafe
  
SELECT 
  me.name AS Menu_Name,
  SUM(me.price * tr.quantity) AS Total_Sale
FROM menus AS me, 
     transactions AS tr
WHERE tr.menu_id = me.menu_id
GROUP BY 1
ORDER BY 2 DESC
LIMIT 3;

จาก result ที่ได้ จะเห็นว่า Dirty ขายดีที่สุด รองลงมา คือ Iced Caramel Macchiato และ Orangano (iced americano + orange juice)

2. วันไหนในเดือนมิถุนายน 2024 ที่มียอดขายต่ำที่สุด

Case นี้จะคล้ายๆกับการ query ก่อนหน้านี้เลย แต่เปลี่ยนการจับกลุ่มตามชื่อเมนูเป็นวันที่ในเดือนมิถุนายน 2024 และเรียงยอดขายจากน้อยไปมาก

ใน query นี้จะมีฟังก์ชันที่ใช้ดึงค่าต่างๆกับข้อมูลที่เป็น date ด้วย นั่นคือ STRFTIME()

-- Which day in June 2024 had the lowest sales?

SELECT
  STRFTIME('%d', invoicedate) AS Day_of_June_2024,
  SUM(price * quantity) AS Total_Sale
FROM transactions AS tr, menus AS me
WHERE tr.menu_id = me.menu_id
GROUP BY 1
ORDER BY 2;

จาก result จะเห็นว่า วันที่ขายได้น้อยที่สุดของเดือนมิถุนายน คือ วันที่ 27 รองลงมา คือ วันที่ 30 และวันที่ 24

3. พนักงานที่ทำยอดขายได้มากที่สุดในช่วงวันที่ 22-30 มิถุนายน 2024

สังเกตว่า สำหรับการ query ใน case นี้ สิ่งที่ต้องเปลี่ยนจาก query เดิมคือ ชื่อพนักงาน (staffs.name) และต้องจับกลุ่มตามพนักงานแต่ละคน

ทำให้การ query รอบนี้ต้อง join 3 tables พร้อมกัน โดยใช้คีย์ staff_id เพิ่มเข้าไปจากเดิม (ถ้าเขียน JOIN ON ปกติ code เราจะยาวมาก T T)

จากนั้นเราเพิ่มเงื่อนไขใน WHERE clause เข้าไปให้ข้อมูลอยู่ในช่วงวันที่ 22-30 มิถุนายน 2024 อีกนิดก็เรียบร้อย

-- Employees with the Highest Sales (June 26-29, 2024)

SELECT 
  st.name                     AS Staff_Name,
  SUM(me.price * tr.quantity) AS Total_Sale
FROM staffs AS st,
     transactions AS tr,
     menus AS me
WHERE st.staff_id = tr.staff_id AND tr.menu_id = me.menu_id
AND (invoicedate BETWEEN '2024-06-22' AND '2024-06-30')
GROUP BY 1
ORDER BY 2 DESC;

จาก result จะเห็นว่า Ice เป็นคนที่ทำยอดขายในช่วงวันที่ 22-30 มิถุนายน 2024 ได้มากที่สุด รองลงมา คือ James และ Pon

Subqueries

ถ้าให้พูดง่ายๆการเขียน subqueries คือ การเขียน query ซ้อน query

ในการเขียน query บางครั้ง เราจำเป็นต้องรัน query มากกว่า 1 query เพื่อให้ได้ result ที่เราต้องการ แต่จริงๆเราสามารถใช้ subqueries เขียนจบในครั้งเดียวเลย

รูปภาพจากหนังสือ Practical SQL Data Analytics Series Final version 2.0 – DataRockie

หลักการทำงานของ subqueries คือ code จะรันจาก inner query ชั้นในสุดก่อนไล่ไปเรื่อยๆจนถึง query ชั้นนอกสุด (outer query)

โดยส่วนใหญ่เรามักจะ subqueries ได้ที่ WHERE, FROM, JOIN และ SELECT clause ซึ่งตำแหน่งที่มักจะเจอง่ายที่สุดคือ WHERE clause

แต่จากแผนภาพเราน่าจะเห็นข้อเสียของวิธีนี้แล้ว คือ ถ้ามี query มากเกินไป อาจจะทำให้ code เราอ่านยาก แก้ไขยากมาก

1. Top Customer 5 คนที่มาคาเฟ่บ่อยที่สุดตั้งแต่วันที่ 25 มิถุนายน 2024

สิ่งที่เราต้องทราบก่อนเลยคือ จำนวนที่ลูกค้าแต่ละคนเข้ามาใช้บริการ​
ซึ่งสามารถเขียน query ได้จาก table transactions ตามปกติเลย

SELECT 
  customer_id, 
  COUNT(*) 
FROM transactions AS tr
WHERE tr.invoicedate >= '2024-06-25'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5;

แต่สิ่งที่เราอยากรู้จริงๆคือใครคือ 5 คนนั้นบ้าง นั่นคือ ชื่อลูกค้าทั้ง 5 คน
ทำให้ตอนเรา SELECT ชื่อลูกค้ามา เราจะฟิลเตอร์เฉพาะลูกค้าที่มี id ตาม result ข้างบนนี้

-- Top 5 customers with the most visits since June 25, 2024

SELECT
  cu.name AS Customer_Name
FROM customers AS cu
WHERE customer_id IN (
   SELECT customer_id FROM (
       SELECT customer_id, COUNT(*) 
       FROM transactions AS tr
       WHERE tr.invoicedate >= '2024-06-25'
       GROUP BY 1
       ORDER BY 2 DESC
       LIMIT 5
       )
);

สังเกตว่า query นี้ จะเริ่มรันจาก inner query สีเหลืองก่อน แล้วไล่ไปที่ inner query สีม่วง จนถึง outer query ชั้นนอกสุด

จาก result จะเห็นว่า ลูกค้า 5 คนที่มาใช้บริการบ่อยที่สุด คือ Tan, New, Dew, Boss และ Ta

2. เมนู category ไหนขายดีที่สุด

ขั้นแรก เราจะต้องรู้ก่อนว่ายอดขายทั้งหมดของแต่ละ category ก่อน ดังนั้น เรามาลองเขียน query นี้กันก่อนเลย

SELECT 
  me.category AS Category,
  SUM(me.price * tr.quantity) AS Total_Sales
FROM 
  menus AS me, 
  transactions AS tr
WHERE me.menu_id = tr.menu_id
GROUP BY 1
ORDER BY 2 DESC;

แต่เราอยากรู้แค่ว่าเมนู category ไหนขายดีที่สุด เราก็แค่ SELECT เฉพาะ Category มาแล้ว LIMIT แค่อันดับ 1 เท่านั้น

-- Most popular category menu

SELECT 
  Category
FROM (
  SELECT 
    me.category AS Category,
    SUM(me.price * tr.quantity) AS Total_Sales
  FROM 
    menus AS me, 
    transactions AS tr
  WHERE me.menu_id = tr.menu_id
  GROUP BY 1
  ORDER BY 2 DESC
)
LIMIT 1;

จาก result เราจะได้ว่า เมนูที่ขายดีที่สุด คือ เมนูประเภท classic coffee นั่นเอง

ก่อนจะไปที่หัวข้อสุดท้ายกัน อยากให้ทุกคนลองช่วยกันสังเกตหน่อยว่า จากทั้งสองตัวอย่างนี้เราเขียน subqueries ไว้ที่ไหนกันบ้าง?

Common Table Expression (CTEs)

Common Table Expression (CTEs) คือ การสร้าง Temporary Result Set เหมือนการประกาศตัวแปรเพื่อเรียกใช้งานต่อภายหลังเลย

ซึ่งข้อดีของการใช้ CTE เลยคือการ break subqueries ของเราที่เป็นปัญหาของเราก่อนหน้านี้ได้ ทำให้ code ของเราอ่านง่ายขึ้น แต่ยังได้ result เหมือนเดิมเป๊ะ! แถมยังสามารถนำไปใช้ใน query อื่นได้ด้วย

1. Top Spender 5 คนแรกที่มาคาเฟ่บ่อยที่สุดตั้งแต่วันที่ 25 มิถุนายน 2024

จากตัวอย่างที่ 1 ในการใช้ subqueries ที่ผ่านมา เราสามารถนำ inner query ชั้นในสุดมาเขียนเป็น CTE โดยใช้ WITH clause ได้

-- Top 5 customers with the most visits since June 25, 2024

WITH Top5_CustomersID_MostVisit AS (
  SELECT customer_id, COUNT(*)
  FROM transactions AS tr
  WHERE tr.invoicedate >= '2024-06-25'
  GROUP BY 1
  ORDER BY 2 DESC
  LIMIT 5
)

SELECT
  cu.name AS Customer_Name
FROM customers AS cu
WHERE customer_id IN (
   SELECT customer_id FROM Top5_CustomerID_MostVisit
);

2. ระหว่างเครื่องดื่มหรือขนม category ไหนขายดีที่สุด

จากตัวอย่างที่ 2 ก่อนหน้านี้ อันนี้ง่ายเลย เราแค่นำ subqueries ใน FROM clause มาสร้างเป็น CTE ใน WITH clause ก่อน โดยเราตั้งชื่อเป็น Total_Sales_by_Category

-- Most popular category menu

WITH Total_Sales_by_Category AS (
  SELECT 
    me.category AS Category,
    SUM(me.price * tr.quantity) AS Total_Sales
  FROM 
    menus AS me, 
    transactions AS tr
  WHERE me.menu_id = tr.menu_id
  GROUP BY 1
  ORDER BY 2 DESC
)

SELECT 
  Category
FROM Total_Sales_by_Category
LIMIT 1;

สรุปสิ่งที่เราได้จากบทความนี้ว่า ก่อนเราจะหา insight ในธุรกิจได้ เริ่มต้นจาก

• การทำความเข้าใจธุรกิจ
• ออกแบบ database ให้ดี
• สร้าง database ที่มั่นคง
• หา insight จาก database เพื่อสร้าง value ให้กับธุรกิจด้วย 3 เทคนิค

ทุกคนลองนำแนวทางนี้ที่เราฝากไว้ไปสร้างบ้านของตัวเองให้มั่นคง แข็งแรงนะ

สุดท้ายนี้ ถ้าชอบฝากกด like ใช่กด share ให้เพื่อนของทุกๆคนได้อ่านต่อกันด้วยนะ บทความหน้าจะเป็นเรื่องอะไร รอติดตามกันได้เลย : )

---

Comments

---