SQL 고급 명령어

이번 포스팅에서는 SQL 고급 명령어에 대해서 정리해보겠다.

 

1) 날짜와 시간 정보를 나타내는 자료형

(1) TIME : 시간만을 나타내는 자료형

(2) DATE : 날짜만을 나타내는 자료형

(3) TIMESTAMP : 날짜와 시간을 모두 나타내는 자료형

(4) TIMESTAMPTZ : 날짜와 시간, 타임존을 나타내는 자료형

 

주의해야할 점은 이미 저장해놓은 타임스탬프를 지울 수는 있지만 새로 추가할 수는 없다는 것이다. 즉, 데이터를 저장할 때 타임스탬프로 저장해놓으면 후에 필요에 따라 날짜만을, 혹은 시간만을 추출해서 사용할 수는 있지만 TIME이나 DATE로 저장해놓은 데이터에는 저장되어 있지 않은 DATE나 TIME을 후에 추가할 수는 없다는 것이다.

 

그럼 이제 위에서 배운 자료형과 관련된 명령어를 배워보자.

2) 날짜와 시간 자료형과 관련된 명령어

(1) TIMEZONE : 타임존을 나타내는 명령어

(2) NOW() : 현재 날짜와 시간을 나타내는 명령어(전부 숫자로)

(3) TIMEOFDAY() : 현재 날짜와 시간을 나타내는 명령어(요일과 달, 타임존은 영어로)

(4) CURRENT_TIME() : 현재 시간을 나타내는 명령어

(5) CURRENT_DATE : 현재 날짜를 나타내는 명령어

 

활용법 : 

(i) SHOW ALL : 실행 시간 값을 보여주는 매개변수

(ii) SHOW TIMEZONE : 현재 사용자의 타임존을 나타냄

(iii) SELECT NOW() : 현재 날짜와 시간을 반환하라는 명령어(전부 숫자로) ex) "2022-10-24 15:25:07.166207+09"

(ix) SELECT TIMEOFDAY() : 현재 날짜와 시간을 반환하라는 명령어(요일과 달, 타임존은 영어로) ex) "Mon Oct 24 15:24:12.917515 2022 KST"

(x) SELECT CURRENT_TIME() : 현재 시간을 반환하는 명령어

(xi) SELECT CURRENT_DATE() : 현재 날짜를 반환하는 명령어

 

특이한 점은 TIMEZONE 명령어는 SELECT가 아닌 SHOW와 같이 쓰인다는 점이다.

 

3) 날짜와 시간 자료형에서 정보를 추출하는 명령어

(1) EXTRACT() : YEAR, MONTH, DAY, WEEK, QUARTER(분기) 키워드와 함께 쓰이며, 날짜 및 시간 자료형의 부속 값을 추출할 수 있음.

(2) AGE() : 타임스탬프가 기록된 시간부터 지금까지의 시간차를 나타내는 명령어

(3) TO_CHAR() : 일자 유형을 문자로 바꿔주는 명령어

 

활용법 : 

(i) EXTRACT(YEAR FROM date_col) : 날짜 데이터 열로부터 연도 추출, YEAR 대신 MONTH, DAY, WEEK, QUARTER 등도 사용 가능. ex) SELECT EXTRACT(YEAR FROM date_col) FROM table;

(ii) AGE(date_col) : ex) 명령어 : SELECT AGE(date_col) FROM table, 반환값 : 13 years 1 mon 5 days 01:34:13.003423

(iii) TO_CHAR(date_col, 'mm-dd-yyyy') : 날짜 및 시간을 나타내는 열 뒤에 적는 'mm-dd-yyyy'와 같은 형태는 구글에 PostgreSQL Data Type Formatting Function을 검색함으로써 다양한 종류를 확인할 수 있다. 아래 링크를 참고하면 된다.

https://www.postgresql.org/docs/current/functions-formatting.html

9.8. Data Type Formatting Functions

예컨대 "2022-10-24 15:25:07.166207+09"라는 타임스탬프 데이터가 있다고 해보자. 이를 SELECT TO_CHAR(2022-10-24 15:25:07.166207+09, 'MON DD Y,YYY') 를 통해 변환시키면 결과는 OCT 24 2,022가 된다. 

이렇듯이 중간에 띄어쓰기로 구분할 수도 있고, 전부 붙여쓸 수도 있고, 혹은 /, - 와 같은 자신이 선택한 기호를 집어넣을 수도 있다.

 

TO_CHAR 함수를 이용하면 다양한 자료형을 문자형으로 바꿀 수 있다. 더 자세한 내용은 위의 링크를 참고하자.

 

추가로 dow라는 키워드를 알아보자. dow라는 키워드는 요일을 숫자로 나타내는 키워드인데, 일요일은 0, 월요일은 1, 화요일은 2, 수요일은 3, 목요일은 4, 금요일은 5, 토요일은 6이다. 예를 들어, 한 테이블에서 목요일날 있었던 결제 건수를 알고 싶다고 하자. 그렇다면 다음과 같이 쓸 수 있다. SELECT COUNT(*) FROM table WHERE(dow FROM payments) = 4;

 

4) 수학 함수와 연산자

이제는 날짜 및 시간 연산자에서 벗어나서 수학 함수와 연산자에 대해서 배워보자.

우선 아래 링크를 참고해보자.

https://www.postgresql.org/docs/9.5/functions-math.html

Mathematical Functions and Operators

위 링크에 나온 연산자들 중에 간단히 몇 가지만 짚고 넘어가자.

(1) + : 덧셈 연산자

(2) - : 뺄셈 연산자

(3) * : 곱셈 연산자

(4) / : 나눗셈 연산자(나눗셈 이후 몫을 반환)

(5) % : 나머지 반환 연산자(나눗셈 이후 나머지를 반환)

 

이뿐만 아니라 여러가지 수학 함수들도 사용할 수 있다. 반올림을 해주는 ROUND 함수, 소수점 이하를 버리는 FLOOR 함수 등이 있다.

필요에 따라 위 링크에서 찾아서 써보자.

 

5) 문자열 함수와 연산자

문자열 함수와 연산자에 관한 도큐멘트는 아래 링크에서 볼 수 있다.

https://www.postgresql.org/docs/9.1/functions-string.html

String Functions and Operators

예시로 몇 가지를 보자면, 문자열을 연결해주는 || 연산자, 문자열의 길이를 계산해주는 LENGTH() 함수, 모든 문자를 대문자로 표현해주는 UPPER() 함수, 모든 문자를 소문자로 표현해주는 LOWER(), 필요한 문자의 길이만큼만 가져오는 LEFT(string, n) 함수 등이 있다.

종류가 다양하기 때문에 모든 것을 알 필요는 없다. 그저 공식 문서를 보고 필요한 것을 가져다 쓸 수 있을 정도라면 충분하다.

 

6) 서브 쿼리

그동안 다뤄왔던 쿼리들은 메인 쿼리들이었다. 즉, 구하고자 하는 것을 직접적으로 나타내는 SELECT문의 범위를 메인 쿼리, 그리고 그 쿼리 안에 또 다른 SELECT문이 들어가면 속에 있는 쿼리를 서브 쿼리라고 한다.

예를 들어, 평균보다 높은 점수를 받은 학생들의 이름과 점수를 출력하려고 한다고 해보자. 이를 서브 쿼리를 통해서 적어본다면 

SELECT student, grade FROM test_scores
WHERE grade > (SELECT AVG(grade) FROM test_scores)

이렇게 적을 수 있다. 그리고 이 서브 쿼리는 메인 쿼리보다 먼저 계산된다.

 

이 서브 쿼리의 실행으로 어떤 열이 추출되었는지 확인하기 위해서 EXISTS()라는 함수 뒤에 주로 쓰인다. 예시 코드로는 다음과 같이 적을 수 있다.

SELECT student_name FROM students
WHERE EXISTS(SELECT student_name FROM school_band)

만약 서브 쿼리로 인해 도출된 결과를 제외하고 싶다면 EXISTS 앞에 NOT을 붙이면 된다. ex) WHERE NOT EXISTS()

 

7) 셀프 조인

셀프 조인이란 자기 자신에게 결합되어 있는 테이블을 말한다. 셀프 조인을 사용하는 이유는 같은 테이블에 있는 열의 값들과 비교해야 하는 상황이 있을 때 유용하기 때문이다.

 

셀프 조인을 위한 특별한 명령어는 없으며, 같은 테이블의 복사본처럼 보이나, 사실은 복사본이 아니라는 것도 알아두어야 한다. 또한 셀프 조인을 사용할 때에는 테이블에 대한 별칭(ailas)를 사용하는 것이 필수적이다. 그렇지 않으면 어떤 표를 지칭하는지 모호해지기 때문이다.

수도코드를 살펴보자.

SELECT tableA.col, tableB.col
FROM table AS tableA
JOIN table As tableB
ON tableA.some_col = tableB.other_col

예시를 들어서 한 번 살펴보자. 다음과 같은 표가 있다고 생각해보자. 

위의 테이블은 회사의 사원을 관리하기 위해 만든 테이블로, id를 나타내는 id열, 이름을 나타내는 name열, 그리고 보고서를 보내면 받는 수신자의 id가 적혀있는 rec_id가 있다. 즉, Andy는 보고서를 Jim에게 보내고, Jim은 Sally에게, Sally는 Bob에게, Bob은 Andy에게 보내는 꼴이다. 이와 같이 숫자로 적혀있으면 헷갈리기 십상이다. 그렇다면 수신자의 이름을 옆에 적어보는 건 어떨까. 이럴 때 사용하는 것이 바로 셀프 조인이다. 이 상황에서는 다른 테이블을 조인하는 것이 아니라, 같은 테이블에 있는 정보를 조인하는 것이다. 그럼 이 문제를 해결하기 위한 코드를 한 번 적어보자.

SELECT emp.name, rec.name FROM Company AS emp
INNER JOIN Company AS rec
ON emp.rec_id = rec.id

차근차근 위의 코드를 뜯어보자. 우리가 나타내고 싶은 것은 먼저 직원의 이름을 왼쪽에 나열하고 오른쪽에 그 직원이 어떤 직원에게 보고서를 보내야 하는지 적는 것이다. 고로, 직원의 이름을 나타내는 열에 수신자의 이름을 나타내는 열을 셀프 조인할 것이다. 셀프 조인을 할 때는 별칭을 사용해야 하므로 FROM과 INNER JOIN 뒤에 사용하는 테이블 AS 별칭을 각각 적어주어야 한다. 그래서 위의 코드에서는 FROM Company AS emp(직원 이름을 나타낼 테이블 역할), INNER JOIN Company AS rec(수신자의 이름을 나타낼 테이블 역할)와 같이 적은 것이다. 그 다음에 추출할 열들을 앞에 테이블 별칭을 덧붙여 적어준다. emp.name, rec.name과 같이 말이다. ON을 보면 emp.rec_id = rec.id가 뒤에 적혀있다. 잘 생각해보면 Andy는 rec_id가 4이므로 즉 Jim에게 보내야 한다. 고로 Andy의 rec_id는 누군가의 id와 같아야 하는 것이다. 그렇기 때문에 직원 이름을 나타내는 테이블 역할을 하는 emp를 앞에 붙여서 emp.rec_id가 조인되는 테이블인 수신자를 나타내는 rec의 id와 같은 사람의 이름을 적어야 하므로, ON emp.rec_id = rec.id라고 적어야 한다.

결과는 다음과 같이 도출된다.

셀프 조인의 개념이 어려울 수 있다. 그러나 충분한 시간을 갖고 이해하려고 하다보면 이해할 수 있을 것이다!