Day7. 쿼리에 사용되는 연산자와 함수

1. 쿼리에 사용되는 연산자와 함수의 종류

• 논리,비교 연산자와 조건문 함수
• 배열 연산자와 함수
• JSON 연산자와 함수
• 날짜 및 시간 연산자와 함수
• 자주 쓰이는 연산자와 함수

1.1 논리,비교 연산자와 조건문 함수

: 논리 연산자는 AND,OR,NOT 3가지로 구성된다.

: 논리연산자에서 사용되는 데이터 타입인 BOOLEAN의 결과값은 TRUE, FALSE, NULL 3가지로 구성된다.

1.2 비교범위 연산자

: 조건문 사용시 어떤 표현이 참인지 거짓인지 알아야 할 때 사용하는 비교술어에는 아래와 같은 종류들이 있다.

• IS TRUE : BOOLEAN 조건이 참인지 거짓인지 알아보는 연산자(참:TRUE, 거짓:FALSE)
• IS NOT TRUE : BOOLEAN 조건이 참인지 거짓인지 알아보는 연산자(참:FALSE, 거짓:TRUE, NULL값인 경우 참이 아니므로 TRUE 값을 가진다.)
• IS FALSE : BOOLEAN 조건이 참인지 거짓인지 알아보는 연산자(참:FALSE, 거짓:TRUE, NULL값인 경우 FALSE 값을 가진다.)
• IS NOT FALSE  
• IS NULL : BOOLEAN 조건이 NULL인지 알아보는 연산자(값이 NULL인 경우: TRUE, 값이 NULL이 아닌 경우: FALSE)
• IS NOT NULL

: 비교연산자와 비교술어 사용 시 결과값의 차이

• SELECT NULL = 'false' AS equal_false, NULL IS FALSE AS is_false;
• 첫 번째 NULL = 'false'의 결과값은 NULL의 값을 알 수 없으므로 결과값이 NULL이 된다.
• 두 번째 NULL IS FALSE의 결과값은 결과값이 FALSE가 아니므로 결과값이 FALSE가 된다.

: 아래의 예시와 같이 비교연산자는 범위연산자로 대체할 수 있다.

• SELECT * FROM example_table WHERE  1 <= num_col AND num_col <= 9;
• SELECT * FROM example_table BETWEEN 1 AND 9; 
• SELECT * FROM example_table WHERE  1 <= num_col AND num_col <= 9;
• SELECT * FROM example_table NOT BETWEEN 1 AND 9;

2. 조건문 함수

2.1 CASE 함수

: 프로그래밍 언어에서의 IF-ELSE 문과 대응되는 함수 

: 아래의 형식에서 모든 조건이  TRUE가 아니고 ELSE 문이 없을 경우 NULL 값을 반환한다.

• CASE
•   WHEN < 조건문1> THEN <결과문1>
•   WHEN < 조건문2> THEN <결과문2>
•   ELSE <결과문3>
• END

2.2 COALESCE 함수

: 데이터를 조회할 경우, NULL 값을 일괄적으로 다른 기본값으로 대체할 때 사용한다.

: 아래와 같은 형식으로 작성되며, 함수 내의 매개변수들의 값을 하나씩 검사해 NULL이 아닌 첫 번째 매개변수 값을 출력해준다.

• COALESCE(<매개변수1>,<매개변수2>,....)

2.3 NULLIF 함수

: 0을 NULL 값으로 바꿀 때 사용하는 함수

: 매개변수1과 매개변수2가 같은 경우 NULL을 반환하고, 서로 다른 경우 매개변수 1을 반환한다.

• NULLIF(<매개변수1>,<매개변수2>)

: 주로 수학계산을 할 때 유용하게 쓰인다.특정 값을 0으로 나눠 에러가 발생하는 경우 아래와 같이 사용할 수 있다.

• SELECT students, COALESCE((12/NULLIF(students,0)):: char,'나눌 수 없음') AS share FROM division_by_zero;

3. 배열 연산자

: 배열 연산자에서 비교 연산자를 사용할 경우, 배열 내부의 모든 값들이 TRUE 값을 지녀야 TRUE 값이 출력된다.예를 들어 아래와 같은 쿼리문은 FALSE가 결과값으로 출력된다.

• SELECT ARRAY[5,3,3] > ARRAY[5,2,4] AS result;

: 배열연산자에서 포함관계, 겹침 유/무를 확인하는 연산자는 아래와 같다.

• <@ 또는 @> (포함관계): ARRAY[1,2,3] @> ARRAY[1,3]- 참
• &&(겹침 유/무): ARRAY[1,2,3,4] && ARRAY[1,5,6]- 참

: 배열끼리 병합하는 연산자로는 '||' 을 사용한다.

• 배열끼리 병합: ARRAY[1,2,3] || ARRAY[1,3] - {1,2,3,1,3}
• 2차원 배열로 병합: ARRAY[[1,2,3],[4,5,6]] || ARRAY[7,8,9] - {{1,2,3},{4,5,6},{7,8,9}}
• 원소 배열로 병합: 1 || ARRAY[2,3,4] - {1,2,3,4}

cf> 2차원 배열 : ' '사이에 중괄호를 넣는 방법과 ARRAY[]를 활용해 생성한다.

• INSERT INTO td_array(name, schedule) VALUES ('9DAYS','{{1,2,3},{4,5,6},{7,8,9}}');
• INSERT INTO td_array(name, schedule) VALUES ('9DAYS',ARRAY[[1,2,3],[4,5,6],[7,8,9]]');

4. 배열 함수

: array_append() 함수: 배열에 원소를 추가시키는 함수

• array_append(<배열>,<원소>)
• SELECT array_append(ARRAY[1,2],3) AS result; -[1,2,3]  

: array_prepend() 함수: 가장 최근에 추가한 원소가 가장 앞으로 위치하도록 추가시켜주는 함수

• array_prepend(<원소>,<배열>)
• SELECT array_prepend(1,ARRAY[2,3]) AS result; -[1,2,3]

: array_remove() 함수: 배열에 원소를 삭제시키는 함수

• array_remove(<배열>,<원소>)
• SELECT array_remove(ARRAY[1,2,3,4],4) AS result; -[1,2,3]

: array_replace() 함수: 배열 속 원소를 다른 원소로 바꾸는 함수

• array_replace(<배열>,<기존의 배열 속 원소>,< 바꿀 원소>)
• SELECT array_replace(ARRAY[1,4,3],4,2) AS result; - [1,2,3]

:array_cat() 함수: 배열과 배열을 합치는 함수

• array_cat(<배열1>,<배열2>);
• SELECT array_cat(ARRAY[1,2],ARRAY[3,4]) AS result; -[1,2,3,4]

5. JSON 연산자와 함수

: JSON에는JSON 데이터 타입과 JSONB 데이터 타입이 존재한다.

: JSON 오브젝트에 저장된 키 값으로 밸류 값을 가지고 오고 싶은 경우 아래와 같은 형식으로 쿼리를 작성한다.

• '{"키 값1":"밸류 값1","키 값2":"밸류 값2"}' -> "키 값1"
• SELECT '{"p":{"1":"postgres"},"s":{"1":"sql"}}' :: json -> 'p' AS result; - {"1":"postgres"}

: 인덱스 번호를 이용해 json 데이터 값 불러오기

• SELECT '[{"p": "postgres"},{"s":"sql"},{"m","mongoDB"}]'::json -> -3 AS result; -{"p": "postgres"}

: JSON 데이터를 모두 텍스트로 불러오기

• SELECT '{"p": {"1":"postgres"},"s":{"1":"sql"}}'::json ->> 'p' AS result; -{"1":"postgres"},( 텍스트 형태의 데이터)

: 다층의 JSON 오브젝트로부터 데이터 불러오기(#>>: 특정경로의 값을 텍스트 형태로 불러옴, #>: 특정경로의 값을 가져옴)

• SELECT '{"i":{"love":{"book":"postgresql"}}}' :: json #>> '{"i","love","postgresql"}' AS result; - postgresql(텍스트 형태의 데이터)
• SELECT '{"post":[{"gre":{"sql":"do it"}},{"t":"sql"}]}' :: json #> '{"post",0,"gre","sql"}' AS result; - "do it"