[MySQL] 문자열 추출(SUBSTR), 문자열 합치기(CONCAT)

Post는 Hacker Rank: The PADS 를 풀이하는 과정에서 사용한 함수들이다.

https://www.hackerrank.com/challenges/the-pads/problem?isFullScreen=true 

The PADS | HackerRank

 

Problem:

Generate the following two result sets:

1. Query an alphabetically ordered list of all names in OCCUPATIONS, immediately followed by the first letter of each profession as a parenthetical (i.e.: enclosed in parentheses). 
   
   OCCUPATIONS 테이블에서 1. 알파벳 순으로 2. 각 직업의 첫 알파벳 글자를 따와서 아래와 같은 형태로 쿼리하라.
   For example:AnActorName(A),ADoctorName(D),AProfessorName(P), andASingerName(S).  -- 1번 쿼리
   
   
2. Query the number of ocurrences of each occupation in OCCUPATIONS. Sort the occurrences in ascending order, and output them in the following format:

There are a total of [occupation_count] [occupation]s.

각 직업이 몇 개나 있는 지 숫자를 세어 위와 같은 형태로 나타나야 하며, 숫자 순서대로 내림차순 정렬 -- 2번 쿼리

Sample output

Ashely(P) Christeen(P) Jane(A) Jenny(D) Julia(A) Ketty(P) Maria(A) Meera(S) Priya(S) Samantha(D) There are a total of 2 doctors. There are a total of 2 singers. There are a total of 3 actors. There are a total of 3 professors.

 

 

---

 

STEP 1

두 쿼리문의 형태가 다르기 때문에 쿼리를 나누어 출력해야 한다고 생각했다.

우선 1번 쿼리를 짜기 전 occupations table에 대해 살펴보았다.

=> 이름 출력, SUBSTR 통해서 첫 문자 가져오는 것으로 확인, CONCAT을 통해 '(', ')'을 붙여주면 될 것이라고 판단.

STEP 2: 쿼리1 완성

-- Name 컬럼을 가져오고, CONCAT 함수로 문자열 연결, SUBSTR으로 Occupation 컬럼의 첫 번째 위치에서 
-- 한 개의 문자만 가지고 옴
-- SUBSTR(문자열 또는 컬럼, 가져올 위치, length)
SELECT Name, CONCAT('(',SUBSTR(Occupation, 1,1),')') as occupation
FROM OCCUPATIONS
;

OUT:

Ashley (P)

Samantha (A)

Julia (D)

Britney (P)

Maria (P)

 

STEP 3: 쿼리 2 작성

-- 문자열 합치기: CONCAT 함수 사용(문자열 연결)
SELECT CONCAT('There are a total of ', COUNT(Occupation), ' ', Occupation,'s.') 
FROM OCCUPATIONS
GROUP BY Occupation -- count 집계 함수를 세기 위해 occupation으로 그룹핑
ORDER BY COUNT(Occupation)
;

OUT:

There are a total of 3 Doctors.
There are a total of 4 Actors.
There are a total of 4 Singers.
There are a total of 7 Professors.

 

STEP 4: 연결

SELECT Name, CONCAT('(',SUBSTR(Occupation, 1,1),')') as occupation
FROM OCCUPATIONS
ORDER BY 1
;
SELECT CONCAT('There are a total of ', COUNT(Occupation),' ', Occupation,'s.')
FROM OCCUPATIONS
GROUP BY Occupation
ORDER BY COUNT(Occupation)
;

OUT: Wrong Answer

Aamina (D)
Ashley (P)
Belvet (P)
Britney (P)
Christeen (S)
Eve (A)
Jane (S)
Jennifer (A)
Jenny (S)
Julia (D)
Ketty (A)
Kristeen (S)
Maria (P)
Meera (P)
Naomi (P)
Priya (D)
Priyanka (P)
Samantha (A)
There are a total of 3 Doctors.
There are a total of 4 Actors.
There are a total of 4 Singers.
There are a total of 7 Professors.

뭐가 틀렸지? 

1) 1번 쿼리에서 Name과 직업 사이에 공백이 있음

2) 2번 쿼리에서 직업이 대문자로 되어있음

STEP 5: 수정

-- Name 컬럼을 CONCAT 안에 넣어줘서 공백을 지움
SELECT CONCAT(Name,'(',SUBSTR(Occupation, 1,1),')') as occupation
FROM OCCUPATIONS
ORDER BY 1
;
-- Occupation에 lower 함수를 적용하여 소문자로 나타내 주었다
SELECT CONCAT('There are a total of ', COUNT(Occupation),' ', lower(Occupation),'s.')
FROM OCCUPATIONS
GROUP BY Occupation
ORDER BY COUNT(Occupation)
;

OUT: 정답

Aamina(D) 
Ashley(P) 
Belvet(P) 
Britney(P) 
Christeen(S) 
Eve(A) 
Jane(S) 
Jennifer(A) 
Jenny(S) 
Julia(D) 
Ketty(A) 
Kristeen(S) 
Maria(P) 
Meera(P) 
Naomi(P) 
Priya(D) 
Priyanka(P) 
Samantha(A) 
There are a total of 3 doctors. 
There are a total of 4 actors. {-이하 생략-}