前言
之前這篇(LeetCode 學習之路178. Rank Scores)裡面有提到排名是一個日常生活中處處可見的情境,充斥在我們的生活中。
除了最經典的排序,也有取最大最小等統計的計算,可能發生在取各班最高分等情境,像是這樣的情境也是相當容易出現在生活中,那麼這次我們一起來看看這個分組統計最大的問題吧!!!
題目
-- Employee table:
-- +--------------+---------+
-- | Column Name | Type |
-- +--------------+---------+
-- | id | int |
-- | name | varchar |
-- | salary | int |
-- | departmentId | int |
-- +--------------+---------+
-- Department table:
-- +-------------+---------+
-- | Column Name | Type |
-- +-------------+---------+
-- | id | int |
-- | name | varchar |
-- +-------------+---------+
列出各部門最高薪資的資料
-- Ex:
-- Employee table:
-- +----+-------+--------+--------------+
-- | id | name | salary | departmentId |
-- +----+-------+--------+--------------+
-- | 1 | Joe | 70000 | 1 |
-- | 2 | Jim | 90000 | 1 |
-- | 3 | Henry | 80000 | 2 |
-- | 4 | Sam | 60000 | 2 |
-- | 5 | Max | 90000 | 1 |
-- +----+-------+--------+--------------+
-- Department table:
-- +----+-------+
-- | id | name |
-- +----+-------+
-- | 1 | IT |
-- | 2 | Sales |
-- +----+-------+
-- Output
-- +------------+----------+--------+
-- | Department | Employee | Salary |
-- +------------+----------+--------+
-- | IT | Jim | 90000 |
-- | Sales | Henry | 80000 |
-- | IT | Max | 90000 |
-- +------------+----------+--------+
由 Output 可以得知,即使同一部門有兩個以上的人為該部門最高薪資,也都會全部列出
解題過程
一開始,我嘗試了最直觀的方式下手,看看能否用 MAX 然後用部門去分組
SELECT
dept.name AS "Department",
emp.name AS "Employee",
MAX(emp.salary) AS "Salary"
FROM Employee emp
LEFT JOIN Department dept
ON dept.id = emp.departmentId
GROUP BY dept.name, emp.name;
-- Output:
-- | Department | Employee | Salary |
-- | ---------- | -------- | ------ |
-- | Sales | Sam | 60000 |
-- | Sales | Henry | 80000 |
-- | IT | Max | 90000 |
-- | IT | Jim | 90000 |
-- | IT | Joe | 70000 |
但我似乎想太美了, GROUP BY dept.name, [emp.name](<http://emp.name>)
這段會在分組的時候也考慮到員工姓名,導致無法如我所想的只按照部門分組。
後來我想試試看能否先把結果變成一個子查詢(先在裡面用 MAX
),然後再從這裡去 JOIN
Department 表來帶入部門名稱
SELECT
dept.name AS "Department",
emp.name AS "Employee",
emp.salary AS "Salary"
FROM
(
SELECT
Employee.departmentId,
Employee.name as "name",
MAX(Employee.salary) AS "salary"
FROM Employee
GROUP BY Employee.departmentId, Employee.name
ORDER BY salary DESC
) as emp
LEFT JOIN Department dept
ON dept.id = emp.departmentId
-- Output:
-- | Department | Employee | Salary |
-- | ---------- | -------- | ------ |
-- | IT | Joe | 70000 |
-- | IT | Jim | 90000 |
-- | IT | Max | 90000 |
-- | Sales | Sam | 60000 |
-- | Sales | Henry | 80000 |
但事與願違,結果與剛剛基本上一樣,只差在排序。
後來發現到因為我需要拿到員工姓名, GROUP BY
的時候需要把員工姓名也考慮進去。
那有沒有可以不用 GROUP BY
的方法呢?
那如果直接對員工姓名下 MAX
會發生什麼?
SELECT
dept.name AS "Department",
emp.name AS "Employee",
emp.salary AS "Salary"
FROM
(
SELECT
Employee.departmentId,
MAX(Employee.name) as "name",
MAX(Employee.salary) AS "salary"
FROM Employee
GROUP BY Employee.departmentId
) as emp
LEFT JOIN Department dept
ON dept.id = emp.departmentId
-- Output:
-- | Department | Employee | Salary |
-- | ---------- | -------- | ------ |
-- | IT | Max | 90000 |
-- | Sales | Sam | 80000 |
好像變得比較像我所希望的樣子了,但題目是需要將同一部門有兩個以上的人為該部門最高薪資,也都會全部列出
換句話說,這種方式確定是行不通了
然而,我已經沒有任何的想法來解決這個問題了,最後我是參考著 ChatGPT 的回答來解決這個問題。
SELECT
dept.name AS "Department",
emp.name AS "Employee",
emp.salary AS "Salary"
FROM
(
SELECT
Employee.departmentId,
Employee.name AS "name",
Employee.salary AS "salary",
DENSE_RANK() OVER(PARTITION BY Employee.departmentId ORDER BY Employee.salary DESC) AS "rank"
FROM Employee
) as emp
LEFT JOIN Department dept
ON dept.id = emp.departmentId
where rank = 1;
這裡用到上一篇提過的 Window Function 在解決,主要是利用 Window Function 來產出分組排名,最後再篩選排名為第一名的資料出來。
結果
知識點
如同上一篇,只是這次比較著重在 PARTITION BY
,因為這個題目涉及到分組(部門)
-- 語法結構
<WINDOW_FUNC> OVER (
PARTITION BY <分區欄位名> => 用於分組,若沒有通通視為一組
ORDER BY <排序欄位名>
)
心得
在完成上一個題目後(題目連結),我一直在想排名的功能除了在得到名次之外,還有什麼用途呢?
我覺得這題算是帶給我了一個不一樣的經驗,通常想到取最大/最小值,想到的通常都是 MAX
, MIN
之類的函數,可是使用這種聚合函數需要對欄位進行 GROUP BY
,而且列出的欄位也需要放進 GROUP BY
子句中,導致在做這題的時候陷入困境,而這也讓我在完成上一題所建立的自信受到了一些打擊。
最後,在沒有更好的想法的時候,去詢問了 ChatGPT,而它的回答讓我有種茅塞頓開的感覺,我才意識到排名的功能可以這麼用。我覺得這次的經驗除了讓我更加了解到 Window Function 的強大之處,也讓我發覺到 AI 的強大。