일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | |||||
3 | 4 | 5 | 6 | 7 | 8 | 9 |
10 | 11 | 12 | 13 | 14 | 15 | 16 |
17 | 18 | 19 | 20 | 21 | 22 | 23 |
24 | 25 | 26 | 27 | 28 | 29 | 30 |
- rank
- HTML
- blog
- advColumnGrid
- GIT
- queryset
- anaconda3
- Delphi
- python3
- pyhcarm
- pythonanywhere
- python 3.7
- Push
- MSSQL
- 중복제거
- declare
- COMMIT
- 백준
- 델파이
- templates
- c#
- get_object_or_404
- hackerrank
- github
- TMS
- Django
- delphi 10.3
- PyCharm
- dbadvgrid
- Visual Studio
- Today
- Total
DevHyun
Occupations 본문
www.hackerrank.com/domains/sql
Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output column headers should be Doctor, Professor, Singer, and Actor, respectively.
Note: Print NULL when there are no more names corresponding to an occupation.
Input Format
The OCCUPATIONS table is described as follows:
Input Format
The OCCUPATIONS table is described as follows:
Occupation will only contain one of the following values: Doctor, Professor, Singer or Actor.
Sample Input
Sample Output
Jenny Ashley Meera Jane
Samantha Christeen Priya Julia
NULL Ketty NULL Maria
Explanation
The first column is an alphabetically ordered list of Doctor names.
The second column is an alphabetically ordered list of Professor names.
The third column is an alphabetically ordered list of Singer names.
The fourth column is an alphabetically ordered list of Actor names.
The empty cell data for columns with less than the maximum number of names per occupation (in this case, the Professor and Actor columns) are filled with NULL values.
번역 : OCCUPATIONS 테이블의 행을 열로 PIVOT 하라, 순서는 알파벳 순서로 정렬!
풀이 : PIVOT 을 사용해도 좋겠지만 값을 SUM하는 것이 아니라서 굳이 쓰진 않았음. PIVOT 없이 CASE문을 이용.
1. 첫번째 시도, 순수 CASE만 이용해서 행을 열로 바꾸기
SELECT CASE WHEN OCCUPATION = 'DOCTOR' THEN NAME ELSE NULL END AS 'DOCTOR' ,CASE WHEN OCCUPATION = 'PROFESSOR' THEN NAME ELSE NULL END AS 'PROFESSOR' ,CASE WHEN OCCUPATION = 'SINGER' THEN NAME ELSE NULL END AS 'SINGER' ,CASE WHEN OCCUPATION = 'ACTOR' THEN NAME ELSE NULL END AS 'ACTOR' FROM OCCUPATIONS 결과값 SAMANTHA NULL NULL NULL NULL NULL NULL JULIA NULL NULL MEERA NULL NULL ASHELY NULL NULL NULL KETTY NULL NULL NULL CHRISTEEN NULL NULL NULL NULL NULL JANE JENNY NULL NULL NULL NULL NULL PRIYA NULL NULL NULL NULL MARIA |
2. HACKERRANK에서 원하는 결과값은 예를들어 OCCUPATION의 중복값이 최대 3이면 3줄로 출력되는것. 그래서 OCCUPATION 별로 RANK를 지정하여 순위를 메김
SELECT NAME,OCCUPATION, RANK() OVER (PARTITION BY OCCUPATION ORDER BY NAME ASC) AS RANK FROM OCCUPATIONS 결과값 JANE ACTOR 1 JULIA ACTOR 2 MARIA ACTOR 3 JENNY DOCTOR 1 SAMANTHA DOCTOR 2 ASHELY PROFESSOR 1 CHRISTEEN PROFESSOR 2 KETTY PROFESSOR 3 MEERA SINGER 1 PRIYA SINGER 2 |
3. 순위를 메긴 테이블의 행을 열로 바꾸고 각 RANK별 MAX값을 가져오게 한 뒤에 GROUP BY 처리.
* NULL 값 : 빈값은 NULL로 지정하라는 조건이 있었기 때문에 따로 처리 안함
* 쨔라란~
SELECT DOCTOR,PROFESSOR,SINGER,ACTOR FROM (SELECT RANK,MAX(CASE WHEN OCCUPATION = 'DOCTOR' THEN NAME ELSE NULL END) AS 'DOCTOR' ,MAX(CASE WHEN OCCUPATION = 'PROFESSOR' THEN NAME ELSE NULL END) AS 'PROFESSOR' ,MAX(CASE WHEN OCCUPATION = 'SINGER' THEN NAME ELSE NULL END) AS 'SINGER' ,MAX(CASE WHEN OCCUPATION = 'ACTOR' THEN NAME ELSE NULL END) AS 'ACTOR' FROM ( SELECT NAME,OCCUPATION, RANK() OVER (PARTITION BY OCCUPATION ORDER BY NAME ASC) AS RANK FROM OCCUPATIONS) AS A1 GROUP BY RANK ) T1 결과값 DOCTOR PROFFESOR SINGER ACTOR -------------------------------------------- JENNY ASHELY MEERA JANE SAMANTHA CHRISTEEN PRIYA JULIA NULL KETTY NULL MARIA |
이것이 MEDIUM의 벽인가...
꽤 오래걸렸음.
PIVOT 대신 CASE를 사용하는것 까진 오래 걸리지 않았으나,
RANK함수를 통해 순위를 메기고 이것들을 MAX로 묶고, GROUP BY 로 GROUPING 하는 생각을 하기 까지 오래걸림.
'SQL > HACKERRANK' 카테고리의 다른 글
New Companies (2) | 2020.11.04 |
---|---|
Print Prime Numbers (0) | 2020.10.29 |
Draw The Triangle 1 (0) | 2020.10.29 |
Weather Observation Station 5 (0) | 2020.10.29 |
Weather Observation Station 4 (0) | 2020.10.29 |