[PCSQL] 샘플 모의고사 풀이

1. 좋아요 기능 추가

SELECT id, creator, 0 likes
from old_posts

union

select *
from new_posts
order by id desc

두 조회 결과를 union으로 합칠 때, 새로운 column이 추가되어 union을 바로 사용할 수 없는 경우이다.
union 사용 시 column의 개수와 타입이 일치해야 한다.

2. 많이 출제된 유형

SELECT ALGORITHM_TYPE
FROM
    (SELECT ALGORITHM_TYPE
    FROM PROBLEMS
    GROUP BY ALGORITHM_TYPE
    ORDER BY COUNT(*) desc, ALGORITHM_TYPE)
WHERE ROWNUM = 1

기본적인 정렬, group by 및 결과 개수 제한 문제다.
mysql에서는 limit으로 결과 개수를 제한하였지만, oracle에서는 where에서 rownum의 수를 제한한다.

3. 회의실 예약 요청 확정하기

SELECT R1.ID, R1.START_TIME, R1.END_TIME
FROM RESERVATION R1
LEFT JOIN RESERVATION R2
ON R1.ID > R2.ID AND R1.START_TIME < R2.END_TIME AND R1.END_TIME > R2.START_TIME
WHERE R2.ID IS NULL
ORDER BY R1.START_TIME

이전에 신청된 회의들과 시간이 겹치지 않도록 하여 확정되는 회의를 골라내는 것이다.
신청 목록에 대해 self join을 해야 한다.
생각을 하기 조금 까다로웠는데, id(회의 신청 순서)를 기준으로 이전에 신청된 회의(r2)와 비교하여, 회의 시간이 겹치는지 여부를 left join하여 r2.id가 null인 것(=회의 시간이 겹치지 않는 것)을 찾아서 결과를 내어야 했다.

4. 시도별 정답률 구하기

WITH TMP AS
(SELECT CASE
            WHEN S.SUBMITTED = P.CORRECT_ANSWER THEN 1
            ELSE 0
        END AS CNT,
       ROW_NUMBER() OVER (PARTITION BY S.USER_ID, S.PROBLEM_ID ORDER BY S.TIMESTAMP) AS ATTEMPT_NUMBER
FROM SUBMISSIONS S
JOIN PROBLEMS P
ON S.PROBLEM_ID = P.PROBLEM_ID)

SELECT  TMP.ATTEMPT_NUMBER AS NTH_SUBMISSION,
        ROUND(SUM(TMP.CNT)/COUNT(TMP.ATTEMPT_NUMBER)*100,0) AS CORRECT_RATE
FROM TMP
GROUP BY TMP.ATTEMPT_NUMBER
ORDER BY TMP.ATTEMPT_NUMBER;

시도 횟수 별 정답률을 구해야 하는 문제였다.
window 함수를 잘 기억하지 못해 다시 정리해야 했다.

window 함수

Oracle에서 Window 함수(또는 분석 함수, Analytic Functions)는 GROUP BY 없이도 데이터 집합 내에서 특정 기준(윈도우)에 따라 연산을 수행하는 함수입니다.

즉, 행별로 특정 범위(윈도우)에 대해 집계 연산을 수행하면서도 각 행을 유지하면서 추가적인 계산을 수행할 수 있습니다.

기본 문법

함수명 (컬럼) OVER (
    [PARTITION BY 그룹 컬럼] -- (선택 사항, 그룹화)
    [ORDER BY 정렬 컬럼]     -- (선택 사항, 순서 지정)
    [WINDOWING 절]           -- (선택 사항, 범위 지정)
)

🔹 주요 Window 함수 종류

1️⃣ 순위 관련 함수

함수 설명

RANK() 동일한 순위를 부여하고 그다음 순위를 건너뜀 (ex. 공동 1위 → 3위)
DENSE_RANK() 동일한 순위를 부여하지만 순위를 건너뛰지 않음 (ex. 공동 1위 → 2위)
ROW_NUMBER() 동일한 값이라도 고유한 순위를 부여함 (중복 없음)

✅ 예제

SELECT EMPLOYEE_ID, DEPARTMENT_ID, SALARY,
       RANK() OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS RANKING,
       DENSE_RANK() OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS DENSE_RANKING,
       ROW_NUMBER() OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS ROW_NUM
FROM EMPLOYEES;

• 부서별(PARTITION BY DEPARTMENT_ID)로 급여 내림차순 정렬 후 순위를 매김
• RANK()는 동일 순위 다음을 건너뜀
• DENSE_RANK()는 동일 순위 다음을 건너뛰지 않음
• ROW_NUMBER()는 동일 순위 없이 1, 2, 3, ... 부여

2️⃣ 이동 평균 및 집계 함수

함수 설명

LAG(컬럼, N, 기본값) 현재 행 기준 이전 N번째 행의 값을 반환
LEAD(컬럼, N, 기본값) 현재 행 기준 다음 N번째 행의 값을 반환
FIRST_VALUE(컬럼) 현재 윈도우에서 첫 번째 값을 반환
LAST_VALUE(컬럼) 현재 윈도우에서 마지막 값을 반환
NTH_VALUE(컬럼, N) 현재 윈도우에서 N번째 값을 반환

✅ 예제

SELECT EMPLOYEE_ID, SALARY,
       LAG(SALARY, 1, 0) OVER (ORDER BY SALARY DESC) AS PREV_SALARY,
       LEAD(SALARY, 1, 0) OVER (ORDER BY SALARY DESC) AS NEXT_SALARY
FROM EMPLOYEES;

• LAG(SALARY, 1, 0): 이전 행의 급여를 가져옴 (없으면 0)
• LEAD(SALARY, 1, 0): 다음 행의 급여를 가져옴 (없으면 0)

3️⃣ 누적 합 및 비율 계산 함수

함수 설명

SUM(컬럼) 지정한 윈도우에 대해 누적 합
AVG(컬럼) 지정한 윈도우에 대해 누적 평균
COUNT(컬럼) 지정한 윈도우에 대해 개수 계산
MAX(컬럼) 지정한 윈도우에 대해 최대값
MIN(컬럼) 지정한 윈도우에 대해 최소값
PERCENT_RANK() 전체 순위 중 백분율 (01)
CUME_DIST() 현재 행보다 낮은 값의 누적 비율 (01)

✅ 예제

SELECT EMPLOYEE_ID, DEPARTMENT_ID, SALARY,
       SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY) AS CUMULATIVE_SALARY,
       AVG(SALARY) OVER (PARTITION BY DEPARTMENT_ID) AS AVG_SALARY,
       PERCENT_RANK() OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS PERCENT_RANKING
FROM EMPLOYEES;

• SUM(SALARY) OVER (...) → 같은 부서에서 누적 급여 합을 계산
• AVG(SALARY) OVER (...) → 같은 부서에서 급여 평균을 계산
• PERCENT_RANK() → 부서 내에서 급여 백분율 순위 계산

4️⃣ 윈도우 범위 지정 (RANGE, ROWS)

옵션 설명

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 처음부터 현재 행까지
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 처음부터 현재 행까지 (행 기준)
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING 현재 행의 앞뒤 1개 범위
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 현재 행의 앞뒤 1개 행

✅ 예제

SELECT EMPLOYEE_ID, SALARY,
       SUM(SALARY) OVER (ORDER BY SALARY ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MOVING_SUM
FROM EMPLOYEES;

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW → 현재 행 기준으로 앞 2개 행까지 포함한 합산 (이동 합계)

5. 점수 합계

WITH TMP AS (
    SELECT S.USER_ID,
           S.PROBLEM_ID,
           CASE WHEN S.SUBMITTED = P.CORRECT_ANSWER THEN 1 ELSE 0 END AS ISCORRECT,
           S.TIMESTAMP,
           P.SCORE,
           ROW_NUMBER() OVER (PARTITION BY S.USER_ID, S.PROBLEM_ID ORDER BY S.TIMESTAMP) AS ATTEMPT
    FROM SUBMISSIONS S
    JOIN PROBLEMS P ON S.PROBLEM_ID = P.PROBLEM_ID
),
TMP2 AS (
    SELECT USER_ID, PROBLEM_ID, ISCORRECT, TIMESTAMP, ATTEMPT, SCORE,
    ROW_NUMBER() OVER (PARTITION BY USER_ID, PROBLEM_ID ORDER BY ATTEMPT) AS RN
    FROM TMP
    WHERE ISCORRECT = 1
),
TMP3 AS (
    SELECT *
    FROM TMP2
    WHERE RN = 1
),
USER_TABLE AS (
    SELECT DISTINCT USER_ID
    FROM SUBMISSIONS
),
RESULT AS (
    SELECT  UT.USER_ID,
    NVL(SUM(TMP3.SCORE),0) AS TOTAL_SCORE,
    NVL(SUM(TMP3.ATTEMPT-1)*300 + MAX(TMP3.TIMESTAMP),0) AS TIME_TAKEN
    FROM TMP3
    FULL JOIN USER_TABLE UT
    ON UT.USER_ID = TMP3.USER_ID
    GROUP BY UT.USER_ID)

SELECT *
FROM RESULT
ORDER BY TOTAL_SCORE DESC, TIME_TAKEN ASC, USER_ID ASC

with문을 엄청 많이 써야했다. 이를 최적화하고자 했으나 정답이 틀려서 포기했다.
과정은 다음과 같다.

1. 각각의 제출에 대해 맞췄는지 여부를 확인한다.
2. 맞춘 문제를 필터링하고, 그 중 가장 빨리 맞춘 시도를 찾기 위해 rownumber를 준다.
3. 가장 빨리 맞춘 문제를 필터링한다.
4. 사용자 목록을 생성한다.
5. 결과를 생성한다.
6. order by로 적절하게 정렬한다.
   그 외에는 NVL을 통해 값이 없는 걸 0을 넣어줘야 했다.