QCC | 1회차 코드 리뷰 & 피드백

 

☑️문제1. 전년도 국민총생산(GNP)이 없거나 전년 대비 GNP가 감소한 국가 중 인구가 1천만 명 이상인 국가의 수 조회하기 

[문제 조건]
-  전년도 국민 총 생산값이 없거나  전년대비 cnp가 감소한 국가 중
-  인구가 1천만명 이상인 국가 갯수 집계
-  출력 컬럼 : 국가의 수 

 

[문제 해결]

• 테이블 확인  ( 조건별 필터링만 하면 됨)
  • 전년도 gnp : gnpold 컬럼
  • 올해 gnp : gnp 컬럼에 값 저장
  • 인구 수 : population 
• 조건 파악 및 작성
  • 전년도 국민 총 생산량이 없거나 전년대비 gnp 감소 : `WHERE (GNP < GNPOld OR GNPOld IS NULL)`
  • 인구가 1천만명 이상인 국가 : `Population>10000000`
  • where절에서 필터링 시, gnp 내용은 `or` 조건/ 인구수는 and 조건이므로  gnp 내용에 괄호 필요
  • 국가 수 집계 : `count(code)` code는 pk로 중복 안됨 `distinct` 불 필요

SELECT COUNT(Code) AS country_count
FROM country c 
WHERE (GNP < GNPOld OR GNPOld IS NULL)
  AND Population>10000000;

 

 

▼ 오류 상황 및 피드백 

[문제 상황 복기] 

1. 테이블 확인 : 쿼리 날려서 테이블 구조 확인 & 주어진 테이블/컬럼 설명은 스킵 → `여기서 가장 큰 문제 발생` 
2. 전년도 대비 값 비교 이므로 lag나 correlated 서브쿼리로 전년도 gnp 컬럼 생성해주고자 함. → 해당 년도는 indepyear 컬럼에 존재.
3. indepyear 컬럼 그룹바이 하여 `전년도` 가 빠짐없이 있는지 확인 → `여기서 두번째 문제`
4. 전년도 이빨이 매우 많이 빠짐 누락된 년도를 recursive cte로 채워주고자 함 → `여기서 시간 몹시 낭비`
5. recursive cte 생성  : `indepyear` 컬럼 int 형태로 저장되어있음 date_add(~) 사용 불가 년도 +1 로 누락년도 생성
6. 초기값 min(indepyear)로 확인하니 마이너스값 & 100단위도 저장 되어있음→`당황, 년도컬럼에 마이너스/800년은 뭐지? 말이 안됨` 
7. 일단 indepyear>0 양수만 필터링후 실행했더니
   • `SQL Error [3636] [HY000]: Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.` 에러 남
   • `왜?` 재귀 cte 는 반복 횟수 1000회 초과 하면 중단 후 에러 발생 
8. 진행 불가로 다시 로우 테이블로 돌아와서 나라별 indepyear 집계 → 나라별 년도 값이 1개씩만 있음 
9. 문제 다시 정독 후 전년도 gnp 값이 gnpold 컬럼에 저장되어있다는 것을 알게됨
10. 문제 해결 

# 헛짓한 재귀문 쿼리... 

WITH RECURSIVE cte AS (
    SELECT MIN(indepyear) AS start_year
    FROM country
    WHERE indepyear>0
    UNION ALL
    SELECT start_year + 1
    FROM cte
    WHERE start_year + 1 <= (SELECT MAX(indepyear) FROM country)
)
SELECT *
FROM cte;

 

[결론 & 피드백]

• 문제 풀이 전 무조건 문제를 정독 할것 
• 문제 풀이전 출력컬럼의 테이블/컬럼 관계 확인 할 것 

 

☑️문제2. 대륙에서 인구가 가장 많은 도시 구하기

[문제 조건]
- 인구가 가장 많은 도시와
- 그 도시의 대륙명, 국가명 출력 
- 인구수 기준 내림차순 
- 출력 컬럼 : 도시명,도시 인구수,대륙명,국가명

 

[문제 해결1 : 서브쿼리 & 윈도우 함수 사용 ]

• 테이블 확인  (출력 컬럼의 위치와 조건 확인)
  • 출력컬럼 : city 테이블  (name, population) country 테이블 (name,continent)
  • 조인키 : city.countrycode 와 country.code 연결 필요
• 조건 파악 및 작성 
  • 출력컬럼이 각자 다른 테이블에 있으므로 조인키 활용하여 `inner join`
  •  대륙별 가장 인구수가 많은 행 필터링 해야함 : `rank/row_number 윈도우 함수` 활용하여 원본 손상 없이 순번 매기기 → rank 쓴 이유는 혹시라도 인구수가 가장 많은 대륙이 중복 됐을때 둘다 출력해주기 위함 `rank() OVER (PARTITION BY co.Continent ORDER BY c.Population desc)` 
  • 윈도우 함수는 바로 where 절 필터링 불가이므로 서브쿼리 생성후 rk=1 일때만(=인구수가 가장 많은 대륙만) 필터링
  • 정렬 조건 만족 

WITH rk_t AS (
SELECT co.name AS countryname
      ,co.Continent AS continent
      ,c.Population 
      ,c.name AS cityname
      ,rank() OVER (PARTITION BY co.Continent ORDER BY c.Population desc) AS rk
FROM country co
	INNER JOIN CITY c ON co.code=c.CountryCode
)
SELECT cityname
      ,countryname
      ,continent
      ,population
FROM rk_t
WHERE rk=1
ORDER BY 4 DESC ;

 

[문제 해결2 : correlated 상관 서브쿼리 ]

• where 절에 상관서브쿼리로 대륙별 인구가 max 일때만 필터링 해줌

SELECT 
    c.Name AS CityName,
    co.Name AS CountryName,
    co.Continent,
    c.Population
FROM 
    city c
JOIN 
    country co ON c.CountryCode = co.Code
WHERE 
    c.Population = (
        SELECT MAX(c2.Population)
        FROM city c2
        JOIN country co2 ON c2.CountryCode = co2.Code
        WHERE co2.Continent = co.Continent
    )
ORDER BY 
    c.Population DESC;

 

[문제 해결3 : 조인 & 서브쿼리 ]

• JOIN 절 서브쿼리 : 인구수가 가장 많은 대륙 그룹바이 필터링한 테이블 생성
• 아우터 쿼리 INNER JOIN : 대륙명과 조인의 대륙명이 같고, 인구수와 조인의 인구수와 같을때 

SELECT 
    c.Name AS CityName,
    co.Name AS CountryName,
    co.Continent,
    c.Population
FROM 
    city c
JOIN 
    country co ON c.CountryCode = co.Code
JOIN (
    SELECT 
        co.Continent,
        MAX(c.Population) AS MaxPopulation
    FROM 
        city c
    JOIN 
        country co ON c.CountryCode = co.Code
    GROUP BY 
        co.Continent
) max_pop ON co.Continent = max_pop.Continent AND c.Population = max_pop.MaxPopulation
ORDER BY 
    c.Population DESC;

 

[3가지 방식 성능 비교 ]

1.서브쿼리 & 윈도우 = 3.조인&서브쿼리  >> 2.상관 서브쿼리

→ 상관 서브쿼리는 테이블을 계속 조회해야하기 때문에  시간이 오래걸려 성능이 낮음. 

---

 

금일 캠프에서 라이브코딩테스트와 유사한 환경으로 쿼리 짜보는 QCC를 처음 진행했다. (1시간안에 2문제 풀기)

SQL을 9월부터 4개월간 꾸준히 문제풀이 & 프로젝트를 병행하며 공부해오던 터라 무리없이 답안제출을 할 수 있을거라 생각했는데.... 첫 문제부터 문제 제대로 안 읽고 30분 가량을 뻘짓하다가 해결했고, 두번째 문제는 시간이 없어 허겁지겁 하다가 완성되지 않은 답으로 제출했다. 이번 QCC를 통해 문제점을 파악 할수 있었고, 코테 준비를 위해 실제와 유사한 환경으로 더 많이 연습 해야겠다는 생각이 든다.

눈치없이 웃고있는 나^^..

문제점 회고) 내가 짜는 쿼리를 실시간으로 누군가 보고있을거라 생각하니 긴장이 확 됐다. 또한 빠르게 문제를 풀어야 겠다는 생각에 부담이 됐다. 평소처럼 문제를 읽으면서 테이블 EDA 하지 않고 바로 쿼리를 짜기 시작했는데(뇌 없이 손코딩), 정리되지 않은 쿼리 흐름 & 컬럼명/컬럼값 숙지 미비로 수십번 본 테이블을 돌리며 불 필요한 반복을 지속했다. 결과적으로 시간 부족으로 검토도 못하고 제출 했다. 아쉬운 마음에 QCC가 끝나고 물 한잔 마시고 바로 다시 풀었을땐 어렵지 않게 문제를 해결할 수 있었다..( 시험끝나고 다시 보면 알것같은 것처럼..)

결론) 문제 조건을 제대로 안 읽음 & 컬럼명/컬럼값 숙지 미비 & 사전에 쿼리 작성 순서/논리 흐름 생각 안함  & 과한 긴장 

qcc 를 하면서, 내 현재 위치에 어딘지 명확하게 확인했다. (우매함의 봉우리)

갈길이 멀다. 겸손하게 공부하자