SQL로 시작하는 데이터 분석 첫걸음 5주차
패스트캠퍼스: SQL로 시작하는 데이터 분석 첫걸음 5주차 (마지막주!)
5주차 강의는 Hacker Rank 사이트를 통해 직접 쿼리문을 작성하는 실습을 했습니다.
1. The Blunder
SELECT ceil(avg(salary) - avg(replace(salary,'0','')))
FROM EMPLOYEES;
설명:ceil을 사용하여 소수점 이하를 없애주고 잘못 계산된 평균 월급을 구하기 위해 replace를 사용하여 0을 제거
2. Top Earners
SELECT salary*months, count(*)
FROM Employee
WHERE salary*months =(SELECT max(salary*months)
FROM Employee)
GROUP BY 1;
설명:먼저 전체 테이블에서 max (최대) 임금을 구함-> 최대 임금으로 그룹을 지정하고 최대 임금과 최대 임금을 받는 직원의 수를 반환.
3. Weather Observation Station 15
Weather Observation Station 15 | HackerRank
SELECT round(LONG_W, 4)
FROM STATION
WHERE LAT_N =(SELECT max(LAT_N)
FROM STATION
WHERE LAT_N < 137.2345);
설명:먼저 전체 테이블에서 위도가 137.2345 보다 작은 범위에서 가장 큰 값을 찾음-> 가장 큰 값의 위도의 경도를 소수점 4째 자리까지 반환.
4. Weather Observation Station 19
Weather Observation Station 19 | HackerRank
SELECT
round(sqrt(power(min(LAT_N)-max(LAT_N), 2) + power(min(LONG_W)-max(LONG_W), 2)), 4)
FROM STATION
설명:power를 이용하여 2제곱 계산하는 것과 sqrt를 사용하여 루트를 계산.
5. Weather Observation Station 20
Weather Observation Station 20 | HackerRank
WITH ordering as
(SELECT lat_n, row_number() over(order by lat_n asc) as lat_order
FROM STATION)
SELECT round(avg(lat_n), 4)
FROM ordering
WHERE lat_order = (SELECT floor(avg(lat_order)) FROM ordering)
OR lat_order = (SELECT floor(avg(lat_order)) FROM ordering);
설명: MySQL에서는 median 명령어가 없기 때문에 median 값을 구할 수 있도록 쿼리를 만들어야함.
데이터 (숫자)를 나열했을 때 중간에 있는 데이터가 median 값이기 때문에 row_number 함수를 이용하여 중간 값 (평균)을 구해야함
With 명령어를 사용하여 row_number가 있는 테이블을 일시적으로 만들어줍니다. 데이터의 수가 짝수일 경우를 생각해야 함.
Ex. 중간 값이 2개인 경우, 전체 랭크의 평균을 구하고 올림 내림 값을 구하기.
Ex2.전체 rank 평균이 3.5이면 3과 4모두 고려.
6. Type of Triangle
SELECT case
when (a=b and b=c and c=a) then 'Equilateral'
when a+b <= c or a+c <= b or b+c <= a then 'Not A Triangle'
when (a=b or b=c or c=a) then 'Isosceles'
else 'Scalene'
end
FROM Triangles
설명:다양한 삼각형의 조건에 따라 삼각형의 이름을 반환하기 위하여 Case When 명령어를 사용. 중요한 점은 2번째 when 구문의 순서를 변경할 수 없다는 점. *Case When 구문은 순서대로 조건을 확인하기 때문에 3번째 when 구문과 2번째 when 구문의 순서를 바꾸는 경우 에러가 발생함!
7. The PADS
SELECT concat(name, '(', upper(substr(occupation,1,1)), ')')
FROM OCCUPATIONS
ORDER BY name;
SELECT concat('There are a total of', count(occupation),
' ', lower(occupation), 's.').
FROM OCCUPATIONS
GROUP BY occupation
ORDER BY count(occupation), occupation;
설명:첫번째 쿼리는 substr 명령어를 사용하여 직업명의 첫 글자만 추출하여 대문자로 변경. 이후 이름과 첫 문자를 concat 명령어를 사용하여 붙여줌.두번째 쿼리는 직업으로 그룹을 지정하고 직업별 총 수와 직업명을 소문자로 반환한 값을 위에 다른 문자들과 concat 명령어를 사용하여 붙임.
8. Occupations
SELECT
min(case when A.occupation = 'Doctor' then A.name else null end) as doctor,
min(case when A.occupation = 'Professor' then A.name else null end) as professor,
min(case when A.occupation= 'Singer' then A.name else null end) as singer,
min(case when A.occupation = 'Actor' then A.name else null end) as actor
FROM
(SELECT name, occupation, rank() over(partition by occupation order by name asc) as name_order
FROM OCCUPATIONS) A
Group By A.name_order;
설명:Pivot, 즉 컬럼과 행을 바꾸기 위해서 테이블에서 이름, 직업명, 그리고 rank 명령어로 생성한 랭크를 반환. 반환한 데이터를 다른 테이블로 사용.[min을 사용한 이유는 group by를 사용하기 위해서는 집계함수가 필요한데 min 또는 max는 문자타입에는 영향을 주지 않기 때문임]
9. Binary Tree Nodes
Binary Tree Nodes | HackerRank
쿼리:
SELECT case
when P is null then concat(N, ' Root')
when N in (SELECT distinct P FROM BST) then concat(N, ' Inner')
else concat(N, ' Leaf')
end
FROM BST
ORDER BY N;
설명:Select 절을 in 명령어와 함께 사용할 수 있음.
10. New Companies
SELECT
A.company_code,
A.founder,
(SELECT count(distinct lead_manager_code)
FROM Lead_manager
WHERE company_code = A.company_code) as num_lead,
(SELECT count(distinct senior_manager_code)
FROM Senior_manager
WHERE company_code = A.company_code) as num_senior,
(SELECT count(distinct manager_code)
FROM Manager
WHERE company_code = A.company_code) as num_manager,
(SELECT count(distinct employee_code)
FROM Employee
WHERE company_code = A.company_code) as num_employee
FROM Company A
ORDER BY company_code asc
설명: 여러 테이블을 조인하지 않고 스칼라 쿼리를 여러번 사용하여 여러 테이블에서 데이터를 가져올 수 있음.
11. Average Population of Each Continent
Average Population of Each Continent | HackerRank
SELECT
COUNTRY.continent,
floor(avg(CITY.population))
FROM
COUNTRY inner join CITY
on COUNTRY.Code = CITY.CountryCode
GROUP BY 1;
설명: COUNTRY.Code와 CITY.CountryCode가 같은 행을 기준으로 두 테이블을 조인하는데 두 테이블에 공통으로 있는 데이터만 가져옴-> 이후 Country 테이블에서 Continent 데이터 그리고 City 테이블에서 population 데이터를 가져와 평균(내림)을 구하고 반환.
12. The Report
SELECT case
when B.Grade < 8 then null
else A.name
end as name,
B.Grade,
A.Marks
FROM
students A inner join grades B
on A.Marks >= B.Min_mark
and A.Marks <= B.Max_mark
ORDER BY grade desc, name asc, marks asc;
설명: 두 테이블에 공통된 컬럼이 없음-> Inner Join을 하는 경우 모든 조합의 경우가 발생하므로 on절에 조건을 추가하여 원하는 형태의 조인을 만듦. Inner join 대신 left outer join을 사용해도 같은 결과를 얻을수 있음.
13. Contest Leaderboard
Contest Leaderboard | HackerRank
SELECT
A.hacker_id,
A.name,
sum(B.score) as total_score
FROM
Hackers A
left outer join
(SELECT
hacker_id,
challenge_id,
max(score) as score
FROM Submissions
GROUP BY 1, 2) B
on A.hacker_id = B.hacker_id
GROUP BY 1, 2
Having sum(B.score) != 0
ORDER BY total_score desc, hacker_id asc;
설명: 서브 쿼리에서 최고 성적 (max)을 계산하고 조인에 사용합니다. 양쪽 테이블의 hacker_id 컬럼을 기준으로 조인. hacker_id와 name를 기준으로 (그룹화) 최고 성적 값들의 총계를 구함.
