[TIL152] HackerRank - Prepare - SQL

문자열의 첫 글자와 마지막 글자 추출하기 (HackerRank - STATION)

문제

STATION 테이블에서 도시명(CITY)이 모음(a,e,i,o,u)으로 시작하고 모음으로 끝나는 도시명을 중복 없이 조회하세요.

---

내가 작성한 코드

SELECT DISTINCT CITY 
FROM STATION 
WHERE LOWER(SUBSTR(CITY,1,1)) IN ('a','e','i','o','u')
  AND LOWER(SUBSTR(CITY,LENGTH(CITY),1)) IN ('a','e','i','o','u');

---

생각 흐름

1. 문제를 보고 도시명의 첫 글자와 마지막 글자를 확인해야 한다고 생각함.
2. 문자열의 일부를 추출하는 함수인 SUBSTR()를 사용해야겠다고 판단함.
3. 첫 글자는 문자열의 시작 위치가 1이므로

SUBSTR(CITY,1,1)

을 사용함.

1. 마지막 글자는 위치를 모르므로 문자열 길이를 먼저 구해야 함.

LENGTH(CITY)

를 사용하여 마지막 글자의 위치를 구함.

1. 따라서 마지막 글자는

SUBSTR(CITY,LENGTH(CITY),1)

로 추출할 수 있음.

1. 대소문자를 모두 처리하기 위해 LOWER()를 적용한 뒤 모음 여부를 확인함.

---

필요한 개념

SUBSTR()

문자열의 특정 위치부터 원하는 길이만큼 추출하는 함수.

형식

SUBSTR(문자열, 시작위치, 길이)

예시

SELECT SUBSTR('SEOUL',1,1);

결과

S

---

첫 글자 추출

예시

SEOUL

위치

S E O U L
1 2 3 4 5

---

첫 글자

SUBSTR('SEOUL',1,1)

↓

S

 

---

마지막 글자 추출

예시

SEOUL

길이

LENGTH('SEOUL')

↓

5

---

따라서

SUBSTR('SEOUL',5,1)

↓

L

---

즉

SUBSTR(CITY,LENGTH(CITY),1)

은

문자열 길이 위치부터
1글자 추출

이라는 의미.

---

왜 LENGTH(CITY)를 사용하는가?

도시마다 길이가 다르기 때문.

예

SEOUL -> 길이 5

---

INCHEON -> 길이 7

---

마지막 글자의 위치가 계속 달라지므로

LENGTH(CITY)

를 이용해 마지막 위치를 동적으로 찾음.

---

LOWER()

문자열을 소문자로 변환.

대소문자를 구분하지 않고 모음 여부를 검사할 수 있음.

---

IN()

여러 값 중 하나인지 확인.

---

정답 흐름

1. 첫 글자 추출

SUBSTR(CITY,1,1)

1. 마지막 글자 추출

SUBSTR(CITY,LENGTH(CITY),1)

1. 둘 다 소문자로 변환

LOWER(...)

1. 둘 다 모음인지 확인

IN ('a','e','i','o','u')

1. 중복 제거

DISTINCT

---

정답 쿼리

SELECT DISTINCT CITY 
FROM STATION 
WHERE LOWER(SUBSTR(CITY,1,1)) IN ('a','e','i','o','u')
  AND LOWER(SUBSTR(CITY,LENGTH(CITY),1)) IN ('a','e','i','o','u');

---

핵심 한 줄

SUBSTR(문자열, 시작위치, 길이)에서 마지막 글자를 추출할 때는 문자열 길이인 LENGTH()를 시작 위치로 사용한다. 즉 SUBSTR(col, LENGTH(col), 1)은 "마지막 위치부터 1글자 추출"이라는 의미이며, 문자열 길이가 달라도 항상 마지막 글자를 가져올 수 있다.

 

 

----

The Blunder (HackerRank)

문제

EMPLOYEES 테이블에서 직원들의 급여(Salary)가 저장되어 있다.

Samantha는 실수로 모든 급여의 숫자 0을 제거한 뒤 평균 급여를 계산했다.

실제 평균 급여와 Samantha가 계산한 평균 급여의 차이를 구하시오.

단, 결과는 올림(Ceil) 하여 정수로 출력해야 한다.

---

내가 작성한 코드

SELECT ROUND(
    AVG(salary) - AVG(REPLACE(salary,0,''))
) AS amount_of_error
FROM employees;

---

생각 흐름

1. 문제를 보고 실제 평균 급여와 잘못 계산된 평균 급여를 비교해야 한다고 생각함.
2. 실제 평균 급여는

AVG(salary)

로 계산할 수 있음.

1. Samantha는 급여에서 모든 0을 제거했으므로

REPLACE(salary,'0','')

를 사용해야 함.

1. 제거된 급여들로 평균을 계산하면 Samantha가 계산한 평균 급여가 됨.
2. 두 평균의 차이를 구하면 오류 금액을 계산할 수 있음.
3. 마지막으로 문제에서 요구하는 방식으로 올림 처리해야 함.

---

필요한 개념

REPLACE()

문자열에서 특정 문자를 다른 문자로 변경하는 함수.

REPLACE(문자열, 찾을문자, 바꿀문자)

예시

REPLACE('1050','0','')

결과

15

---

 

숫자 컬럼에서도 REPLACE가 가능한 이유

salary는 숫자 컬럼이지만,

REPLACE(salary,'0','')

를 수행하면 내부적으로

1050
↓
'1050'
↓
'15'
↓
15

처럼 처리됨.

---

ROUND() 와 CEIL() 차이

ROUND()

일반 반올림.

ROUND(10.4)

↓

10

 

---

CEIL()

무조건 올림.

CEIL(10.1)

↓

11

---

CEIL(10.9)

↓

11

---

정답 흐름

1. 실제 평균 급여 계산

AVG(salary)

1. 0을 제거한 급여 계산

REPLACE(salary,'0','')

1. 잘못된 평균 급여 계산

AVG(REPLACE(salary,'0',''))

1. 두 평균의 차이 계산

AVG(salary)
-
AVG(REPLACE(salary,'0',''))

1. 결과를 올림

CEIL(...)

---

정답 쿼리

SELECT CEIL(
    AVG(Salary) - AVG(REPLACE(Salary,'0',''))
) AS amount_of_error
FROM EMPLOYEES;

---

핵심 한 줄

급여에서 특정 숫자를 제거하는 문제는 REPLACE()를 활용하며, 문제에서 "올림"을 요구하면 ROUND()가 아니라 CEIL()을 사용해야 한다. 특히 문자열 함수인 REPLACE()는 숫자 컬럼도 내부적으로 문자열로 변환하여 처리할 수 있다는 점을 기억해야 한다.

 

 

--- 

 

POWER()

제곱.

POWER(값, 지수)

 

예

POWER(3,2)

 

↓

9

 

---

SQRT()

제곱근.

SQRT(값)

 

예

 

SQRT(25)

 

↓

5