문자형 전처리 함수

Split Part

• `split_part(string, delimiter, n)
  : 구분자로 나눈 n번째 값

select split_part('123@456@789', '@', 3)
-- 결과: 789

-- email에서 username 추출
select email, split_part(email, '@', 1) as username
from customer

-- title에서 첫 단어 추춘
select title, split_part(title, '', 1) as first_word
from flim

Position

• position(substring IN string)
  : 문자열 위치 찾기

-- @문자의 위치 찾기
select email, position('@' in email) as at_pos
from customer

-- the가 제목 어디 있는지
select title, position('The' in title) as the_pos
from film where title like '%the%'

Length

• length(string) 문자열의 길이

-- title 길이 계산
select title, length(title) as title_length
from film

Lower Upper

• lower(string) / upper(string) : 소문자/대문자 변환

-- 이름을 소문자로
select first_name, lower(first_name) as lower_name
from customer

-- 이름을 대문자로
select last_name, upper(last_name) as upper_name
from customer

TRIM

• Trim([LEADING/TRAILING/BOTH] chars FROM string): 공백 또는 특정 문자 제거

-- 좌우 공백 제거
select ' Hello ' as raw, trim(' Hello ') as trimmed

-- 특정 문자 제거
select trim(BOTH 'x' from 'xxxxabcxxxx') as clean_text

-- 왼쪽 xxx 지우기
select ltrim('xxxabcxxx', 'x') as clean_text

--오른쪽 xxx 지우기
select rtrim('xxxabcxxx', 'x') as clean_text

REPLACE

• Replace(string, from, to): 치환

-- 하이픈을 공백으로
select replace('2024-05-05', '-', '') as new_date

-- 제목에서 the 제거
select description, replace(description, 'the', '') as cleaned_title
from film
where description like '%the%'

정규표현식 전처리 함수

• 정규표현식은 문자열 패턴을 검색하고 필터링하는 데 사용되는 강력한 문법으로, 특정 형식의 데이터를 찾거나, 문자열을 검증하거나, 데이터를 변환하는 데 활용됨.

패턴	의미	예시	매칭 결과
.	임의의 한 문자	a.c	abc, acc
*	앞 문자의 0회 이상 반복	bo*	b, booo
+	앞 문자의 1회 이상 반복	bo+	bo, boo
?	앞 문자의 0 또는 1회	colou?r	color, colour
[abc]	a, b, c 중 하나	[abc]	a, b, c
[^0-9]	숫자를 제외한 문자	[^0-9]	a, #
{n}	n회 반복	a{3}	aaa
( )	그룹 지정	(abc)+	abcabc
^	문자열의 시작	^Hello	Hello world
$	문자열의 끝	world$	Hello world

Substring

• substring(text from pattern): 부분 문자열 추출
• 즉, text를 pattern에 맞게 추출하는 문법

filmdesc에서 숫자 추출

- 설명(description)에서 연속된 숫자 한 덩어리 추출
select title, substring(desc from '[0-9]+') as num_part
from film
where desc ~ '[0-9]';

email 도메인 추출

-- @ 뒤부터 문자열 끝까지 추출
select email, substring(email from '@(.+)$') as domain
from customer

referrer 컬럼에서 인터넷 주소(domain)만 추출

SELECT stamp,
       substring(referrer FROM 'http[s]?://([^/]+)') AS referrer_host
FROM access_log;

1. http[s]?://

• http
  → 그대로 "http" 매칭
• [s]? → s가 있을 수도 있고 없을 수도 있음
  → http:// 또는 https:// 모두 매칭
• ://
  → 그대로 매칭

2. ([^/]+)

• () → 그룹 지정 → 그룹 안 내용만 substring으로 뽑음
• [^/] → /가 아닌 한 문자
• + → 1개 이상 연속 → / 나오기 전까지 문자 연속 추출

따라서 전체 의미
: /가 나오기 전까지 모든 문자(즉, 도메인 이름)를 그룹으로 추출

url 컬럼에서 도메인, path, id 추출

SELECT stamp,
substring(url FROM 'http[s]?://([^/]+)’) AS url_host
 ,substring(url FROM 'http[s]?://[^/]+/([^?]+)’) AS url_path
 ,substring(url FROM 'id=([0-9]+)’) AS content_id
FROM access_log;

• () : 그룹
• [^?] : not ? → ?를 제외한 아무 문자
• + : 1개 이상 반복. 앞 패턴 ([^?]) 이 1개 이상 반복
  즉, ([^?]+) → ? 문자가 나오기 전까지의 모든 문자열을 가져와라

split_part를 이용한 url의 N번째 요소 추출

SELECT stamp,
split_part(url, '/', 1) AS part_1,
split_part(url, '/', 4) AS part_4
FROM access_log;

숫자와 문자가 섞여 있는 텍스트에서 숫자만 추출

SELECT regexp_replace('a1b2c3', '[^0-9]', '', 'g') AS only_number;

• [0-9] → 숫자
• [^0-9] → 숫자가 아닌 문자
• '' → 제거
• 'g' → global, 전체 문자열 적용
• 'g' 없으면 → 첫 번째 매칭만 제거
• 결과: '123'

숫자 하나씩 배열로 추출하기

SELECT regexp_matches('a1b2c3', '[0-9]', 'g') AS only_number;

• [0-9] → 숫자 하나씩 매칭
• 'g' → 전체 문자열 적용
• 결과 → 배열 형태로 각각의 숫자
• 결과: {1,2,3}

숫자형 전처리 함수

ROUND

• `round(number, digits): 반올림

SELECT rental_rate, round(rental_rate, 0) AS rounded
FROM film;

CEIL / FLOOR

• ceil(number) / floor(number): 올림/내림

SELECT replacement_cost, ceil(replacement_cost), floor(replacement_cost)
FROM film;

ABS

• ABS(number): 절대값

-- 대체 비용과 20의 차이 절대값
SELECT title, abs(replacement_cost - 20) AS diff
FROM film;

-- id를 5로 나눈 나머지
SELECT customer_id, mod(customer_id, 5) AS group_num
FROM customer;

날짜형 전처리 함수

현재 날짜와 시간

SELECT now(), current_date;

• now() → 현재 날짜와 시간 (예: 2025-09-29 13:45:00)
• current_date → 오늘 날짜만 (예: 2025-09-29)

날짜에서 연도 추출

SELECT rental_date, date_part('year', rental_date) AS year
FROM rental;

• date_part('year', rental_date) → 해당 날짜의 연도만 추출
• 예: '2025-01-15' → 2025

참고: date_part는 월(month), 일(day) 등 다른 단위도 추출 가능

AGE

• age(timestamp): 현재와의 간격

-- 계정 생성 후 경과 기간
select create_date, age(now(), create_date) as period
from customer

To_char

• to_char(): 포맷팅된 날짜 출력

-- 포맷된 날짜 출력
select to_char(payment_date, 'YYYY-MM-DD') AS paid_on
from payment

Interal

• Interval: 시간 차이 표현

-- 3일 뒤 날짜
SELECT now() + interval '3 days’ AS future_date;

-- 대여일에 7일 더하기
SELECT rental_date, rental_date + interval '7 days’ AS due_date
FROM rental

NULL 및 조건 처리

Coalesce

• coalesce(a, b, ...): 첫 번째 non-null 값

-- 주소가 없으면 unknown
SELECT address, coalesce(address2, 'Unknown') AS full_address
FROM address;

-- active 표시
SELECT active, coalesce(active, 0) FROM customer;

Nullif

• nullif(a, b): 값이 같으면 null 반환

-- 두 값이 같으면 NULL
SELECT nullif(5, 5) AS result1, nullif(5, 3) AS result2;

-- 같은 이메일이면 null
SELECT email, nullif(email, 'jared.ely@sakilacustomer.org')
FROM customer;

-- 분모에 0이 들어가면 안될 때 null로 처리
SELECT 100/nullif(0,0)  null

-- Group by, distinct, case 조건 만들 때
SELECT COALESCE(NULLIF(region, '전국'), '기타')

형(TYPE) 변환

CAST(expr AS type)

::type

-- 문자열을 정수로
SELECT '123’::int + 10 AS total;

-- 날짜로 형변환
SELECT CAST('2024-04-09’ AS date) + interval '1 day’ AS next_day;