[SQL] 데이터 캠프 수강 (6) - Functions For Manipulating Data in PostgreSQL

다음 4가지의 과정을 배운다.

• Common data type in PostgreSQL
• Data and Time functions and operators
• Parsing and manipulating text
• Full-text search and PostgreSQL Extensions

---

Common data type in PostgreSQL

• Text data types
  • CHAR, VARCAHR, TEXT
• Numeric data types
  • INT, DECIMAL
• Data / Time data types
  • DATE, TIME, TIMESTAMP, INTERVAL
    • DATE + INTERVAL '3 days' = 3일 더해줌
    • INTEVAL '1' day * num = num days로 변환됨 (날짜 계산에 사용하기 위해 변환 필요)
• Arrays
  • 배열 형태이며 값을 추출하는 방식은 Python과 똑같다.
  • ANY : Array 형태의 값 중 하나만 있더라도 반환.
    • 찾고자 하는 값 = ANY(col) 형태로 작성
    • WHERE array_name @> ARRAY ['serch text'] 를 이용해 ANY와 같은 결과를 추출한다.

DB의 Schema는 DB의 자료구조를 나타낸다.

아래의 코드는 DB내의 SCHEMA 중에서 테이블 명이 tableA인 Columns을 추출해준다.

SELECT *
FROM DB.SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tableA';

 

SELECT COLUMN_NAME, DATA_TYPE -- 컬럼 명과 데이터 타입을 볼 수 있다.
FROM DB.SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tableA';

 

[ARRAY 생성하는 법]

 CREATE TABLE table_name (
 	col_name1 DATA_TYPE,
    col_name2 DATA_TYPE[][],
    col_name3 DATA_TYPE[]
);   

INSERT INTO table_name
VALUES(value1, '{{value2, value3}, {value4, value5}}',
{value ...})'

[ARRAY 추출하는 법]

SELECT
col1[1][1] AS ,
col1[1][2] AS ,
col2[1],
FROM TableA

---

Data and Time functions and operators

[Overview of basic arithmetic operators]

• AGE 함수를 사용해 두 시간의 차이를 계산할 수 있다.
• interval 값을 반환해준다.

SELECT AGE(timestamp '2021-03-22 12:00:00', timestampe '2021-03-24-00 00:00:00');

[Functions for retrieving current date/time]

• NOW() : 현재 날짜 및 시간의 Timestamp 값을 반환해준다.
• CURRENT_TIMESTAMP : PostgreSQL에서 현재 날짜 및 시간을 추출하는 또 다른 방법이다. 
  • NOW()와 다른 점 : CURRENT_TIMESTAMP(2) 를 지정하면 마이크로 초 단위의 소수점 둘 째 자리까지만 보임
• CURRENT_DATE : PostgreSQL에서 현재 날짜를 추출하는 방법이다.
• CURRENT_TIME : PostgreSQL에서 현재 시간을 추출하는 방법이다.

Casting : Column의 Type을 바꿔 출력하게 한다.

• PostgreSQL에서는 '::' 을 활용한다.
  • ex) SELECT NOW() :: timestamp;
• 일반적인 SQL에서는 CAST() 를 활용한다.
  • ex) SELECT CAST(NOW() as timestamp);

 

[Extracting and transfoming date/time data]

• EXTRACT(field FROM source)
  • field : MONTH, YEAR, DAY, DOW(요일 : 일요일 = 0, 토요일 = 6) 
  • ex) EXTRACT(month FROM CURRENT_TIMESTAMP) : '월' 값만 나옴
• DATE_PART('filed', source)
  • ex) DATE_PART('month', CURRENT_TIMESTAMP) : '월' 값만 나옴
• DATE_TRUNC() : 특정 field를 기준으로 간격을 나누고INTERVAL 또는 timestamp로 값을 반환
  • ex) DATE_TRUNC('month', TIMESTAMP '2021-03-25 12:00:00') -> 2021-03-01 00:00:00

---

Parsing and manipulating text

• Reformatting string and character data
• Parsing string and character data
• Determine string length and character position
• Truncating and padding string data

[Reformatting string and character data]

• CONCAT(col1, ' ', col2) : 문자열 연결 기능을 한다.
• col1 || ' ' || col2 : 문자열 연결 기능을 한다
• UPPER/LOWER(col) : 문자열을 대문자/소문자로 반환한다.
• INITCAP(col) : 단어의 맨 앞의 글자만 대문자화 시킨다.
• REPLACE(col, 바꾸려는 문자, 바뀐 후의 문자) : 바꾸려는 문자를 바뀐 후의 문자로 변환시켜준다.
• REVERSE(col) : 값을 거꾸로 반환한다.

[Parsing string and character data]

• CHAR_LENGTH/LENGTH(col) : 각 행의 문자열 수를 반환
• POSITION('문자' IN col) / STRPOS(col, '문자') : 문자열의 위치 반환 (왼쪽에서 오른쪽으로 순서가 진행됨)
• LEFT/RIGHT(col, 숫자) : col의 문자를 왼쪽/오른쪽에서 부터 읽어 숫자만큼 추출해준다.
• SUBSTRING(col, 시작 숫자, 끝 숫자) : col의 문자를 시작 ~ 끝 만큼 추출해준다.
  • SUBSTRING(col, FROM 0 for POSITION('문자' IN col)) 과 함께 쓸 수 있다.
• SUBSTR(col, 시작 숫자, 끝 숫자) : col의 문자를 시작 ~ 끝 만큼 추출해주지만, FROM FOR은 사용 못함.

[Truncating and padding string data]

• TRIM([leading | trailing | both]) [characters] from string) : 기준 문자를 기준으로 옵션에 따라 삭제 후 반환
  • 주로 공백을 제거하기 위해 사용한다.
  • 첫번째 파라미터의 기본값은 'both'
  • 두번째 파라미터의 기본값은 ' ' (공백)
  • LTRIM/RTRIM : 왼쪽/오른쪽부터 시작하여 삭제
• LPAD(문자, 길이, 채울 문자) : '채울 문자'를 왼쪽부터 채워 '문자'랑 합쳤을 때 해당 길이가 될 때 까지 합친다.
  • ex) LPAD('padded', 10, '#) -> ####padded 의 결과가 반환
• RPAD(문자, 길이, 채울 문자) : '채울 문자'를 오른쪽부터 채워 '문자'랑 합쳤을 때 해당 길이가 될 때 까지 합친다.
  
  • ex) RPAD('padded', 10, '#) -> padded#### 의 결과가 반환
  • 만약 채울 문자에 값을 입력하지 않는다면, 공백으로 채워진다.
  • 만약 길이가 문자보다 짧다면, 결과가 길이만큼 짤려서 나타난다.

---

Full-text search and PostgreSQL Extensions

• to_tsvector(col) @@ to_tsquery(문자) : 대,소문자를 구분하지 않고 col 값이 문자를 포함하는 모든 경우를 반환
  • to_tsvector(col) : 명사, 형용사, 동사를 추출하여 '단어' : '위치' 형태의 벡터로 반환한다.

[Extending PostgreSQL]

• User-defined functions

CREATE FUNCTION squared(i integer) RETURNS integer AS $$
	BEGIN
    	RETURN i * i;
	END;
$$ LANGUAGE plpgsql; -- $$ = 함수가 SQL을 언어로 사용하도록 지정하는 것

SELECT squared(10); -- RESULT : 100

 

[Intro to PostgreSQL extensions]

• PostGIS : 위치 쿼리를 SQL로 실행하도록 지원
• PostPic : 데이터베이스 내에서 이미지 처리를 허용
• fuzzystrmatch, pg_trgm : 전체 확장 기능을 제공

SELECT name
FROM pg_available_extensions;

위 코드를 통해 설치 및 확장 가능한 패키지(?) 들을 볼 수 있다.

SELECT extname
FROM pg_extension;

위 코드를 통해 이미 설치되어 사용 가능한 패키지(?) 들을 볼 수 있다.

CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;

위 코드를 통해 패키지를 로드할 수 있다.

 

[패키지 설명]

• levenshtein(문자1, 문자2) : 두 문자를 일치시키기 위해 바꿔야하는 문자의 수를 계산한다.
• similarity(문자1, 문자2) : 문자 간의 유사도를 계산한다.