[LeetCode] 1075. Project Employees I - SQL

링크: https://leetcode.com/problems/project-employees-i/

Table: Project

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| project_id  | int     |
| employee_id | int     |
+-------------+---------+
(project_id, employee_id) is the primary key of this table.
employee_id is a foreign key toEmployee table.
Each row of this table indicates that the employee with employee_id is working on the project with project_id.

Table: Employee

+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| employee_id      | int     |
| name             | varchar |
| experience_years | int     |
+------------------+---------+
employee_id is the primary key of this table. It's guaranteed that experience_years is not NULL.
Each row of this table contains information about one employee.

Write an SQL query that reports the average experience years of all the employees for each project, rounded to 2 digits.

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input:
Project table:
+-------------+-------------+
| project_id  | employee_id |
+-------------+-------------+
| 1           | 1           |
| 1           | 2           |
| 1           | 3           |
| 2           | 1           |
| 2           | 4           |
+-------------+-------------+
Employee table:
+-------------+--------+------------------+
| employee_id | name   | experience_years |
+-------------+--------+------------------+
| 1           | Khaled | 3                |
| 2           | Ali    | 2                |
| 3           | John   | 1                |
| 4           | Doe    | 2                |
+-------------+--------+------------------+
Output:
+-------------+---------------+
| project_id  | average_years |
+-------------+---------------+
| 1           | 2.00          |
| 2           | 2.50          |
+-------------+---------------+
Explanation: The average experience years for the first project is (3 + 2 + 1) / 3 = 2.00 and for the second project is (3 + 2) / 2 = 2.50

---

문제풀이

목표: 각 프로젝트에 대한 모든 employees 의 평균 experience year 를 구하라

• 소수점 둘째자리로 반올림

최종코드

SELECT
    p.project_id,
    ROUND(SUM(e.experience_years) / COUNT(p.employee_id), 2) average_years
FROM Employee e
    JOIN Project p
    ON e.employee_id = p.employee_id
GROUP BY p.project_id;

설명

FROM 절

FROM Employee e
    JOIN Project p
    ON e.employee_id = p.employee_id

• Employee 테이블과 Project 테이블을 employee_id 컬럼을 기준으로 JOIN

GROUP BY 절

GROUP BY p.project_id;

• 결과는 project 별 평균 경력 연수를 구해야 하므로 project_id 를 가진 여러 행들을 하나의 그룹으로 묶는다.

SELECT 절(핵심)

SELECT
    p.project_id,
    ROUND(SUM(e.experience_years) / COUNT(p.employee_id), 2) average_years

• 사실 복잡할건 없는데, 각 project_id 그룹에 속한 직원들의 총 근무 연수를 그 직원 수로 나눠 평균을 구하면 된다.