มาฝึกทำโปรเจค “HR Analytics” ด้วย SQL แบบลงมือจริงกัน

เคยสงสัยกันไหมกันว่า Data Analyst ในชีวิตจริงพวกเราทำงานกันอย่างไร ?

สำหรับผู้ที่สนใจอยากลองมาทำงานสาย Data การฝึกฝนทำโปรเจคกับข้อมูลเสมือนจริงจะช่วยให้รับมือกับการสัมภาษณ์งาน และสามารถทำให้มั่นใจได้ว่าเมื่อเข้าไปทำงานจริงจะทำงานได้

ในบทความนี้ เราจะชวนมาวิเคราะห์ข้อมูลด้วยการเขียนโปรแกรม SQL ในธีม “HR Analytic” สำหรับคนที่กำลังเริ่มเรียนรู้การใช้ SQL ก็สามารถทำไปพร้อม ๆ กันได้เลย

💡 ในบทความนี้เราใช้ Replit ที่ใช้งานสะดวกมาก ไม่ต้อง Install Program ก็สามารถใช้งานได้ ถ้าใครที่สะดวกใช้งานเครื่องมืออื่น ๆ สามารถทำได้เช่นกัน ถ้าพร้อมแล้วมาเริ่มทำกันเลย

อยากฝึกโจทย์ Data แนวไหนอีก ? ทักมาบอกทีมงานเราได้ที่ Post นี้เลย – ทีม Datayolk

สิ่งที่จะได้เรียนรู้จากการลองทำโปรเจคนี้

1. การใช้งานโปรแกรม Replit สำหรับวิเคราะห์ข้อมูลด้วยภาษา SQL
2. คำสั่งการสร้าง, เพิ่มข้อมูล และแก้ไขข้อมูลใน Database
3. คำสั่งพื้นฐานของ SQL ซึ่งประกอบไปด้วย Filter Data, Sorting Data, Aggregation
4. การเชื่อมตารางข้อมูล (Join table)
5. การเขียนคำสั่งย่อย (Subqueries)

วิธีการใช้งาน Replit สำหรับการทำ Data Project

• เข้าไปที่เว็บไซต์ Replit

• Sign Up เพื่อใช้งาน กดที่ปุ่ม Sign Up เพื่อสมัครใช้งานสำหรับผู้ใช้งานใหม่

• Create a Replit account สร้างบัญชีผู้ใช้งาน ในนี้มีทางเลือก 3 แบบเพื่อลงทะเบียนคือ สร้างบัญชีผ่าน Gmail สร้างบัญชีผ่าน Github และ สร้างบัญชีผ่าน Email อื่น ๆ

• Create a Repel ซึ่งก็คือการเลือก Template สำหรับการเลือกใช้งานโปรแกรม ในที่นี้เราจะค้นหา “SQL Lite” และตั้งชื่อ “Title” โปรเจคที่กำลังจะสร้าง

• Run command สำหรับข้อนี้เป็นองค์ประกอบที่สำคัญเพราะเมื่อพิมพ์คำสั่งในฝั่งซ้ายมือ แล้วกด Run ด้านบน จะปรากฎผลลัพธ์ในฝั่งขวามือ

โปรเจค “HR Analytic”

ลองนึกภาพว่าตัวเองอยู่ในบริษัทที่ปรึกษาแห่งหนึง เราในฐานะ Data Analsyt จำเป็นต้องทำงานกับหลายทีม ซึ่งทีมแรกของการทำงานคือ Human Resources เหล่า HR ของเราต้องการสำรวจข้อมูลพนักงานในองค์กร แต่เราไม่มีฐานข้อมูลพนักงานมาก่อน ความคาดหวังต่อเราในฐานะคนทำงานด้านข้อมูลคือการสร้างฐานข้อมูลพนักงานที่ประกอบด้วย ฐานข้อมูล Employee, ฐานข้อมูล Salary และฐานข้อมูล Team ซึ่งรายละเอียดในแต่ละฐานข้อมูลอยู่ในตารางข้อมูลที่ทีม HR เตรียมไว้ให้แล้ว นอกจากนี้พวกเขายังต้องการให้เรา Query ข้อมูลตามโจทย์ข้างล่างนี้

ข้อ 1 : ลองสร้าง Database จาก 3 ตารางประกอบด้วย ข้อมูล Employee, ข้อมูล Salary และข้อมูล Team

ให้ลองสร้าง Database ที่ประกอบไปด้วย 3 Tables ที่มี Field ข้อมูลดังนี้

• Table: Employee
  • employee_id
  • first_name
  • last_name
  • email
  • hire_date
  • team_id
• Table: Salary
  • employee_id
  • role
  • salary
• Table: Team
  • team_id
  • team_name

วิเคราะห์โจทย์ข้อที่ 1

เหตุผลที่เราควรเก็บข้อมูลใน Database มากกว่า ตารางใน Excel เพราะความสามารถในการจัดเก็บ (Store), เรียกค้น (Retrieve) และจัดการข้อมูลเช่น สร้าง, ย้าย, ปรับปรุง และ ลบ ข้อมูลนั้นมีประสิทธิภาพมากกว่า

ดังนั้นโจทย์นี้ต้องการให้เราสร้าง Database ที่ประกอบด้วย Table Employee, Table Salary และ Table Team ที่มีตัวแปรและชนิดของตัวแปร รวมถึงคีย์หลักและคีย์รองตามภาพ จากนั้นทำการ Insert ข้อมูลตามภาพลงตาราง

ดูวิธีการเขียนคำสั่ง SQL CREATE DATABASE Statement และ SQL INSERT INTO Statement

ทำข้อ 1 เสร็จแล้วใช่ไหม ดูเฉลยกัน

-- สร้าง Table
CREATE TABLE Team (
  team_id INT PRIMARY KEY,
  team_name VARCHAR(50)
);

CREATE TABLE Employee (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(255) UNIQUE,
    hire_date DATE,
    team_id INT,
    FOREIGN KEY (team_id) REFERENCES team(team_id)
);

CREATE TABLE Salary (
  employee_id INT PRIMARY KEY,
  role VARCHAR(50),
  salary INTEGER
);

-- เพิ่มข้อมูลไปในแต่ละ Table
INSERT INTO employee (employee_id, first_name, last_name, email, hire_date, team_id)
VALUES (1, 'Warde', 'Remon', 'wremon0@zimbio.com','1-Jun-23', 1),
(2, 'Orlando', 'Groneway', 'ogroneway1@wikipedia.org', '22-Jun-23', 1),
(3, 'Amalita', 'Shorland', 'ashorland2@npr.org', '25-Mar-23', 1),
(4, 'Reinwald', 'Pickersail', 'rpickersail3@skyrock.com', '24-Jun-23', 1),
(5, 'Ilario', 'Anfrey', 'ianfrey4@google.com','2-Jan-23',1),
(6,'Davey', 'Frowen', 'dfrowen5@nsw.gov.au', '3-Mar-23', 6),
(7, 'Leigha', 'Randlesome', 'lrandlesome6@alibaba.com', '15-Oct-22', 6),
(8,'Junia', 'Yakovliv', 'jyakovliv7@artisteer.com', '31-Jul-23', 6),
(9, 'Rochell', 'Waggatt', 'rwaggatt8@opera.com', '15-Apr-23', 6),
(10, 'Moises', 'Ardley', 'mardley9@webnode.com', '21-Jun-23', 6);

INSERT INTO Team (team_id, team_name)
VALUES (1, 'plan_A'),
(6, 'plan_B');

INSERT INTO Salary (employee_id, role, salary)
VALUES (1, 'Sales', 40153),
(2, 'Marketing', 46670),
(3, 'Legal', 71983),
(4, 'Sales', 15857),
(5, 'Software Engineer', 95174),
(6, 'Sales', 27819),
(7, 'Legal', 75323),
(8, 'Software Engineer', 90525),
(9, 'Tax Accountant', 85946),
(10, 'Marketing', 56575);

💡 เมื่อเราพิมพ์คำสั่งที่เป็น DDL-Data Definition Language เช่น Create Table() ลงใน Replit แล้วกด Run เราจะตรวจสอบ Console ฝั่งซ้ายว่าคำสั่งถูกต้องหรือไม่ หากถูกต้อง จะไม่ปรากฎ Error หากเราพิมพ์คำสั่งที่เป็น DML-Data Manipulation Language เช่น Insert Table(), SELECT() ลงใน Replit แล้ว Run เราจะตรวจสอบ Console ฝั่งซ้ายว่าคำสั่งถูกต้องหรือไม่ หากถูกต้อง จะปรากฎรายการที่เรา Insert ลงไปและสำหรับคำสั่ง Select เราจะได้คำตอบตามที่เราใช้คำสั่ง

ไม่ว่าอย่างไรก็ตาม เราสามารถตรวจสอบได้แค่คำสั่งที่ผิดเท่านั้น หากคำสั่งผิดเราจะเห็น Error Message ใน Console แต่ถ้า Logic หรือสิ่งที่ต้องการ Query ผิดก็ยังปรากฎคำตอบอยู่ นั่นเป็นสิ่งที่เราจะต้องเรียนรู้วิธีการใช้ Logic และ Query ที่เหมาะสมซึ่งเราจะได้ฝึกฝนกับในโจทย์ที่เตรียมมาให้

ข้อ 2 : พนักงานในบริษัทเราเป็นใคร ทำตำแหน่งอะไร เงินเดือนเท่าไหร่ ทำงานตั้งแต่เมื่อไหร่ ?

ให้ลอง Query เพื่อเรียกดูข้อมูลชื่อ นามสกุล, อีเมล, วันที่จ้างงาน, ตำแหน่ง และ เงินเดือนของพนักงานแต่ละคน

วิเคราะห์โจทย์ข้อที่ 2

ในการที่จะเรียกดูข้อมูลชื่อ นามสกุล, อีเมล, วันที่จ้างงาน, ตำแหน่ง และ เงินเดือน จาก 2 Table คือ Table Employee และ Table Salary จะต้องมีการเชื่อม (Join Table) ตาราง Employee และ Salary ด้วย employee_id

ดูวิธีการเขียนคำสั่ง SQL LEFT JOIN

ทำข้อ 2 เสร็จแล้วใช่ไหม ดูเฉลยกัน

select *
from employee
left join salary
on employee.employee_id
= salary.employee_id;

ข้อ 3 : พนักงานที่รับเข้าทำงาน 3 คนล่าสุดเป็นใคร อยู่ทีมไหน เข้ามาทำงานวันไหน ?

วิเคราะห์โจทย์ข้อที่ 3

เรียกดูข้อมูลชื่อ นามสกุล, อีเมล, วันที่จ้างงาน, ชื่อทีม หรือ รหัสทีม จาก 2 Table คือ Table Employee และ Table Team โดยคีย์หลักคือ team_id จากฐานข้อมูลทีม ซึ่งเงื่อนไขที่เราจะได้จัดลำดับการจ้างงานล่าสุดคือ RANK() OVER ซึ่งเป็น Window Function คือการคำนวณข้ามแถว ที่ใช้ร่วมกับ OVER

ดูวิธีการเขียนคำสั่ง RANK() Function

ก่อนจะไปเริ่มลงมือเขียนคำสั่ง ลองมาดูตัวอย่างการใช้ RANK() OVER ในสถานการณ์ที่เราอยากจะลำดับยอดขายของผลิตภัณฑ์แต่ละรายการตามยอดขายรายภูมิภาค เป็นตารางแบบนี้ให้หัวหน้าเรา

ซึ่งเรามีข้อมูล ที่ประกอบด้วย product_name, region, sale_amount ดังนั้นเราจะใช้คำสั่ง RANK() OVER เพื่อคำนวณลำดับ ตามด้วยกลุ่มหรือ Category ในที่นี้คือภูมิภาคด้วยการเขียน PARTITION BY region แล้วให้เรียง (ถ้าเกิดว่าไม่ได้เขียน PARTITION BY ไปจะถือว่าเราไม่ได้ต้องการแบ่งกลุ่มหรือไม่มีการแบ่งกลุ่มนะ) ปิดท้ายด้วย ORDER BY sales_amount DESC เพื่อลำดับยอดขายจากมากไปน้อย จบด้วยการตั้งชื่อคอลัมน์นี้ว่า sale_rank ลองเขียน Query แบบเต็ม ๆ จะได้แบบนี้

SELECT
  product_name,
  region,
  sales_amount,
  RANK() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS sales_rank
FROM
  sales;

ถึงตาผู้อ่านบ้างแล้ว มาลองประยุกต์การใช้ RANK() OVER ในโจทย์ข้อ 3 กัน

ทำข้อ 3 เสร็จแล้วใช่ไหม ดูเฉลยกัน

แบบที่ 1 หาคำตอบจากตารางหลัก (Single Table) ที่มี Team_ID

SELECT team_id, first_name, last_name, email, hire_date, 
RANK() OVER (ORDER BY hire_date) AS hire_date_rank
FROM employee
LIMIT 3;

แบบที่ 2 หาคำตอบจากตาราง Employee เชื่อมตาราง Team เพื่อให้ได้ Team_Name

SELECT 
    t.team_name, 
    e.first_name, 
    e.last_name, 
    e.email,
		e.hire_date
    RANK() OVER (ORDER BY e.hire_date ASC) AS hire_date_rank
FROM 
    employee e
JOIN 
    team t 
ON 
    e.team_id = t.team_id
LIMIT 3;

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

ข้อ 4 เงินเดือนเฉลี่ยของพนักงานแต่ละตำแหน่ง ?

4.1 หาเงินเดือนเลี่ยของแต่ละตำแหน่ง

4.2 หาเงินเดือนของแต่ละตำแหน่งเป็นสัดส่วนเท่าใดของเงินที่จ้างพนักงานทั้งหมด

วิเคราะห์โจทย์ข้อที่ 4

โจทย์ข้อนี้เกี่ยวข้องกับการประยุกต์ใช้ Agregation ซึ่งเป็นหนึ่งใน Function ของคำสั่ง SQL ประกอบด้วย ค่าเฉลี่ย (Avg), ค่าสูงสุด (Max), ค่าต่ำสุด (Min), ค่าผลรวม (Sum) และค่าของจำนวนนับ (Count) ตำแหน่งของ Agregation จะอยู่ได้สองที่คือ ใน SELECT cause หรือ HAVING cause (กรณีที่ใช้ร่วมกับ Group By)

เพิ่มเติมอีกนิดนึงสำหรับโจทย์ข้อนี้มีคำถามสองข้อย่อย ในส่วนของข้อย่อยที่สองสังเกต Data Type ของ Salary ให้ดี เพราะมีผลต่อการหาค่าเฉลี่ยในแต่ตำแหน่งงาน

ดูวิธีการเขียนคำสั่ง SQL Subquery

ทำข้อ 4 เสร็จแล้วใช่ไหม ดูเฉลยกัน

-- ข้อ 1 หาเงินเดือนเฉลี่ยของแต่ละตำแหน่ง
SELECT 
    role,
    AVG(salary) AS average_salary
FROM 
    salary
GROUP BY 
    role;

-- ข้อ 2 หาเงินเดือนของแต่ละตำแหน่งเป็นสัดส่วนเท่าใดของเงินที่จ้างพนักงานทั้งหมด
SELECT 
    S.role,
    SUM(S.salary) * 1.0 / (SELECT SUM(salary) FROM Salary) AS proportion
FROM 
    Team T
JOIN 
    Employee E ON T.team_id = E.team_id
JOIN 
    Salary S ON E.employee_id = S.employee_id
GROUP BY 
  S.role;

💡 สำหรับข้อย่อย 4.2 เราใช้ Sub-query เพื่อช่วยสร้างคอลัมน์ใหม่ตั้งชื่อว่า Proportion ซึ่งใช้เพื่อคำนวณค่าเฉลี่ยเงินเดือนในแต่ละ Role

ข้อ 5 : กำหนดให้พนักงานที่เงินเดือนสูงกว่า 75,000 เรียกว่า Manager เงินเดือนตั้งแต่ 45,000 ถึง 75,000 เรียก Senior ส่วนเงินเดือนน้อยกว่านั้นเรียกว่า Staff

วิเคราะห์โจทย์ข้อที่ 5

สำหรับโจทย์ในข้อนี้เป็นการใช้คำสั่ง CASE statement เพื่อระบุเงื่อนไขสามารถเขียนในรูปแบบนี้

SELECT
    CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ...
        ELSE default_result
    END AS alias_name
FROM
    table_name;

จากโจทย์เขียนออกมาาเป็นเงื่อนไข ได้แบบนี้

• เมื่อ salary > 75,000 ให้เรียกว่า ‘Manager’
• เมื่อ 45,000 ≥ salary ≤ 75,000 ให้เรียกว่า ‘Senior’
• เมื่อ salary < 45,000 ให้เรียกว่า ‘Staff’

ดูวิธีการเขียนคำสั่ง SQL CASE Expression

ทำข้อ 5 เสร็จแล้วใช่ไหม ดูเฉลยกัน

SELECT 
    E.employee_id,
    E.first_name,
    E.last_name,
    S.salary,
    CASE
        WHEN S.salary > 75000 THEN 'Manager'
        WHEN S.salary <= 75000 AND S.salary > 45000 THEN 'Senior'
        ELSE 'Staff'
    END AS employee_category
FROM 
    Employee E
JOIN 
    Salary S ON E.employee_id = S.employee_id;

ข้อ 6 : ถ้ามีพนักงานเข้ามาในวันที่ 14-Oct-23 ตำแหน่ง Marketing เงินเดือน 45,000 บาท เงินเดือนเฉลี่ยล่าสุดของตำแหน่ง Marketing จะเป็นเท่าใด ?

วิเคราะห์โจทย์ข้อที่ 6

ในกรณีที่มีพนักงานใหม่ต้องอัพเดตข้อมูลลง SQL Database ทีนี้เราสามารถ Insert คนใหม่เข้าตารางได้เลย

อย่าสับสนระหว่าง Insert กับ Update ล่ะ เพราะในโจทย์บอกว่าเป็นพนักงานใหม่ ดังนั้นต้องสร้าง employee_id ใหม่ต่างจากคำสั่ง Update ที่เป็นการแก้ไขบางค่าของข้อมูลของพนักงานที่มีข้อมูลอยู่แล้วหรือคือพนักงานที่มี employee_id อยู่แล้ว เมื่อทำการเพิ่มพนักงานใหม่เข้าฐานข้อมูลแล้วก็ลองคำนวณหาว่าตำแหน่ง Marketing มีเงินเดือนเฉลี่ยเท่าไร ?

ดูวิธีการเขียนคำสั่ง SQL SELECT INTO Statement

ทำข้อ 6 เสร็จแล้วใช่ไหม ดูเฉลยกัน

-- Insert พนักงานใหม่คนที่ 11 ในตาราง employee และ salary ตามข้อมูลที่โจทย์บอก
INSERT INTO employee (employee_id, first_name, last_name, email, hire_date, team_id)
VALUES (1, 'Warde', 'Remon', 'wremon0@zimbio.com','1-Jun-23', 1),
(2, 'Orlando', 'Groneway', 'ogroneway1@wikipedia.org', '22-Jun-23', 1),
(3, 'Amalita', 'Shorland', 'ashorland2@npr.org', '25-Mar-23', 1),
(4, 'Reinwald', 'Pickersail', 'rpickersail3@skyrock.com', '24-Jun-23', 1),
(5, 'Ilario', 'Anfrey', 'ianfrey4@google.com','2-Jan-23',1),
(6,'Davey', 'Frowen', 'dfrowen5@nsw.gov.au', '3-Mar-23', 6),
(7, 'Leigha', 'Randlesome', 'lrandlesome6@alibaba.com', '15-Oct-22', 6),
(8,'Junia', 'Yakovliv', 'jyakovliv7@artisteer.com', '31-Jul-23', 6),
(9, 'Rochell', 'Waggatt', 'rwaggatt8@opera.com', '15-Apr-23', 6),
(10, 'Moises', 'Ardley', 'mardley9@webnode.com', '21-Jun-23', 6),
(11, , , , '11-Oct-23', );

INSERT INTO Salary (employee_id, role, salary)
VALUES (1, 'Sales', 40153),
(2, 'Marketing', 46670),
(3, 'Legal', 71983),
(4, 'Sales', 15857),
(5, 'Software Engineer', 95174),
(6, 'Sales', 27819),
(7, 'Legal', 75323),
(8, 'Software Engineer', 90525),
(9, 'Tax Accountant', 85946),
(10, 'Marketing', 56575),
(11, 'Marketing', 45000);

-- หาเงินเดือนเฉลี่ยของ Marketing ใหม่
SELECT 
    role,
    AVG(salary) AS average_salary
FROM 
    salary
GROUP BY role
HAVING role = 'Marketing';

จบลงแล้วสำหรับโปรเจค HR Analytic ที่ชวนมาทำโจทย์ SQL ซึ่งลักษณะคำถามแบบนี้ เหล่าคนทำงานข้อมูลก็ใช้คำสั่งพื้นฐานเหล่านี้ในชีวิตประจำวัน ซึ่งรูปแบบการเขียนอาจจะทำต่างออกไปขึ้นอยู่กับการคำนึงเรื่องประสิทธิภาพ แต่สำหรับโจทย์ที่เราพึ่งทำกันไป ส่วนมากจะประยุกต์กับโจทย์อื่น ๆ เพื่อใช้ทดสอบในคนทำงานสายข้อมูลในระดับพื้นฐาน เดี๋ยวครั้งหน้าจะชวนไปทำโจทย์อื่น ๆ ที่น่าสนใจและใช้บ่อย ๆ ในคนทำงานสายข้อมูลกัน

ขอบคุณภาพประกอบจาก Photo by Jefferson Santos on Unsplash

บทความที่เกี่ยวข้อง

June 22, 2024June 22, 2024Data 7

มาฝึกใช้ Google Sheet Query Function ผ่าน Food Delivery Data กันเถอะ

มาเรียนรู้การใช้ Google Sheet Query Function ผ่านการฝึกวิเคราะห์ข้อมูลแพลตฟอร์ม Food Delivery กันเถอะ

April 7, 2024April 7, 2024Data 48

มาฝึกทำโปรเจค “Customer Review” ด้วย SQL แบบในชีวิตจริงกัน

เคยสงสัยกันไหมกันว่า Data Analyst ในชีวิตจริงพวกเราทำงานกันอย่างไร ? ในบทความนี้จะชวนมาวิเคราะห์ข้อมูลด้วยการเขียนโปรแกรม SQL ในธีม “Customer Review”

March 30, 2024March 30, 2024Data 56

สำรวจวงการซื้อขายงานวิจัยผ่านข้อมูลจากเว็บไซต์ Science Publisher

จากการวิเคราะห์ข้อมูลบนเว็บไซต์ Science Publisher ที่ให้บริการขายลิขสิทธิ์บทความวิจัยทั่วโลก ในหนึ่งปีจะมีบทความที่ขายในเว็บไซต์นี้อย่างต่ำก็ 600 บทความ มูลค่ารวมทั้งปีจะอยู่ราว ๆ 100 ล้านบาท

March 24, 2024March 30, 2024Data 37

มาฝึกทำโปรเจค “Car Dealer” ด้วย SQL แบบในชีวิตจริงกัน

เคยสงสัยกันไหมกันว่า Data Analyst ในชีวิตจริงพวกเราทำงานกันอย่างไร ? ในบทความนี้จะชวนมาวิเคราะห์ข้อมูลด้วยการเขียนโปรแกรม SQL ในธีม “Car Dealer”

• Facebook
• Twitter
• Line

Data Analytics Data Driven Decision Making exploratory data analysis hr analytics Quest Side Project sql

Apirat A.