2025-01-09 TIL (SQL codekata, Sub Query)

SQL CODEKATA

15 Days of Learning SQL | HackerRank

 

문제 1

줄리아씨는 15일 동안 SQL 학습 대회를 진행했습니다. 대회 시작일은 2016년 3월 1일이었고, 종료일은 2016년 3월 15일이었습니다. 

1. 매일 최소 제출 횟수를 기록한 고유 해커의 총 수를 출력
2. 매일 최대 제출 횟수를 기록한 해커의 hacker_id와 이름 찾기
3. 해커가 최대 제출 횟수를 두 번 이상 기록한 경우, 가장 낮은 hacker_id 출력
4. 대회를 날짜별로 정렬

 

테이블

• Hacker
  
  • hacker_id
  • name
• Submissions
  • submission_date
  • submission_id
  • hacker_id
  • score

 

풀이를 하기전.

매일 제출한 해커를 구하는 것이 핵심이었다.

우선 윈도우 함수 Dense_Rank()을 써서 먼저 순서를 매긴뒤

날짜와 비교하여 1일 부터 15일까지 연속적으로 되는지를 확인할려고 했다.

 

문제발견

select *,
    DENSE_RANK() over (partition by hacker_id order by submission_date) as r
from Submissions

-- ERROR 1064 (42000) at line 17: You have an error in your SQL syntax; 
-- check the manual that corresponds to your MySQL server version 
-- for the right syntax to use near '(partition by hacker_id order by submission_date) as r
-- from Submissions' at line 2

 

하지만 버젼을 확인하라는 말을 해줬다.

 

select version();

5.7.27-0ubuntu0.18.04.1

 

MYSQL 8.0부터 지원을 한다네,,?

 

SET @dense_rank = 0;
SET @prev_hacker_id = NULL;
SET @prev_date = NULL;

SELECT
    hacker_id,
    submission_date,
    @dense_rank := IF(@prev_hacker_id = hacker_id AND @prev_date = submission_date, @dense_rank, 
                      IF(@prev_hacker_id = hacker_id, @dense_rank + 1, 1)) AS dense_rank,
    @prev_hacker_id := hacker_id AS dummy_hacker,
    @prev_date := submission_date AS dummy_date
FROM (
    SELECT hacker_id, submission_date
    FROM Submissions
    ORDER BY hacker_id, submission_date
) AS sorted_submissions;

 

GPT씨가 Dense_Rank()을 구현을 이렇게 한다고 한다.

풀어서 본다면

 

1. SET 함수로 각 변수를 초기화

SET @dense_rank = 0;
SET @prev_hacaker_id = NULL;
SET @prev_date = NULL;

 

@dense_rank : 현재 그룹 내 순위

@prev_hacker_id : 이전 행의 hacker_id

@prev_date : 이전 행의 submission_date

 

 

2. Table 정렬

FROM (
    SELECT hacker_id, submission_date
    FROM Submissions
    ORDER BY hacker_id, submission_date
) AS sorted_submissions;

 

hacker_id별 submission_date 오름차순

 

3. 조건문

@dense_rank := IF(@prev_hacker_id = hacker_id AND @prev_date = submission_date, 
                  @dense_rank, 
                  IF(@prev_hacker_id = hacker_id, @dense_rank + 1, 1))

 

 

1. hacker_id와 submission_date가 동일한 경우:
   • 순위를 유지합니다 (@dense_rank 그대로).
2. hacker_id는 같지만 submission_date가 다른 경우:
   • 순위를 1 증가시킵니다 (@dense_rank + 1).
3. hacker_id가 다른 경우:
   • 순위를 1로 초기화합니다.

 

4. 값 할당

@prev_hacker_id := hacker_id AS dummy_hacker,
@prev_date := submission_date AS dummy_date

 

이전 hacker_id, 이전 날짜를 비교를 하기 위해서 값을 할당

드디어 문제 풀이

 

문제 풀이

1.  연속된 제출자 구하기 Dense_Rank()을 노가다..

SET @dense_rank = 0;
SET @prev_hacker_id = NULL;
SET @prev_date = NULL;

select dr.submission_date, dr.hacker_id, count(dr.hacker_id) as u_cnt
From
(
    SELECT
        hacker_id,
        submission_date,
        @dense_rank := IF(@prev_hacker_id = hacker_id AND @prev_date = submission_date, @dense_rank, 
                          IF(@prev_hacker_id = hacker_id, @dense_rank + 1, 1)) AS dense_rank,
        @prev_hacker_id := hacker_id AS dummy_hacker,
        @prev_date := submission_date AS dummy_date
    FROM (
        SELECT hacker_id, submission_date
        FROM Submissions
        ORDER BY hacker_id, submission_date
    ) AS sorted_submissions
) AS dr
where day(dr.submission_date) = dense_rank
group by dr.submission_date, dr.hacker_id

 

Window Function Dense_Rank(partition by hacker_id order by submission_date) 와 같은 코드

각 해커 마다 순서를 매겨 (같은 날에도 똑같은 값으로) 매기고 해당 날짜와 순서가 같은 데이터만 가져오기

 

ex)

1. 테이블 dense_rank

date	id	dense_rank
2021.01.01	a	1
2021.01.01	a	1
2021.01.02	a	2
2021.01.02	b	1

 

2. group by

date	id	cnt
2021.01.01	a	2
2021.01.02	a	1
2021.01.02	b	1

 

3. 그 후 count(distinct id)

2.  매일 최대 제출자 구하기

-- hacker_id_name
SELECT s.hacker_id 
FROM submissions s
WHERE s.submission_date = dc.submission_date
GROUP BY hacker_id
ORDER BY COUNT(submission_id) DESC, hacker_id
LIMIT 1

 

1. 그룹된 날짜

2021.01.01 -> 상관 서브쿼리(2021.01.01인 데이터 가져옴)

 

2. hacker_id 그룹화 

 

3. 많이 제출한 순서(count(hacker_id)) 으로 정렬 (같은 값일 때 hacker_id가 낮은거)

 

4. 제일 위 1개만 가져오기

 

3. 이름 가져오기

(SELECT name FROM hackers WHERE hacker_id = hacker_id_name)

 

hacker_id_name에서 hacker_id값 가져와서 name 출력

 

4.전체 코드

SET @dense_rank = 0;
SET @prev_hacker_id = NULL;
SET @prev_date = NULL;

select submission_date
    , count(distinct hacker_id) as cnt
    , (SELECT s.hacker_id 
        FROM submissions s
        WHERE s.submission_date = dc.submission_date
        GROUP BY hacker_id
        ORDER BY COUNT(submission_id) DESC, hacker_id
        LIMIT 1) AS hacker_id_name
        
    , (SELECT name FROM hackers WHERE hacker_id = hacker_id_name)
from
(
    select dr.submission_date, dr.hacker_id, count(dr.hacker_id) as u_cnt
    From
    (
        SELECT
            hacker_id,
            submission_date,
            @dense_rank := IF(@prev_hacker_id = hacker_id AND @prev_date = submission_date, @dense_rank, 
                              IF(@prev_hacker_id = hacker_id, @dense_rank + 1, 1)) AS dense_rank,
            @prev_hacker_id := hacker_id AS dummy_hacker,
            @prev_date := submission_date AS dummy_date
        FROM (
            SELECT hacker_id, submission_date
            FROM Submissions
            ORDER BY hacker_id, submission_date
        ) AS sorted_submissions
    ) AS dr
    where day(dr.submission_date) = dense_rank
    group by dr.submission_date, dr.hacker_id
) as dc
group by 1

 

문제 2

Print Prime Numbers | HackerRank

 

1000 이하의 모든 소수를 출력하는 쿼리를 작성하십시오.

결과는 한 줄로 출력하고, 공백 대신 앰퍼샌드(&) 문자를 구분자로 사용하십시오

예를 들어, 모든 소수 출력 결과가 10이라면 출력 형식은:

 

2&3&5&7

 

문제 풀이

1. 일단 재귀 CTE로 2~1000까지 테이블 만들기

WITH RECURSIVE CTE AS (
  SELECT 2 AS n
  UNION ALL
  SELECT 1+n FROM numbers WHERE n < 1000
)

 

2부터 1000까지 값 생성

 

2. 소수 판별

decimal_n AS(
   SELECT n
   FROM CTE
   WHERE NOT EXISTS(
         SELECT c.n
           FROM CTE AS c
          WHERE c.n > 1 
            AND c.n <= SQRT(CTE.n)
            AND CTE.n % c.n = 0
   )
)

 

1. c.n > 1 1은 소수가 아님

2. c.n이 CTE.n의 제곱근 까지 

3. 나눠지면 소수임

 

ex) 10

CTE Table	CTE as c Table
n	n
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10

 

n = 3 일때

c.n은 2부터 시작

c.n은 1보다 크고, 1.732이하인 숫자 -> False

3 % 2 = 0 -> False

서브 쿼리 해당 값 없음 -> NOT EXIST으로 출력

 

n = 4 일때

c.n은 2부터 시작

c.n은 1보다 크고 2이하인 숫자 -> True

4 % 2 = 0 -> True

서브 쿼리 해당 값 존재 -> NOT EXIST으로 출력하지 않음

 

n = 5일때

c.n은 2부터 시작

c.n은 1보다 크고 2.xxx 이하인 숫자 -> True

5 % 2 = 0 -> False

서브 쿼리 해당 값 없음 -> NOT EXIST으로 출력

 

3. 숫자 &으로 결합

SELECT GROUP_CONCAT(decimal_n.n SEPARATOR '&')
FROM decimal_n;

 

SEPARATOR '&'으로 결합

 

4. 전체코드

WITH RECURSIVE CTE AS (
  SELECT 2 AS n
  UNION ALL
  SELECT 1+n FROM CTE WHERE n < 1000
), decimal_n AS(
   SELECT n
   FROM CTE
   WHERE NOT EXISTS(
         SELECT c.n
           FROM CTE AS c
          WHERE c.n > 1 
            AND c.n <= SQRT(CTE.n)
            AND CTE.n % c.n = 0
   )
)
SELECT GROUP_CONCAT(decimal_n.n SEPARATOR '&')
FROM decimal_n;

 

서브 쿼리

• 서브 쿼리는 하나의 쿼리 내에 또 다른 쿼리가 포함되어 있는 쿼리를 의미
• 서브쿼리는 메인 쿼리에 포함되어 있는 관계
• Where절에 사용할 경우 복잡한 업무적인 조건을 직관적인 SQL로 표현하여 필터링하는데 주로 사용

-- 평균 급여 이상의 급여를 받는 직원
select * from hr.emp where sal>=(select avg(sal) from hr.emp)

-- 가장 최근 급여 정보
select * 
from hr.emp_salary_hist a 
where todate = (select max(todate) from hr.emp_salary_hist x where a.empno = x.empno);

 

서브 쿼리 유형

• Where절에 사용되는 서브쿼리
• Select 절에 사용되는 서브쿼리(스칼라 서브쿼리)
• From 절에 사용되는 서브쿼리(인라인 뷰)

 

상관 서브쿼리 exists 연산자

• 메인쿼리의 레코드가 서브쿼리에서 존재하는지를 체크하기 위해 활용
• 달일 컬럼 연결시에는 IN연산자와 Exists의 결과는 서로 동일

 

메인 쿼리와 상관 서브쿼리의 연결 방식

• 메인쿼리는 상관 서브쿼리에서 연결시 한 건의 서브쿼리만 결과를 반환하면 더 이상 동일 레코드로 연결을 수행하지 않기 때문에 메인 쿼리의 집합 레벨을 그대로 유지 할 수 있음

 

NOT IN과 NOT EXISTS

Null 존재 컬럼 서브쿼리 연결시 NOT IN과 NOT EXISTS의 차이

• IN 연산자 같은 경우 개별 값이 =조건들의 OR 연산 적용
  in (20, 30) -> a = 20 or a = 30
  in (20, 30, null) -> a = 20 or a = 30 or a = null
  not in (20, 30, null) -> not(a = 20) and not(a = 30) and not(deptno = null)
  null 연산을 적용시 null이됨, 여러 조건의 결합 시 True and null은 null 이지만 True or Null은 True가 됨
• null 존재 컬럼을 서브쿼리로 연결시 not in에서 결과를 추출하려면 서브쿼리 내에서 null이 아닌 레코드만 filltering해야함

 

스칼라 서브쿼리

• select절에 사용할 수 있는 서브쿼리로 상관 서브쿼리와 유사하게 동작
• 단 한개의 로우, 단 한 개의 컬럼 값만 반환 할 수 있음
• 조인과 유사하게 from절의 집합과 연결되어 있는 결과를 추출할 수 있으며, from절의 집합 레벨을 변화 시키지 않음
• N이 늘어날수록 수행속도가 저하됨
• 스칼라 서브쿼리는 LEFT OUTER JOIN으로 대체 될 수 있음
• 남발하지 마라, 가독성 떨어지고, 성능이 저하, 온라인 쿼리에만 적용