text
У сучасному світі, де дані мають вирішальне значення, організації прагнуть отримати інсайти з своїх даних для прийняття обґрунтованих рішень. Одне з поширених завдань аналітики — це визначення найкращих працівників або найбільших заробітчан в різних відділах компанії. У цьому блозі ми розглянемо, як визначити найбільш високооплачуваних працівників у кожному відділі за допомогою SQL. Ми пройдемо через постановку задачі, розберемо структуру даних і реалізуємо покрокове рішення.
Фото: Статуя Свободи та Манхеттен з парому, Нью-Йорк. Фото: Таснім та Кавсар
Таблиця: 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 -- Таблиця працівників (містить дані працівників)
LEFT JOIN
Department AS d -- Таблиця відділів (містить дані відділів)
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 profile або підпишіться на мою розсилку для останніх блогів, і натискайте кнопку оплесків, щоб підтримати написання. Не соромтеся ставити запитання в коментарях.
text
SQL залишається актуальним навіть із появою нових технологій у сфері обробки даних завдяки своєму широкому використанню та здатності масштабуватися разом з ростом вимог до даних.
Висновок
SQL — це більше, ніж просто інструмент, це основна мова, яка відкриває можливості даних. Вона є основою реляційних баз даних і має вирішальне значення для маніпулювання даними, аналізу та звітності. Якщо ви хочете побудувати кар'єру в будь-якій ролі, що пов'язана з даними, SQL — це основа, з якої варто почати.
Перекладено з: What is SQL and Why It’s Important in the Data-Driven World?