sql 테이블 자기 자신과 조인하기
테이블 자기 자신과 조인?
- leetcode에서 sql 문제 중 자기 자신과 join하는 문제를 묶었다.
- 업무를 할 때 보통 서로 다른 테이블 간 join을 하였다. 자기 자신과 조인을 하는 경우는 거의 없다. 하지만 리트코드가 제공하는 문제 중 테이블 자신과 join을 하는 경우가 많았다. 이 방식으로 쉽게 해결할 수 있는 문제가 많았다. 새로운 시야를 얻었다. 문제를 풀고 해소한 내용을 정리한다.
어제보다 더운 날은?
요구사항
- https://leetcode.com/problems/rising-temperature
Input:
Weather table:
+----+------------+-------------+
| id | recordDate | temperature |
+----+------------+-------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+----+------------+-------------+
Output:
+----+
| id |
+----+
| 2 |
| 4 |
+----+
Explanation:
In 2015-01-02, the temperature was higher than the previous day (10 -> 25).
In 2015-01-04, the temperature was higher than the previous day (20 -> 30).
해소
- Weather 테이블 두 개를 join 하며, 조인의 기준은 날짜로 한다. 첫 번째 테이블과 두 번째 테이블의 날짜를 한 레코드씩 어긋나게 조인하여, 온도를 뺀다.
select w2.id
from weather w1
join weather w2
on date_add(w1.recordDate, interval +1 day) = w2.recordDate
where w1.temperature < w2.temperature;
짝수와 홀수 순서 바꾸기
- https://leetcode.com/problems/exchange-seats/
요구사항
Input:
Seat table:
+----+---------+
| id | student |
+----+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+----+---------+
Output:
+----+---------+
| id | student |
+----+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+----+---------+
Explanation:
Note that if the number of students is odd, there is no need to change the last one's seat.
해소
- 짝수를 가진 사람과 홀수를 가진 사람으로 테이블을 분리했다. 그리고 그것을 합치는 방식으로 해소했다.
- 이 때 사용한 기능 중 하나는 mysql user variable(@var) 인데, 각 가상 테이블의 레코드마다 auto-increment 의 효과를 만들었다.
select
cast(@num3:=@num3+1 as signed integer)as id
,student
from (
(
select
@num:=@num+1 as num
, s.id
, s.student
from
(select @num:=0) a,
seat s
where
mod(s.id, 2)=0
)
union
(select
@num2:=@num2+1 as num
,s.id
,s.student
from
(select @num2:=0) a,
seat s
where
mod(s.id, 2)=1
)
order by num asc, id desc
)tb
, (select @num3:=0) c;
- 아래는 best practice 이다. 확실히 아래가 더 직관적이다. 각 레코드마다 홀수, 짝수를 비교
mod()
하고 그것이 홀수이면 +1 짝수면 -1을 하는 형태로 하였다. - 그런데 속도는 위의 쿼리가 더 빨랐다. 그러나 더 직관적인 쿼리는 아무래도 아래의 것이라 생각한다.
select
(case
when mod(id, 2) = 0 then id - 1
when id = counts then id -- 총 갯수가 홀수일 경우 마지막 레코드는 그대로 둔다.
when mod(id, 2) = 1 then id + 1
end) as id
, student
from
seat,
(select
count(*) as counts
from
seat) as seat_counts
order by id asc
두 번째로 큰 값은?
- https://leetcode.com/problems/second-highest-salary/
요구사항
Input:
Employee table:
+----+--------+
| id | salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
Output:
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
해소
select
max(salary) SecondHighestSalary
from employee
where salary not in (select max(salary) from employee)
세 번 같은 값이 연속된 데이터는?
- https://leetcode.com/problems/consecutive-numbers/
요구사항
Input:
Logs table:
+----+-----+
| id | num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
Output:
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
Explanation: 1 is the only number that appears consecutively for at least three times.
해소
- 기존의 테이블에 칼럼을 두 개 추가한다. 첫 번째는 id가 하나 적은 레코드의 숫자를 자신의 숫자와 뺀 값이며, 두 번째는 id가 두 개 적은 레코드의 숫자를 뺀 값이다. 뺀 값이 두 개가 모두 0 이면 동일함을 보장한다.
select
tb.num ConsecutiveNums
from (
select
l.id,
l.num,
(l.num - (select a.num from logs a where a.id = (l.id - 1))) diff1,
(l.num - (select a.num from logs a where a.id = (l.id - 2))) diff2
from logs l
where l.id > 1
) tb
where tb.diff1 = 0 and tb.diff2 = 0
group by tb.num
- 위의 방법은 끔찍하게 느렸다. 아래의 방법은 best practice 이며 매우 직관적이다.
select *
from
logs l1,
logs l2,
logs l3
where
l1.id = l2.id - 1
and l2.id = l3.id - 1
and l1.num = l2.num
and l2.num = l3.num;
각 부서마다 최고의 연봉을 받는 직원의 목록
- https://leetcode.com/problems/department-highest-salary/
요구사항
Input:
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 |
+------------+----------+--------+
Explanation: Max and Jim both have the highest salary in the IT department and Henry has the highest salary in the Sales department.
해소
- 연봉(salary)로 비교해야 하기 때문에 먼저 부서 별 max 값을 추출했다. 해당 max 값과 부서의 값이 일치하는 데이터를 join을 통해 추출하였다.
select
d.name Department ,
e.name Employee ,
e.salary Salary
from
employee e
join (
select
max(departmentId) departmentId
, max(salary) salary
from employee
group by departmentId
) tb
on
tb.departmentId = e.departmentId
and e.salary = tb.salary
join
department d
on e.departmentId = d.id
- 아래는 best practice 이다. 위의 방법과 매우 유사하지만, where 절을 통해 해소했다.
- where 절의 비교를 괄호를 통해 두 개 동시에 할 수 있다.
select
d.name Department ,
e.name Employee ,
e.salary Salary
from
employee e
join
department d
on e.departmentId = d.id
where (e.departmentId , e.salary)
in
(
select
max(departmentId) departmentId
, max(salary) salary
from employee
group by departmentId
)
부서별 연봉 3위 이상인 직원의 목록
- https://leetcode.com/problems/department-top-three-salaries/
요구사항
Input:
Employee table:
+----+-------+--------+--------------+
| 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 |
+----+-------+--------+--------------+
Department table:
+----+-------+
| id | name |
+----+-------+
| 1 | IT |
| 2 | Sales |
+----+-------+
Output:
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Joe | 85000 |
| IT | Randy | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
Explanation:
In the IT department:
- Max earns the highest unique salary
- Both Randy and Joe earn the second-highest unique salary
- Will earns the third-highest unique salary
In the Sales department:
- Henry earns the highest salary
- Sam earns the second-highest salary
- There is no third-highest salary as there are only two employees
해소
- 아래는 best practice 이다. 자기 자신보다 임금이 높은 사람의 횟수를 세고, 그것의 임금을 group by 를 하여, 그 임금의 갯수가 3개 초과하는 경우를 where로 한다.
select
d.name as Department
, e1.name as employee
, e1.salary
from
employee e1
join department d
on d.id = e1.departmentId
where
3 > (
select
count(distinct e2.salary)
from employee e2
where e2.salary > e1.salary
and e2.departmentId = e1.departmentId
)
100명 이상 이용한 날
- https://leetcode.com/problems/human-traffic-of-stadium/
요구사항
Input:
Stadium table:
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 1 | 2017-01-01 | 10 |
| 2 | 2017-01-02 | 109 |
| 3 | 2017-01-03 | 150 |
| 4 | 2017-01-04 | 99 |
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-09 | 188 |
+------+------------+-----------+
Output:
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-09 | 188 |
+------+------------+-----------+
Explanation:
The four rows with ids 5, 6, 7, and 8 have consecutive ids and each of them has >= 100 people attended. Note that row 8 was included even though the visit_date was not the next day after row 7.
The rows with ids 2 and 3 are not included because we need at least three consecutive ids.
해소
- 날짜는 함정으로 id만 봐야 한다.
- 사람의 숫자가 100명을 넘는 id를 추출하고, 아래의 조건을 만족해야 한다.
- 연속된 숫자가 앞에 두 개가 있거나
- 뒤에 두 개가 있거나
- 앞 뒤로 두 개가 있거나
select
*
from
stadium
where
id in
(select
*
from
(select id from stadium s where people >= 100) tb
where
(select
count(1)
from
(select id from stadium s where people >= 100) tb3
where
tb3.id in (tb.id - 1, tb.id + 1)
) > 1
or
(select
count(1)
from
(select id from stadium s where people >= 100) tb1
where
tb.id between tb1.id + 1 and tb1.id + 2
) > 1
or
(select
count(1)
from
(select id from stadium s where people >= 100) tb2
where
tb2.id between tb.id + 1 and tb.id + 2
) > 1
)