1차 QCC review

1차 QCC revies

 

[5기] QCC - 1회차 | Notion

 

 

[1번]

문제: country 테이블을 기반으로, 전년도 국민총생산(GNP)이 없거나 전년 대비 GNP가 감소한 국가 중 인구가 1천만 명 이상인 국가의 수를 조회하는 쿼리를 작성하세요.

 

내가 작성한 쿼리

with sub1 as(SELECT name, 
		population
from country
where GNPOld is NULL 
	or GNP < GNPOld
group by name
having population >= 10000000)
select count(name)
from sub1;

 

회고

• GROUP BY, HAVING절은 불필요함.
• GROUP BY절은 집계 함수(sum, count, avg 등)를 사용해 그룹화된 데이터를 기반으로 계산할 때 사용된다.
• GROUP BY 사용 시 주의사항!
  • SELECT절에 포함된 모든 열은 1.GROUP BY에서 그룹화되어야 하거나, 2.집계함수로 처리되어야 한다.

수정한 쿼리

WITH sub1 AS (
    SELECT name, 
           population
    FROM country
    WHERE GNPOld IS NULL 
       OR GNP < GNPOld
       AND population >= 10000000
)
SELECT COUNT(name)
FROM sub1;

정답

select count(distinct code)
from country
where (gnpold is null or gnp < gnpold) and population >= 10000000;

• distinct code로 중복 방지!
• (gnpold is null or gnp < gnpold) 괄호 쳐서 먼저 실행되도록. 
  • 그 다음에 (and) population 조건이 실행되도록.

이렇게 간단한데 난 저따구로 짬. 다행히 출력값은 정답과 동일했다.

 

[2번]

문제: city 테이블과 country 테이블을 사용하여 각 대륙에서 인구가 가장 많은 도시를 찾아, 해당 도시와 국가, 그리고 대륙의 정보를 조회하세요. 결과는 인구를 기준으로 내림차순 정렬해야 합니다.

 

내가 작성한 쿼리

with sub1 as
(select name,
        Population
from city
group by 1
order by 2 desc)
select i.name as CityName,
o.name as CountryName,
o.continent,
i.population
from city i join country o on i.CountryCode = o.Code
where i.name in (select name from sub1)
group by o.Continent
order by i.population desc;

 

정답

with sub1 as(SELECT i.name as CityName,
	o.name as CountryName,
	o.continent,
	i.population,
	rank() over (partition by o.continent order by i.population desc) as rk
from city i join country o on i.CountryCode = o.Code)
select cityname,
	countryname,
	continent,
	population
from sub1
where rk = 1
order by 4 desc;

• city, country 테이블 inner join
• cityname, countryname, continent, population 불러오기
• RANK()함수로
  • partition by o.continent -> 대륙별(o.continent)로 나누기
  • order by i.population DESC -> 인구 많은 도시 순으로 정렬
  • RANK() 실행 -> 인구 순위 매기기.
    • 인구 수 동일한 도시가 있을 경우, 둘다 출력(동일한 순위 매기기)하기 위해 rank()함수 씀
• where rk = 1   ->  대륙별로 인구가 가장 많은 도시 1개만 출력돼야 하기 때문에
• order by 4(인구) desc

* 윈도우 함수 실행순서

partition by 칼럼 (이 칼럼의 고유값끼리 파티션들이 나눠짐) --> order by 칼럼 (각 파티션 내부 정렬) --> 파티션 내 프레임 범위 지정 --> 윈도우 함수

 

*윈도우 함수 형식

윈도우함수() over (partition by 칼럼 order by 칼럼 row/range [a] and [b]