쿼리 공부 모음
SQL 공부
HackerRank에서 문제를 풀면서 특별히 생각나는 문제들을 모아 보았습니다. 사용 언어는 오라클 SQL 입니다.
소수 골라내기
1000보다 작은 소수를 조회하는 문제입니다.
1
2
3
4
5
6
7
8
9
WITH A as (SELECT LEVEL + 1 AS Num FROM DUAL CONNECT BY LEVEL < 1000)
, B as (SELECT LEVEL AS Den FROM DUAL CONNECT BY LEVEL < SQRT(1000))
SELECT Num
FROM A, B
WHERE Num >= POWER(Den, 2)
AND MOD(Num, Den) = 0
GROUP BY Num
HAVING COUNT(*) = 1
ORDER BY Num;
또는
1
2
3
4
5
WITH A as (SELECT LEVEL + 1 AS Num FROM DUAL CONNECT BY LEVEL < 1000)
, B as (SELECT LEVEL + 1 AS Den FROM DUAL CONNECT BY LEVEL < SQRT(1000))
SELECT Num
FROM A
WHERE NOT EXISTS (SELECT * FROM B WHERE Num >= POWER(Den, 2) AND MOD(Num, Den) = 0);
두번째 쿼리문과 달리, 첫번째 쿼리문은 GROUP BY 절을 사용하기 때문에 실행계획에 정렬이 포함되고 join 횟수가 불필요하게 많아지므로 비효율적입니다. 또한 이렇게 뽑힌 데이터가 정렬되어 있지 않기 때문에 ORDER BY절을 필요로 합니다. 그러므로 숫자 $N$이 커질 경우에는 두번째 쿼리문처럼 서브쿼리를 사용하는게 좋습니다.
Time interval 만들기
이 문제의 discussions 란을 보면, 대부분 project의 시작날짜와 종료날짜가 될 수 있는 후보들을 각각 뽑은 후 각 시작날짜에 대응되는 종료날짜를 짝지어주는 방법으로 풀었습니다. 예를 들면 다음과 같습니다.
1
2
3
4
5
6
SELECT Start_Date, MIN(End_Date)
FROM (SELECT Start_Date FROM Projects WHERE Start_Date NOT IN (SELECT End_Date FROM Projects))
, (SELECT End_Date FROM Projects WHERE End_Date NOT IN (SELECT Start_Date FROM Projects))
WHERE Start_Date < End_Date
GROUP BY Start_Date
ORDER BY DATEDIFF(MIN(End_Date), Start_Date), Start_Date;
하지만 계층적 쿼리를 이용하면 좀 더 간단하게 해결이 가능합니다.
1
2
3
4
5
6
7
SELECT Start_Date
, MAX(CONNECT_BY_ROOT End_Date)
FROM Projects
WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR Start_Date = End_Date
GROUP BY Start_Date
ORDER BY MAX(LEVEL), Start_Date;
이 방법은 일반적인 트리 구조에서도 root와 leaf를 매칭시켜 뽑아낼 수 있기 때문에 매우 유용하다고 생각합니다.
15 Days of Learning SQL
2016년 3월 1일부터 2016년 3월 15일까지 15일 동안 매일 문제를 푼 사람들을 조회하는 문제입니다. 여기서 저는 자료형과 관계없이 날짜가 바뀌더라도 돌아가는 강건한 쿼리를 만들고자 했습니다. 이 문제를 통해 WHERE 구문에도 두 서브쿼리를 비교하는 문법이 가능하다는 걸 알게 되었습니다. 또한 GROUP BY절이 사용된 쿼리에서 윈도우 함수 속에 KEEP(… ORDER BY COUNT(…))와 같이 집계함수를 끼워넣을 수 있다는 것도 알 수 있었습니다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
SELECT A.submission_date
, A.num_hackers
, B.hacker_id
, C.name
FROM (
SELECT submission_date
, COUNT(DISTINCT hacker_id) AS num_hackers
FROM Submissions A
WHERE (
SELECT COUNT(DISTINCT submission_date)
FROM Submissions
WHERE submission_date < A.submission_date
) = (
SELECT COUNT(DISTINCT submission_date)
FROM Submissions
WHERE hacker_id = A.hacker_id
AND submission_date < A.submission_date
)
GROUP BY submission_date
) A
LEFT OUTER JOIN (
SELECT submission_date,
hacker_id
FROM (
SELECT submission_date
, hacker_id
, ROW_NUMBER() OVER(
PARTITION BY submission_date
ORDER BY COUNT(submission_id) DESC
, hacker_id
) AS r
FROM Submissions
GROUP BY submission_date, hacker_id
)
WHERE r = 1
) B
ON A.submission_date = B.submission_date
JOIN Hackers C
ON B.hacker_id = C.hacker_id;