[Part 3] Database: Advance query

จากบทความที่แล้ว https://www.pathompat.me/part-2-database-query-crud/ เราทราบ query พื้นฐานในการ INSERT, DELETE, UPDATE, SELECT รวมถึงการ query ข้อมูลแบบมีเงื่อนไขขั้นต้นไปแล้ว บทความนี้เราจะมาเรียนรู้วิธีการ query แบบ aggregation กัน โดยเราจะใช้ table structure นี้

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    nickname VARCHAR(50) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    age INT,
    gender VARCHAR(1) NOT NULL DEFAULT 'M',
    city VARCHAR(50),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (name, nickname, email, age, gender, city) VALUES
('สมชาย ใจดี', 'ชาย', 'somchai.j@example.com', 32, 'M', 'กรุงเทพมหานคร'),
('วิภาวดี วงศ์วาน', 'วดี', 'wipawadee.v@example.com', 28, 'F', 'เชียงใหม่'),
('เอกชัย สุขใจ', 'เอก', 'ekachai.s@example.com', 35, 'M', 'ขอนแก่น'),
('ปรีชา สมบัติ', 'ปรี', 'preecha.s@example.com', 40, 'M', 'นครราชสีมา'),
('รุ่งอรุณ วัฒนธรรม', 'รุ่ง', 'rungarun.w@example.com', 27, 'F', 'ภูเก็ต'),
('ศุภชัย รัตนชาติ', 'ชัย', 'supachai.r@example.com', 30, 'M', 'อุดรธานี'),
('อรอนงค์ สงวนศิลป์', 'อร', 'oranong.s@example.com', 33, 'F', 'สงขลา'),
('ปิยะดา วงศ์ทอง', 'ปิยะ', 'piyada.w@example.com', 25, 'F', 'นครปฐม'),
('ธนพล ทองดี', 'ธน', 'thanapon.t@example.com', 29, 'M', 'พิษณุโลก'),
('ภานุวัฒน์ พรหมมาศ', 'ภานุ', 'panuwat.p@example.com', 31, 'M', 'สุราษฎร์ธานี'),
('ณัฐวุฒิ ศรีสุข', 'ณัฐ', 'nattawut.s@example.com', 26, 'M', 'ระยอง'),
('จารุวรรณ แสงทอง', 'จารุ', 'jaruwan.s@example.com', 38, 'F', 'นครศรีธรรมราช'),
('สุจิตรา นาคทอง', 'จิต', 'sujitra.n@example.com', 24, 'F', 'ลำปาง'),
('กิตติศักดิ์ แซ่ลิ้ม', 'กิต', 'kittisak.l@example.com', 37, 'M', 'ตรัง'),
('ปาริชาติ อมรชัย', 'ปารี', 'parichat.a@example.com', 22, 'F', 'กาญจนบุรี'),
('ณภัทร รุ่งเรือง', 'ภัทร', 'napat.r@example.com', 34, 'M', 'ชลบุรี'),
('วราภรณ์ แก้วมณี', 'วรา', 'waraporn.k@example.com', 29, 'F', 'ราชบุรี'),
('อนุวัฒน์ บำรุงไทย', 'วัฒน์', 'anuwat.b@example.com', 32, 'M', 'สุโขทัย'),
('รุ่งโรจน์ ใจเพ็ชร', 'โรจน์', 'rungroj.j@example.com', 28, 'M', 'มหาสารคาม'),
('จิราพร สงวนสุข', 'จิ', 'jiraporn.s@example.com', 23, 'F', 'ยะลา');

Aggregation function

เป็นฟังก์ชัน SQL ใช้เพื่อจัดกลุ่มของข้อมูล หาความสัมพันธ์ นับจำนวน หรือหาค่าทางสถิติบางอย่างกับ query ของเรา

COUNT(): ใช้สำหรับนับจำนวนแถวในตารางที่ตรงกับเงื่อนไขที่กำหนด

ตัวอย่าง: นับจำนวนผู้ใช้ทั้งหมดในตาราง users

SELECT COUNT(*) AS total_users FROM users;

ตัวอย่างเมื่อใช้ร่วมกับ Conditional: นับจำนวนผู้ใช้ทั้งหมดที่อายุน้อยกว่า 30

SELECT count(*) FROM users WHERE age < 30;

SUM(): ใช้ในการคำนวณผลรวมของค่าตัวเลขในแต่ละกลุ่ม

ตัวอย่าง: หาผลรวมของอายุผู้ใช้ทั้งหมด

SELECT SUM(age) AS total_age FROM users;

ตัวอย่างเมื่อใช้ร่วมกับ Conditional: หาผลรวมของอายุผู้ใช้ทั้งหมดที่เป็นเพศชาย

SELECT SUM(age) AS total_age FROM users WHERE gender = 'M';

AVG(): ใช้สำหรับหาค่าเฉลี่ยของค่าตัวเลข

ตัวอย่าง: คำนวณค่าเฉลี่ยอายุของผู้ใช้ทั้งหมด

SELECT AVG(age) AS average_age FROM users;

SELECT AVG(age) AS average_age FROM users WHERE gender = 'M';

MIN(), MAX() : ใช้เพื่อหาค่าต่ำสุดและสูงสุดในชุดข้อมูลที่กำหนด

SELECT MIN(age) AS min_age FROM users;
SELECT MAX(age) AS max_age FROM users;

-- หรือ สามารถใช้คู่กันก็ได้
SELECT MIN(age) as min_age, MAX(age) AS max_age FROM users;

ตัวอย่างเมื่อใช้ร่วมกับ Conditional: หาอายุต่ำสุดและสูงที่สุดของคนที่อยู่ในเมือง "กรุงเทพมหานคร"

SELECT MIN(age) as min_age, MAX(age) AS max_age FROM users WHERE city = 'กรุงเทพมหานคร';

Group by, Having

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

Syntax:

SELECT [...] FROM users GROUP BY [...] HAVING [...];

ตัวอย่าง:

นับจำนวน user แยกเพศชายและหญิง

SELECT gender, COUNT(*) as count_gender FROM users GROUP BY gender;

หาค่าเฉลี่ยอายุของ user เพศชายและหญิง ที่อยู่ในจังหวัดกรุงเทพ, เชียงใหม่, ขอนแก่น

SELECT gender, AVG(age) as avg_gender FROM users WHERE city = 'กรุงเทพมหานคร' OR city = 'เชียงใหม่' OR city = 'ขอนแก่น' GROUP BY gender;

หาเมืองที่มี user มากกว่า 1

SELECT city, COUNT(*) as count_city FROM users GROUP BY city HAVING count_city > 1;

กรณีกลุ่มข้อมูลกระจายตัวจนไม่สามารถ group by ได้ (เช่น อายุ) เราสามารถจัดกลุ่มข้อมูลให้จำกัดเราที่เราต้องการ โดยใช้ CASE WHEN ได้ ตัวอย่างเช่น

หาจำนวน, ค่าเฉลี่ยอายุของ user ในช่วงอายุ 0-20, 21-35, 35-50 , >50

SELECT 
  CASE
   WHEN age <= 20 THEN '<20'
   WHEN age > 20 and age <= 35 THEN '20-35'
   WHEN age > 35 AND age <= 50 THEN '35-50'
   WHEN age > 50 THEN '>50'
  END as age_group,
  COUNT(*) as count_age,
  AVG(age) as avg_age
FROM users
GROUP BY
  CASE
   WHEN age <= 20 THEN '<20'
   WHEN age > 20 and age <= 35 THEN '20-35'
   WHEN age > 35 AND age <= 50 THEN '35-50'
   WHEN age > 50 THEN '>50'
  END

หาค่าเฉลี่ยและผลรวมอายุของ user เพศชาย แยกรายจังหวัด ที่มีอายุระหว่าง 18-80 ปีและชื่อไม่มีคำว่า "สม" แสดงข้อมูลเฉพาะจังหวัดที่มีคนมากกว่า 1 คน เรียงตามอักษร ก-ฮ

SELECT
	city,
	AVG( age ) AS avg_user,
	COUNT(*) AS count_user 
FROM
	users 
WHERE
	age BETWEEN 18 AND 80 AND 
	name NOT LIKE '%สม%' 
GROUP BY
	city 
HAVING
	count_user > 1 
ORDER BY
	city ASC;

Group by มากกว่า 1 คอลัมน์: นับจำนวน user แยกตามเมืองและเพศ

SELECT gender, city, count(*) AS count_user FROM users GROUP BY gender, city;

System function

เป็นฟังก์ชันที่ SQL สร้างขึ้นให้เพื่อให้เราเอามาใช้ช่วยให้ query เงื่อนไขต่างๆ ได้ง่ายขึ้น จะยกตัวอย่างที่ใช้หลักๆ คือ

CONCAT(): ใช้ต่อ text 2 ชุด เข้าด้วยกัน

SELECT CONCAT(name, " ", nickname) as full_name FROM users;

ตัวอย่าง query ชื่อและชื่อเล่นของ users

FORMAT(): ใช้กรณีต้องการแสดงผลจุดทศนิยม ตำแหน่งที่เท่าที่กำหนด

SELECT FORMAT(250500.5634, 2);

CHAR_LENGTH(): ใช้นับขนาดของตัวอักษร

SELECT name, CHAR_LENGTH(name) as name_character FROM users;

LOWER(), UPPER(): ใช้เปลี่ยนตัวอักษร (เฉพาะภาษาอังกฤษ) เป็นตัวใหญ่หรือเล็กทั้งหมด

SELECT LOWER(email) as l_email, UPPER(email) as u_email FROM users;

TRIM(), LTRIM(), RTRIM(): ใช้ลบช่องว่างในตัวอักษร

SELECT name, TRIM(email) as email FROM users;

Time helper function
1. CURDATE(): ใช้หาค่าวันที่ของระบบ
2. DATE_ADD(): ใช้เป็นฟังก์ชันช่วยหาเวลาในอนาคต
3. DATE_SUB(): ใช้เป็นฟังก์ชันช่วยหาเวลาในอดีต
4. NOW(): ใช้หาค่าเวลาปัจจุบันของระบบ หรือเป็นตัวเทียบเวลา

SELECT NOW();
SELECT DATE_ADD(CURDATE(), INTERVAL 1 DAY) AS tomorrow_date;
SELECT DATE_SUB(CURDATE(), INTERVAL 2 MONTH) AS date_two_months_ago;

-- ใช้กับ condition query เพื่อเช็คเวลาได้
SELECT name, TRIM(email) as email FROM users WHERE created_at < NOW();

Exercise

จากตาราง products ใน exercise แรก ให้หาข้อมูลดังนี้

นับจำนวนสินค้าทั้งหมดในรายการที่มีราคาสูงกว่า 1500
หาค่าเฉลี่ยราคาของสินค้าที่ขึ้นต้นด้วยอักษร a

ให้เพิ่ม category และ mock ข้อมูลใส่เข้าไปประมาณ 4-5 category จากนั้น

หาราคารวมของสินค้าทั้งหมดแบ่งตาม category
หาราคาต่ำสุดของสินค้าหมวด Tablet ที่มีชื่อขึ้นต้นด้วย "T"
นับจำนวน, หาราคาสูงสุดของสินค้าแต่ละหมวด โดยตัดสินค้าที่มีราคาสูงสุดน้อยกว่า 10,000 ออก และให้เรียงผลลัพธ์ตามตัวอักษร
หาหมวดสินค้าที่มีราคาเฉลี่ยสูงที่สุดเป็น 3 อันดับแรก เรียงลำดับตามตัวอักษร

[Part 3] Database: Advance query

Aggregation function

Group by, Having

System function

Exercise

Reference

Pathompat Sungpankhao