반응형
인프런 인강 참고
[백문이불여일타] 데이터 분석을 위한 고급 SQL 문제풀이 - 인프런 | 강의
인프런 누적 수강생 7000명 이상, 풍부한 온/오프라인 강의 경험을 가진 데이터리안의 SQL 고급 문제풀이 강의. SQL 고급 내용을 연습해 볼 수 있는 여러 문제를 함께 풀어봅니다., 백문이불여일타!
www.inflearn.com
[문제]
- Weather Observation Station 17 : https://www.hackerrank.com/challenges/weather-observation-station-17/problem
- Contest Leaderboard : https://www.hackerrank.com/challenges/contest-leaderboard/problem
- New Companies : https://www.hackerrank.com/challenges/the-company/problem
- Join을 사용하지 말고 다른 방법으로 풀어 보세요.
- Occupations :c
[풀이]
1. Weather Observation Station 17
SELECT ROUND(LONG_W,4) FROM STATION
WHERE LAT_N = (SELECT MIN(LAT_N) FROM STATION WHERE LAT_N > 38.7770 )나는 위에 방식처럼 풀어서 결과를 도출해냈지만
인강에서 문제풀이는 나와 달랐다.
인강 문제풀이 결과 - 큰결과를 정렬하고 그중에 첫번째 값만 가져오기
SELECT ROUND(LONG_W,4)
FROM STATION
WHERE LAT_N > 38.7780
ORDER BY LAT_N
LIMIT 1
2. Contest Leaderboard
/*
The total score of a hacker is the sum of their maximum scores for all of the challenges. Write a query to print the hacker_id, name, and total score of the hackers ordered by the descending score. If more than one hacker achieved the same total score, then sort the result by ascending hacker_id. Exclude all hackers with a total score of 0 from your result.
*/
SELECT H.hacker_id , H.name , SUM(A.score) AS totalScore
FROM
(
SELECT S.hacker_id, S.challenge_id , MAX(S.SCORE) AS score FROM Submissions S
GROUP BY S.hacker_id, S.challenge_id) A , Hackers H
WHERE A.hacker_id = H.hacker_id
GROUP BY H.hacker_id, H.name
having totalScore !='0'
ORDER BY totalScore DESC, H.hacker_id ASC
3. New Companies
/*
write a query to print the company_code, founder name, total number of lead managers,
total number of senior managers, total number of managers, and total number of employees.
Order your output by ascending company_code.
*/
SELECT C.company_code, C.founder,
count(distinct LM.lead_manager_code) AS totLeadManager ,
count(distinct SM.senior_manager_code) AS totSeniorManager ,
count(distinct M.manager_code) AS totManager ,
count(distinct E.employee_code) AS totEmployee
FROM Company C , Lead_Manager LM , Senior_Manager SM , Manager M , Employee E
WHERE C.company_code =LM.company_code
AND LM.lead_manager_code = SM.lead_manager_code
AND SM.senior_manager_code = M.senior_manager_code
AND M.manager_code = E.manager_code
GROUP BY C.company_code, C.founder
ORDER BY C.company_code서브쿼리로 푸는 방법
/*
write a query to print the company_code, founder name, total number of lead managers,
total number of senior managers, total number of managers, and total number of employees.
Order your output by ascending company_code.
*/
SELECT C.company_code, C.founder,
(SELECT count(distinct LM.lead_manager_code) FROM Lead_Manager LM
WHERE C.company_code = LM.company_code ) AS totLeadManager ,
(SELECT count(distinct SM.senior_manager_code) FROM Senior_Manager SM
WHERE C.company_code = SM.company_code ) AS totSeniorManager ,
(SELECT count(distinct M.manager_code) FROM Manager M
WHERE C.company_code = M.company_code ) AS totManager ,
(SELECT count(distinct E.employee_code) FROM Employee E
WHERE C.company_code = E.company_code ) AS totEmployee
FROM Company C
ORDER BY C.company_code
4. Occupations ***** 어려웠음
- Case를 활용한 피벗 테이블
첫번째 컬럼: Doctor , 두번째 컬럼 : Professor, 세번째 컬럼 : Singer, 네번째 컬럼: Actor
- ROW_NUMBER() OVER ( PARTITION BY ...ORDER BY ) 사용... <- 오라클의 경우에는 이 window함수로 끝!
- mysql의 경우 ROW_NUM 구현하기
| SELECT @RNUM := @RNUM + 1 AS RNUM , A.* FROM 테이블 A , (SELECT @RNUM := 0) B |
- mysql의 경우 ROW_NUMBER(PARTITION BY 컬럼1 ORDER BY 컬럼2) 구현하기
| SELECT @ROW_NUM := IF(@PREV_VALUE = A.컬럼1, @ROW_NUM + 1, 1) AS ROW_NUMBER , @PREV_VALUE := A.컬럼1 , A.* FROM 테이블 A , (SELECT @ROW_NUM := 1) X , (SELECT @PREV_VALUE := '') Y ORDER BY A.컬럼2 |
- CASE 문을 MIN() 으로 감싼 이유 : MAX() 나 MIN() 에서 NULL 은 비교대상으로 삼지 않는다.
SELECT MIN(CASE WHEN A.occupation = 'Doctor' THEN A.name else null END) doctor ,
MIN(CASE WHEN A.occupation = 'Professor' THEN A.name else null END) professor ,
MIN(CASE WHEN A.occupation = 'Singer' THEN A.name else null END) singer ,
MIN(CASE WHEN A.occupation = 'Actor' THEN A.name else null END) actor
FROM (
SELECT
occupation,
name,
-- ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) RNUM
@rownum := IF(@value=occupation,@rownum+1,1) AS RNUM ,
@value := occupation
FROM OCCUPATIONS,
(SELECT @rownum :=0) AS R , -- @rownum 0 reset
(SELECT @value := '') AS P
ORDER BY occupation, name
) A
GROUP BY A.RNUM
'SQL' 카테고리의 다른 글
| [SQLD] 요점정리 1- 1장 데이터 모델링의 이해 (0) | 2022.03.08 |
|---|---|
| DB2와 ORACLE 함수(문법) 차이 (0) | 2022.02.04 |
| [중급 SQL 문제풀이] HackerRank 문제 및 풀이(3) (0) | 2022.01.19 |
| [중급 SQL 문제풀이] HackerRank 문제 및 풀이(2) (0) | 2022.01.17 |
| [중급 SQL 문제풀이] HackerRank 문제 및 풀이(1) (0) | 2022.01.13 |
