-- beat 68%
-- 서브쿼리가 많은 쿼리
SELECT
CASE
WHEN mod(id, 2) = 0 THEN id - 1
WHEN id = (SELECT MAX(id) FROM Seat)
AND mod(
(SELECT COUNT(*) FROM Seat),
2) != 0
THEN id -- 마지막 id이고 홀수일 때,
ELSE id + 1
END as id,
student
FROM
Seat
ORDER BY
id ASC
;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------+
| 1 | PRIMARY | Seat | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using temporary; Using filesort |
| 3 | SUBQUERY | Seat | NULL | index | NULL | PRIMARY | 4 | NULL | 5 | 100.00 | Using index |
| 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------+
3 rows in set, 1 warning (0.05 sec)
-- type을 확인
-- 메인 쿼리가 전체 테이블 스캔(type이 'ALL')을 하고, 임시 테이블과 파일 정렬을 사용한다는 것
- 먼저 Seat를 Full scan -> temporary 영역을 이용하여 file sort 사용
- 서브쿼리 1: index scan -> primary key
- 서브쿼리 2: 'Select tables optimized away' :
데이터베이스가 쿼리 최적화를 통해 실제 테이블 액세스 없이 결과를 도출했다는 것을 의미
-- 서브쿼리 최소화
-- 하지만 아래 쿼리는 leetcode에서는 지원 x
SELECT COUNT(*) INTO @TotalCount FROM Seat;
EXPLAIN
SELECT
CASE
WHEN id % 2 = 0 THEN id - 1
WHEN id = @TotalCount THEN id
ELSE id + 1
END as id,
student
FROM
Seat
ORDER BY
id ASC;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | Seat | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
-- case문 + window 함수
select id,
student,
lead(student, 1)
over (order by id) as lead_student,
lag(student, 1)
over (order by id) as lag_student
from Seat;
+----+---------+--------------+-------------+
| id | student | lead_student | lag_student |
+----+---------+--------------+-------------+
| 1 | Abbot | Doris | NULL |
| 2 | Doris | Emerson | Abbot |
| 3 | Emerson | Green | Doris |
| 4 | Green | Jeames | Emerson |
| 5 | Jeames | NULL > self | Green |
+----+---------+--------------+-------------+
5 rows in set (0.03 sec)
-- 서브쿼리 최소화
-- Beats 96.74%
-- case문 + window 함수
-- 홀수 일 경우에는 lead + null 값 대체 (자기 자신)
-- 짝수 일 경우에는 lag
-- 홀수 일 경우, 자기 앞의 id을 가져옴(id-1 과 같은 효과)
-- coalesce(lead(student, 1) over(order by id), student)
-- coalesce : 만약 제일 상단의 행일 경우, null이기 때문에 자기 자신으로 대체
-- 짝수일 경우
-- 자기 뒤의 id을 가져옴 (id + 1과 같은 효과)
-- lag(student, 1) over(order by id)
select id,
case when id % 2 = 1
then coalesce(
lead(student, 1)
over(order by id), student
)
else lag(student, 1) over(order by id)
end as student
from Seat;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | Seat | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 2 warnings (0.02 sec)
-- io를 줄인 것을 확인
-- filesort의 경우, window 함수로 인해 발생
SQL
복사
[..] 부분은 생략 가능하다.
offset : 지정시 N번째 값을 가져온다.
- default_value : N번째 값이 없을 경우 default_value값을 가져온다.
PARTITION BY : 지정시 GROUP 별로 행 값을 가져온다.
LEAD(<expr> [,offset[,default_value]])
OVER ([PARTITION BY <expr>] ORDER BY <expr>)
SQL
복사
