В сучасному світі, де все більше уваги приділяється даним, організації прагнуть отримати корисну інформацію з своїх наборів даних для прийняття обґрунтованих рішень. Одним з поширених аналітичних завдань є визначення найкращих працівників або високих заробітків у різних відділах компанії. У цьому блозі ми розглянемо, як визначити найвищих за заробітною платою працівників у кожному відділі за допомогою SQL. Ми крок за кроком пройдемо через завдання, зрозуміємо структуру даних і реалізуємо рішення.
Фото: Статуя Свободи і вид на Манхеттен з парома, Нью-Йорк. Автор: Tasnim та Kawsar
Таблиця: Employee
+--------------+---------+
| Назва стовпця | Тип |
+--------------+---------+
| id | int |
| name | varchar |
| salary | int |
| departmentId | int |
+--------------+---------+
id — це первинний ключ (стовпець з унікальними значеннями) для цієї таблиці.
departmentId — це зовнішній ключ (стовпець посилання) на ID з таблиці
Department.
Кожен рядок цієї таблиці вказує на ID, ім'я та зарплату працівника.
Також містить ID їхнього відділу.
Таблиця: Department
+-------------+---------+
| Назва стовпця | Тип |
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
id — це первинний ключ (стовпець з унікальними значеннями) для цієї таблиці.
Кожен рядок цієї таблиці вказує на ID відділу та його назву.
Керівництво компанії хоче побачити, хто заробляє найбільше у кожному з відділів компанії. Високий заробіток в відділі — це працівник, чия зарплата входить до топ-3 унікальних зарплат для цього відділу. Напишіть рішення для того, щоб знайти працівників, які є високими заробітками в кожному з відділів. Поверніть результат у будь-якому порядку.
Вхідні дані:
Таблиця Employee:
+----+-------+--------+--------------+
| id | name | salary | departmentId |
+----+-------+--------+--------------+
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
+----+-------+--------+--------------+
Кожен рядок цієї таблиці вказує на ID, ім'я та зарплату працівника.
Також містить ID їхнього відділу.
Таблиця Department:
+----+-------+
| id | name |
+----+-------+
| 1 | IT |
| 2 | Sales |
+----+-------+
Кожен рядок цієї таблиці вказує на ID відділу та його назву.
Вихідні дані:
+------------+----------+--------+
| Відділ | Працівник| Зарплата |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Joe | 85000 |
| IT | Randy | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
Пояснення:
В відділі IT:
- Max заробляє найвищу унікальну зарплату
- Randy та Joe отримують другу за величиною унікальну зарплату
- Will отримує третю за величиною унікальну зарплату
В відділі Sales:
- Henry заробляє найвищу зарплату
- Sam заробляє другу за величиною зарплату
- Третьої за величиною зарплати немає, оскільки є тільки два працівники
Обмеження: В таблицях немає працівників з однаковими іменами, зарплатами
та відділами.
SQL схема:
Create table If Not Exists Employee (id int, name varchar(255), salary int, departmentId int)
Create table If Not Exists Department (id int, name varchar(255))
Truncate table Employee
insert into Employee (id, name, salary, departmentId) values ('1', 'Joe', '85000', '1')
insert into Employee (id, name, salary, departmentId) values ('2', 'Henry', '80000', '2')
insert into Employee (id, name, salary, departmentId) values ('3', 'Sam', '60000', '2')
insert into Employee (id, name, salary, departmentId) values ('4', 'Max', '90000', '1')
insert into Employee (id, name, salary, departmentId) values ('5', 'Janet', '69000', '1')
insert into Employee (id, name, salary, departmentId) values ('6', 'Randy', '85000', '1')
insert into Employee (id, name, salary, departmentId) values ('7', 'Will', '70000', '1')
Truncate table Department
insert into Department (id, name) values ('1', 'IT')
insert into Department (id, name) values ('2', 'Sales')
Рішення для MySQL, PostgreSQL та MS SQL Server:
-- Крок 1: Створення спільного виразу таблиці (CTE) з ім'ям "cte"
WITH cte AS (
-- Вибір відділу, імені працівника, зарплати та рангу
SELECT
d.name AS Department, -- Отримання назви відділу
e.name AS Employee, -- Отримання імені працівника
salary AS Salary, -- Отримання зарплати працівника
-- Використання DENSE_RANK() для ранжування зарплат працівників всередині кожного відділу
-- Рейтинг починається знову для кожного відділу завдяки PARTITION BY d.name
-- Зарплати впорядковані за спаданням, тому найвища зарплата отримує ранг 1
DENSE_RANK() OVER (
PARTITION BY d.name -- Перезапуск ранжування для кожного відділу
ORDER BY salary DESC -- Порядок зарплат за спаданням
) AS rnk -- Присвоєння рангу стовпцю з ім'ям "rnk"
FROM
Employee AS e -- Таблиця Employee (містить деталі працівників)
LEFT JOIN
Department AS d -- Таблиця Department (містить деталі відділів)
ON
e.departmentId = d.id -- З’єднання таблиць через departmentId для з’єднання працівників з їх відділами
)
-- Крок 2: Використовуємо результат CTE в основному запиті
SELECT
Department, -- Отримання назви відділу
Employee, -- Отримання імені працівника
Salary -- Отримання зарплати
FROM
cte -- Використовуємо тимчасовий результат, створений CTE
WHERE
rnk IN (1, 2, 3); -- Фільтрація, щоб включити лише працівників, які займають 1, 2 чи 3 місце за зарплатою у кожному відділі
Щасливого аналізу!
Джерела: [1] Leetcode Source: department-top-three-salaries
Слідкуйте за мною на LinkedIn Instagram Medium профіль або підпишіться на мій список розсилки для останніх блогів та натискайте на кнопку аплодування, щоб підтримати написання. Не соромтесь ставити питання в коментарях.
Не забувайте поділитися посиланням на блог з вашими друзями або контактами в LinkedIn.
[
Топ три зарплати в відділах: Leetcode Pandas Har
В сучасному світі, орієнтованому на дані, організації прагнуть отримати інформацію з своїх наборів даних для прийняття обґрунтованих рішень. Один…
kawsar34.medium.com
](/department-top-three-salaries-leetcode-pandas-har-faa9fcac1169?source=post_page-----4b3cd4b21374--------------------------------)
[
Як здати сертифікацію AWS Machine Learning Engineer Associate
Опануйте підготовку даних, розробку моделей машинного навчання, впровадження та оркестрацію робочих процесів машинного навчання, моніторинг рішень МН…
kawsar34.medium.com
](/how-to-pass-aws-machine-learning-engineer-associate-certification-91b7b4370906?source=post_page-----4b3cd4b21374--------------------------------)
Перекладено з: Department Top Three Salaries: Leetcode SQL