[DAY 21] SQL 함수를 통해 숫자, 문자, 날짜 형태의 데이터 다루기, NULL 처리하기

설 연휴가 지난 20230125 수요일이다. 이번주는 3일 연속으로 Special Lecture인 SQL이 예정되어 있다.

집계함수, group by, order by, having 등 내용을 복습한 후 함수에 대해 공부했다.

함수 - 숫자 다루기

ROUND(number, n) : number의 소수점 n+1번째 자리에서 반올림
- ROUND(10.5678, 3) : 10.568

TRUNC(number, n) : number의 소수점 n+1번째 자리에서 내림(절삭)
- TRUNC(10.5678, 3) : 10.567

MOD(number, n) : number를 n으로 나눈 나머지
- MOD(10, 3) : 1

POWER(number, n) : number의 n제곱
- POWER(10, 3) : 1000.0

SQRT(number) : number의 제곱근
- SQRT(10) : 3.1622776601683795


연습문제

### SQL 연습문제 6-1
상품정보(products) 테이블에서 상품의 id, 상품명(name), 판매가격(retail_price)를 조회합니다. 
판매가격은 반올림하여 소수점 2자리까지 표시 합니다.

select
  id,
  name,
  round(retail_price, 2) as retail_price
from `thelook_ecommerce.products`

 

### SQL 연습문제 6-2
회원(users) 테이블에서 나이가 홀수인 유저만 조회하세요.
조회 항목은 id, first_name, last_name, age 입니다.

select 
  id,
  first_name,
  last_name,
  age
from `thelook_ecommerce.users`
where mod(age, 2) = 1

함수 - 문자열 다루기

SUBSTR(string, start_position, [length]) : string의 start_position에서부터 length만큼의 문자열
- 문자열의 시작 위치는 1
- length를 생략하면 마지막 문자까지
- SUBSTR('hello world', 1, 5) : hello
- SUBSTR('hello world', 3, 5) : llo w
- SUBSTR('hello world', 3) : llo world

LEFT(string, length) : string의 가장 왼쪽에서부터 length만큼의 문자열
- LEFT('sql is fun', 3) : sql

RIGHT(string, length) : string의 가장 오른쪽에서부터 length만큼의 문자열
- RIGHT('sql is fun', 3) : fun

CONCAT(string1, string2, [string3], ...) : 여러 string들을 하나로 연결
- CONCAT('paul', '-', 'lab') : paul-lab

LOWER(string) : string을 모두 소문자로 변경
- LOWER('ABcdE') : abcde

UPPER(string) : string을 모두 대문자로 변경
- UPPER('ABcdE') : ABCDE

INITCAP(string) : string의 각 단어 첫 글자를 대문자로, 나머지는 소문자로 변경
- INITCAP('sqL is FUN') : Sql Is Fun

REPLACE(original_value, from_value, to_value) : original_value에서 from_value를 to_value로 변경
- REPLACE('hello world', 'world', 'sql') : hello sql

LENGTH(string) : string의 길이
- LENGTH('hello world') : 11

INSTR(source_value, search_value) : source_value에서 search_value의 위치
- INSTR('hello world', 'w') : 7

응용 : 이메일 주소에서 @ 앞까지만 추출하는 방법
- select left('abcdef@gamil.com', instr('abcdef@gmail.com', '@') - 1)


연습문제

### SQL 연습문제 6-3
회원(users) 테이블에서 전체이름(full_name)을 조회하세요.
성(first_name)과 이름(last_name)을 합쳐서 조회합니다.
이름(last_name)은 모두 대문자로 표시합니다.

select
  first_name,
  last_name,
  upper(last_name),
  concat(first_name, ', ', upper(last_name)) as full_name,
  first_name || ', ' || upper(last_name) as full_name2,
from `thelook_ecommerce.users`

concat을 사용하지 않고 ||를 통해 문자열 연결도 가능

### SQL 연습문제 6-10
회원(users) 테이블에서 다음 내역을 조회하세요.
- id
- 이름(first_name)
- 이름의 길이(name_length)
- 이름(first_name)의 앞 3글자(part_name)
- 이름의 앞 3글자를 별표 처리한 이름(name_with_asterisk)

select
  id,
  first_name,
  length(first_name) name_length,
  left(first_name, 3) part_name,
  concat('***', substr(first_name, 4)) name_with_asterisk
from `thelook_ecommerce.users`

 

형변환 함수 CAST

숫자로 변환
- int 혹은 float로 직접 지정할 수도 있고 NUMERIC을 사용할 수도 있다

# 숫자로 변환
select '123' + '123'  # 에러
select CAST('123' AS INT64)  # 정수로 지정하여 변환
select CAST('123' AS INT64) + CAST('123' AS INT64)  # 246
select CAST('123.123' AS FLOAT64)  # 실수로 지정하여 변환
select CAST('123' AS NUMERIC)  # 정수로 자동 변환
select CAST('123.123' AS NUMERIC)  # 실수로 자동 변환

문자열로 변환

# 숫자(INTEGER, FLOAT) -> 문자
select CAST(123 AS STRING)
select CAST(123.123 AS STRING)

# true, false -> 문자
select CAST(true AS STRING)
select CAST(false AS STRING)

# “NULL” 이라는 문자열로 바뀌지 않고 NULL 값이 된다
select CAST(NULL AS STRING)

 

DATE - 날짜 데이터 다루기

CURRENT_DATE() : 2023-01-25 (현재 날짜 반환)
DATE(2023, 1, 1) : 2023-01-01
DATE('2023-1-1') : 2023-01-01
DATE('2023-01-01') : 2023-01-01

년, 월, 일 추출
EXTRACT(YEAR FROM DATE('2023-12-31')) : 2023
EXTRACT(YEAR FROM DATE '2023-12-31') : 2023
EXTRACT(MONTH FROM DATE('2023-12-31')) : 12
EXTRACT(DAY FROM DATE('2023-12-31')) : 31


연습문제

### SQL 연습문제 6-4
회원(users) 테이블에서 회원아이디(id), 이메일(email), 가입연도(signup_year)을 조회하세요.

select 
  id,
  email,
  extract(year from date(created_at)) as signup_year
from `thelook_ecommerce.users`

 

### SQL 연습문제 6-7
회원(users) 테이블에서 가입연도(signup_year), 연도별 가입자 수(user_count)를 조회하세요.

select
  extract(YEAR from date(created_at)) as signup_year,
  count(id) as user_count
from `thelook_ecommerce.users`
group by signup_year

 

### SQL 연습문제 6-13
회원(users) 테이블에서 남성유저의 가입연도별 국가별 데이터를 조회하세요.
가입 연도(signup_year), 국가명(country), 가입자수(user_count), 평균나이(avg_age) - 반올림하여 소수점 2자리까지
정렬순서는 가입연도 내림차순, 가입자수 내림차순 입니다.
그룹핑 결과에서 가입자수가 100명 이상인 데이터만 표시해주세요.
select
  extract(year from date(created_at)) signup_year,
  country,
  count(id) user_count,
  round(avg(age), 2) avg_age
from `thelook_ecommerce.users`
where gender = 'M'
group by signup_year, country
having user_count >= 100
order by signup_year desc, user_count desc

 

DATETIME - 날짜와 시간 데이터 다루기

CURRENT_DATETIME() : 2023-01-25T01:54:29.468646 (현재 날짜와 시간 반환)
DATETIME(2023, 1, 25, 05, 30, 00) : 2023-01-25T05:30:00
DATETIME("2023-12-25") : 2023-12-25T00:00:00
DATETIME("2023-12-25 05:30:00") : 2023-12-25T05:30:00

시간, 분, 초 추출
EXTRACT(HOUR FROM CURRENT_DATETIME()) : 1
EXTRACT(MINUTE FROM CURRENT_DATETIME()) : 54
EXTRACT(HOUR FROM CURRENT_DATETIME()) : 29


연습문제

### SQL 연습문제 6-12
회원(users) 테이블에서 브라질 여성유저의 시간대별 유저 가입자수를 조회하세요.
- 시간대(hour)
- 가입자수(user_count)

select
  extract(hour from datetime(created_at)) hour,
  count(id) user_count
from `thelook_ecommerce.users`
where country = 'Brasil' and gender = 'F'
group by hour
order by hour

이후 프로그래머스 문제를 풀며 반복학습했다.
프로그래머스 lv1
12세 이하인 여자 환자 목록 출력하기
경기도에 위치한 식품창고 목록 출력하기
강원도에 위치한 생산공장 목록 출력하기
조건에 맞는 도서 리스트 출력하기
흉부외과 또는 일반외과 의사 목록 출력하기
조건에 맞는 회원수 구하기
최댓값 구하기

프로그래머스 lv2
NULL 처리하기
입양 시각 구하기(1)
DATETIME에서 DATE로 형 변환


NULL 값의 대체값을 지정할 수 있는 IFNULL 함수에 대해서도 배웠다.
그리고 BigQuery와 MySQL에선 IFNULL을 사용하지만 Oracle에선 IFNULL이 없고 NVL이 있는 점, MySQL은 EXTRACT함수를 쓰지 않고도 YEAR, MONTH, HOUR 등의 함수로 날짜와 시간 데이터를 더 직관적으로 다룰 수 있는 점 등 벤더에 따라 지원하는 함수 차이가 있음을 실습을 통해 확실히 알게 되었다.