[SolveSQL] 데이터 그룹으로 묶기, 복수국적 메달 수상한 선수 찾기

1.데이터 그룹으로 묶기 -  https://solvesql.com/problems/group-by/

solvesql

Q. points 테이블은 프란시스 앤스컴이 만든 Anscombe's quartet 데이터를 담고 있습니다. 이 데이터는 quartet 컬럼에 의해 4개의 서브셋으로 나뉘어지고, 각 서브셋은 평균, 표본 분산, 상관계수 등이 거의 동일하나 데이터의 분포를 시각화하면 전혀 다른 분포를 가지는 특징이 있습니다.

points 테이블에 쿼리를 수행해 quartet으로 구분되는 각 서브셋 데이터에 대해서 아래 통계량을 계산하는 쿼리를 작성해주세요. 계산된 값은 소수점 아래 셋째 자리에서 반올림 해야 합니다. 결과 데이터에는 아래 5개의 컬럼이 존재 해야 합니다.

* Hint : 이 문제에서 구해야 하는 분산은 모분산이 아니라 표본 분산입니다. DBMS에 따라 표본 분산을 구하는 함수가 다를 수 있으니 유의해주세요.

 

A. 일단 '표본' 분산을 구하는 함수는 variance이다. 이를 활용해 코드를 구성하면

select
  quartet,
  round(avg(x), 2) as x_mean,
  round(variance (x), 2) as x_var,
  round(avg(y), 2) as y_mean,
  round(variance (y), 2) as y_var
from
  points
group by
  quartet

* round(a,b) : a를 최대 소수 b번째 자리까지 나타내라는 뜻이다. 

실제로 쿼리 실행 결과 소수 둘째자리까지 갈 거 없는 숫자들(7.5같은) 은 첫째 자리에서 끝났다. 

 

2. 복수 국적 메달 수상한 선수 찾기 - https://solvesql.com/problems/multiple-medalist/

solvesql

Q. 역대 올림픽 정보 데이터셋은 역대 올림픽 경기와 관련된 데이터가 들어있는 테이블로 이루어져 있습니다.

athletes 테이블에는 역대 올림픽 참가 선수의 이름이 들어 있습니다. events 테이블에는 종목과 경기 이름이 들어 있습니다. games 테이블에는 올림픽 개최 연도, 개최 도시와 시즌 정보가 기록되어 있습니다. records 테이블에는 역대 올림픽 참가 선수들의 신체 정보와 획득한 메달 정보가 기록되어 있습니다. 이 테이블은 다른 테이블과 매핑할 수 있는 ID 정보도 가지고 있습니다. teams 테이블에는 국가 정보가 기록되어 있습니다.

2000년 이후의 메달 수상 기록만 고려했을 때, 메달을 수상한 올림픽 참가 선수 중 2개 이상의 국적으로 메달을 수상한 기록이 있는 선수의 이름을 조회하는 쿼리를 작성해주세요. 조회된 선수의 이름은 오름차순으로 정렬되어 있어야 합니다.

 

A. 일단, 정렬의 경우 기본 오름차순이기 때문에 ASC 옵션을 작성해준다.  난이도가 보통으로 올라갔다!

그리고 테이블이 5종류가 있다. 이 다섯 테이블의 join을 통해 선수명과 국가(국적) 정보가 한 테이블에 모이게 하자. 이렇게 모으는 게 From 구문이다. 

SELECT a.name 
FROM records AS r
       INNER JOIN games AS g ON r.game_id = g.id
       INNER JOIN athletes AS a ON r.athlete_id = a.id
WHERE g.year >= 2000
AND medal IS NOT NULL
GROUP BY athlete_id
HAVING COUNT(DISTINCT team_id) > 1
ORDER BY a.name ASC

특색이라면 Having이 들어갔다는 것. 국적이 2개 이상이어야 하므로, Count 함수와 Having 조건절을 붙여주었다.