เปิด LeetCode มาฝึกสมองกันอีกแล้ว คราวนี้ไม่ได้อยากรู้แค่มากที่สุดอันดับ 2 (ตามโพสต์ก่อนหน้านี้: link) ต้องการความ dynamic มากขึ้น อันดับไหนก็ได้ถ้าชั้นอยากรู้ เจ้าทาสทั้งหลายจงไปหาคำตอบมาให้ข้า .. ฮ่าๆๆ งั้นคงต้องเขียนเป็นฟังก์ชันแล้วล่ะ โอเคเริ่ม

Table: Table: Employee

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
- id is the primary key (column with unique values) for this table.
- Each row of this table contains information about the salary of an employee.

Write a solution to find the nth highest salary from the Employee table. If there is no nth highest salary, return null.

จงหา เงินเดือนที่สูงที่สุดตามอันดับที่ระบุ (nth) จากตารางทาส ถ้าไม่มีอันดับที่ต้องการส่ง null กลับมาจ้า

The result format is in the following example.

Example 1:

Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
n = 2

Output: 
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

Example 2:

Input: 
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+
n = 2

Output: 
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| null                   |
+------------------------+

โจทย์ยากอยู่นะเนี่ย เค้าให้เขียนเป็น function เรียกใช้งานด้วย ประเด็นคือหนุ่มไม่ค่อยได้เขียน function เท่าไหร่ แต่เขียน select clause อะบ่อย .. ก็เลยมีแนวคิดที่แบ่งออกเป็น 3 พาร์ท โดยประยุกต์ใช้คิวรี่เดิมจากโจทย์เก่า

select max(salary) as SecondHighestSalary 
from employee
where salary < (select max(salary) from employee)

• พาร์ท 1 หากรับ argument N เป็น 1 เข้ามาคือ ต้องการอันดับ 1 เราก็สามารถใช้ MAX() ส่งออกข้อมูลออกไปได้เลยใช่ม้า
• พาร์ท 2 ถ้า argument N ดันมากกว่า 1 หล่ะ เป็น 2, 3, 4, 5, … ก็น่าจะวนลูป แล้วใช่ค่ามากสุดแต่ละรอบ เพื่อตอบคำถามได้
• พาร์ท 3 ถ้าไม่มีอันดับที่ N ฟังก์ชัน MAX() จะตอบกลับเป็น NULL อยู่แล้ว

ไปดู Function กันเลย

CREATE OR REPLACE FUNCTION getNthHighestSalary (N INT)
RETURNS INT
BEGIN
    DECLARE res INT;
    DECLARE res_tmp INT;

    SELECT MAX(salary) INTO res_tmp FROM employee;

    IF N = 1 THEN
        SET res = res_tmp; 
    ELSE
        WHILE N > 1 DO
            SELECT MAX(salary) INTO res FROM employee WHERE salary < res_tmp; 
            SET res_tmp = res; 
            SET N = N - 1;
        END WHILE;
    END IF;

    RETURN res;
END;

1. กรณี N = 1

• อธิบายพาร์ท 1 ก่อนง่ายๆ: หากรับ argument N เป็น 1 เข้ามาคือ ต้องการค่ามากที่สุดอันดับ 1 เราสามารถ SELECT MAX() ของตาราง Employee ได้เลย
• และเก็บไว้ในตัวแปร res_temp ไว้ก่อน
• จากนั้นจะมา execute IF ต่อ และเงื่อนไข N = 1 เป็นจริง ทำให้เซ็ตตัวแปร res = res_temp ซึ่งเท่ากับ 300 อยู่ตอนนี้
• และหลุดฟังก์ชัน IF ออกมา เสร็จแล้วจึงสามารถ return res ออกมาเป็นผลลัพธ์ได้เลย

2. กรณี N = 2

• รับ argument N เป็น 2 ยังต้องผ่าน SELECT MAX() ของตาราง Employee และเก็บค่า 300 ไว้ใน res_temp ก่อน จากนั้นเข้า ELSE เนื่องจาก 2 ไม่เท่ากับ 1 ต่อด้วย WHILE Loop เพราะ 2 > 1 อยู่
• มาที่ SELECT MAX() โดยที่ WHERE salary ที่น้อยกว่า res_temp ซึ่งตอนนี้เท่ากับ 300 อยู่ ค่า MAX() ที่ได้จึงเท่ากับ 200 เก็บค่าไว้ใน res
• บรรทัดต่อมาเซ็ต res_temp = res จะได้ใช้ตอน กรณีที่ N > 2 (ตอนนี้เอาไว้ก่อน)
• จากนั้นเซ็ต N = N (ที่เป็น 2) ลบด้วย 1 นั่นคือตอนนี้ N เท่ากับ 1 แล้ว
• และเรายังอยู่ใน WHILE Loop อยู่ วนกลับไปอีกครั้งตามลูกศรสีแดง แต่เพราะ 1 ไม่ได้มากกว่า 1 (1 > 1 เป็น FALSE) จึงจบลูป WHILE และสามารถ return ค่า res (มีค่า 200) ออกไปเป็นคำตอบได้เลย

3. กรณี N = 3 หรือ มากกว่านั้น

• ในรอบแรกรับ argument N เป็น 3 ยังต้องผ่าน SELECT MAX() ของตาราง Employee และเก็บค่า 300 ไว้ใน res_temp ก่อน จากนั้นเข้า ELSE เนื่องจาก 3 ไม่เท่ากับ 1 ต่อด้วย WHILE Loop เพราะ 3 > 1 อยู่
• มาที่ SELECT MAX() โดยที่ WHERE salary ที่น้อยกว่า res_temp ซึ่งตอนนี้เท่ากับ 300 อยู่ ค่า MAX() ที่ได้จึงเท่ากับ 200 เก็บค่าไว้ใน res
• บรรทัดต่อมาเซ็ต res_temp = res ตอนนี้ทั้งคู่เท่ากับ 200 แล้ว
• จากนั้นเซ็ต N = N (ที่เป็น 3) ลบด้วย 1 นั่นคือตอนนี้ N เท่ากับ 2
• และเรายังอยู่ใน WHILE Loop อยู่ วนกลับไปอีกครั้งตามลูกศรสีแดง โดยเงื่อนไข 2 > 1 ยังเป็นจริง TRUE อยู่จึงต้องเข้าลูปอีกรอบ (ตามรูปถัดไป)

• ใน ลูปก่อนหน้านี้ N เป็น 2 ยังสามารถเข้า WHILE Loop ได้เพราะ 2 > 1 อยู่
• มาที่ SELECT MAX() โดยที่ WHERE salary ที่น้อยกว่า res_temp ซึ่งตอนนี้เท่ากับ 200 ทำให้ค่า MAX() ที่ได้จึงเท่ากับ 100 เก็บค่าไว้ใน res
• บรรทัดต่อมาเซ็ต res_temp = res ทำให้ทั้งคู่เท่ากับ 100
• จากนั้นเซ็ต N = N (ที่เป็น 2) ลบด้วย 1 นั่นคือตอนนี้ N เท่ากับ 1
• เมื่อวนกลับไปอีกครั้งตามลูกศรสีแดง N=1 โดยเงื่อนไข 1 > 1 เป็นเท็จ FALSE แล้ว จึงต้องจบลูป และสามารถ return ค่า res (มีค่า 100) ออกไปเป็นคำตอบ
• และในกรณีที่ input ที่รับเข้ามากกว่า 3 ตามตัวอย่าง มันก็จะวน WHILE ลูปไปเรื่อยๆ จนเงื่อนไขเป็นเท็จจึงจะได้คำตอบสุดท้ายออกมา
• และถ้าวนลูปจนไม่มีคำตอบ Function MAX() จะตอบกลับเป็น NULL อยู่แล้วครับ

ทดสอบใช้งาน

ใช้งานบน Localhost

บน LeetCode หล่ะ

ในที่สุดกว่าจะได้ เรียกว่าข้ามขีดจำกัดตัวเองไปอีกขั้นแล้ว มาเขียนฟังก์ชันใช้เองละ อิอิ เป็นไงมั้งครับ อาจจะเขียนยาวนิดนึงน้า พยายามจะอธิบายออกมาให้ง่ายสุดแล้วอ่าครับ (เอ๊ะหรือทำให้ยากขึ้น ขอโท้ดค้าบบ 🙏)

---

ps. และใช่คับ เค้าโค้ดง่ายกว่าหนุ่มอีกแล้วว 😲

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
	with cte as (
		select *, dense_rank()over(order by salary desc) as rnk from employee
	)
	select distinct ifnull(salary, null) from cte
	where rnk = N
);
END

ขอบคุณโจทย์ที่ใช้ 177. Nth Highest Salary

หวังว่าจะเป็นประโยชน์กับผู้อ่านทุกท่านนะครับ คอมเม้นต์ให้คำแนะนำได้นะครับ

แปลผิดแปลถูก + มี typo ขออภัยครับ 🙏 จะพยายามให้ดีขึ้นครับ

ขอบคุณครับ