[PostgreSQL/SQL] partition by 사용하여 그룹별 순위 매기는 방법, rank(), dense_rank(), row_number() 차이점

1. SQL에서 순위를 매기는 방법

SQL에서 GROUP 별로 순위를 매기고자 할 때 PARTITION BY와 ORDER BY를 사용하여 그룹별 순서를 매길 수 있다. 
대표적인 예시로 

SELECT RANK() OVER (PARTITION BY [그룹화할 칼럼들] ) ORDER BY [순서 정렬할 칼럼들] DESC) AS RANK, * 
FROM [테이블명] A ;

위 SQL이 있을 수 있는데 여기서 가장 먼저 나오는 RANK() 이외에도 DENSE_RANK(), ROW_NUMBER()를 사용할 수 있고 오늘은 이들 각각의 공통점과 차이점에 대하여 알아보겠다. 

1) RANK()

SELECT RANK() OVER (PARTITION BY [그룹화할 칼럼들] ) ORDER BY [순서 정렬할 칼럼들] DESC) AS RANK, * 
FROM [테이블명] A ;

PARTITION BY 뒤에 나오는 칼럼들로 그룹화를 한다. 그리고 동일한 RANK가 있으면 그 다음 순위로 바로 넘어간다. 예를 들어 아래와 같이

SELECT RANK() OVER (PARTITION BY TEAM ORDER BY AGE DESC) AS RANK, * 
FROM 테이블;

과 같은 SQL이 있다고 가정해보자.
그러면 TEAM으로 그룹을 나누고 AGE로 순위를 매긴다. 그리고 RANK()의 특징은 그 전 같은 순위의 값들을 모두 다른 RANK로 간주하고 다음의 숫자로 다음 RANK가 매겨진 다는 것이다. 
예를 들면 1등이 3명 있다면 다음 RANK는 4부터 시작하는 형식이다. 

여기서도 같은 GROUP인 (TEAM에 의해서 PARTITION BY됨) A에서 RANK 1이 2명 있으므로 그 다음 A GROUP의 사람은 RANK 3을 배정받는 것이다. 

2) DENSE_RANK() 

DENSE_RANK()는 RANK()와 유사하지만 동일 RANK가 있을 경우 다음 RANK를 정하는 방식만 다르다. 이전 순위의 중복값의 개수와 상관없이 다음 숫자로 RANK를 매긴다. 

위 예시에서는 

A 그룹의 2등이 숫자 3이 아니라 2로 배정된 다는 것을 알 수 있다. 

즉 같은 AGE가 있을 경우 모두 다 같은 RANK로 간주하고, 그 다음 순위는 그 전 순위에 동차가 있더라도 바로 다음 숫자로 넘어간다. 예를 들어 1등이 3명이더라도 다음 순위는 2등으로 배정된다는 것이 DENSE_RANK()의 특징이다. 

3) ROW_NUMBER()

ROW_NUMBER()는 DENSE_RANK()에서 나아가 해당 칼럼의 동일값이 있어도 아예 중복값을 허용하지 않는 것이다. 즉 위 DENSE_RANK()에서는 ORDER BY AGE에 의해서 AGE라는 칼럼 데이터가 같으면 동일 RANK로 배정을 했는데 ROW_NUMBER()에서는 이것도 허용하지 않는 것이다. 

[PostgreSQL] Tab 들어가있는 데이터 확인 및 제거하는 방법

가끔 SQL을 조회하다 보면 varchar 데이터 뒤에 tab이 들어간 채로 저장이 된 경우가 보인다. 

보이기에는 제대로 된 데이터 같지만 length나 특정 조건으로 조건을 걸면서 조회를 할 때 필터링 되어 조회되지 않을 가능성이 있고 

나중에 Tab Separated 되어 csv 등으로 파일을 추출할 때도 문제가 될 수 있기 떄문에 오늘은 tab이 들어간 데이터들을 조회하고 이들 중 tab들을 제거하는 방법에 대해 알아보겠다. 

1. TAB 이 들어간 데이터 조회하기

chr(39)라는 아스키코드는 작은따옴표를 나타낸다고 지난 포스팅에서 다룬 적이 있었다. 

마찬가지로 TAB도 아스키코드를 사용하여 검출할 수 있다. 

TAB은 chr(9) 와 매핑되며 따라서  chr(9)를 사용하여 데이터를 조회할 수 있다.

예를 들어 EMP_NM (사원명) 에 tab이 들어간 데이터를 확인하고 싶다고 해보자.

SELECT * FROM 테이블명 WHERE EMP_NM (칼럼명) like CONCAT('%', chr(9), '%');

이렇게 조회를 할 수 있다.

chr(9)가 들어간 데이터들을 조회하기 위하여 CONCAT을 사용해서 like 조건으로 조회를 한 것이다. 

2. TAB 이 들어간 데이터 제거하기

그러면 이렇게 TAB이 들어간 데이터에서 TAB을 제거하려면 어떻게 해야할까? 

그럴 때는 REPLACE 함수를 써서 TAB을 empty string으로 교체해주면 된다. 

UPDATE 
테이블명 
SET EMP_NM(사원명) = REPLACE(EMP_NM, chr(9), '') 
WHERE EMP_NM (칼럼명) like CONCAT('%', chr(9), '%');

이런식으로 Replace 함수와 아스키문자를 활용하면 TAB 뿐 아니라 다른 특수문자가 들어간 데이터도 원하는 데이터로 쉽게 변경할 수 있다.

[PostgreSQL] 작은따옴표 concat하는 방법 (아스키코드 chr(39) 사용해보기)

1. PostgreSQL Concat 방법 

오늘은 PostgreSQL에서 특정 칼럼 앞 뒤로 따옴표를 헷갈리지 않게 Concat할 수 있는 방법에 대해 알아보겠다. 

보통 PostgreSQL은 concat을 할 때 || 를 사용해서 예를 들어 

특정 문자열 앞에 Hello를 붙여주고 싶다면 'Hello'||칼럼명

이런 식으로 사용할 수 있다. 

2. chr(39)를 사용하여 작은 따옴표 CONCAT하는 방법

하지만 만약 앞뒤로 작은 따옴표를 붙이고 싶다면 ''' 이런 식으로 쓸 수 없기 때문에 헷갈리지 않게 

chr(39)를 사용할 수 있다. chr(39)는 아스키코드로 작은 따옴표를 뜻한다. 

칼럼 명이 EMP_NM (사원명) 이라고 하고 

앞뒤로 작은따옴표를 붙여서 선택하고 싶다면 

SELECT chr(39)||EMP_NM||chr(39) 

이렇게 적어줄 수 있다

이렇게 특수문자의 경우 chr로 나타낼 수 있기 때문에 아스키코드와 맵핑을 시켜 편리하게 출력해줄 수 있다. 

원하는 위치에 아스키코드를 사용하여 적절히 출력을 해보자.

[MySQL] 유저 생성하고 권한 부여하기 (Intellij Databse Navigator Console 활용하기)

오늘은 MySQL 상에서 유저를 생성하고 권한을 부여하는 방법에 대하여 알아보도록 하겠다. 

그 전에, 우리는 Intellij에서 MySQL을 실행하고 있고 @localhost라는 DB를 사용하고 있다고 가정해보자. 

Console을 클릭하여 Intellij 상에서도 DB를 실행할 수 있어서 매우 편리한 기능이다. 

https://www.programmingstory.com/2024/02/intellij-community-edition-database.html

참고로 Intellij에서 Database Navigator Plugin을 추가하여 사용하는 방법은 위 링크, 전 포스팅에 정리해놓았으니 참고하면 된다. 

먼저 우리는 user1 이라는 root 계정이 아닌 유저를 생성할 것이다. 계정을 생성할 때 password를 지정해줄 수 있는데, password는 간단히 password1이라고 설정해보겠다. 그러면 아래와 같이 SQL을 작성해줄 수 있다. 

create user 'user1'@'localhost' identified by 'password1';

위 SQL을 통해 우리는 user1을 생성할 수 있다. identified by 뒤에는 설정하려는 password를 적어주면 된다. 

다음으로 그러면 user가 잘 생성되었는지를 판단하기 위해서 

select * from `mysql`.`user`; 

라고 적어줄 수 있다. mysql을 MySQL에서 기본적으로 제공되는 schema이다. 

실행 결과 위와 같이 User들이 출력되는 것을 알 수 있다. 

기본적으로 있었던 위 4개의 User에 우리가 생성해준 'user1'이라는 user가 하나 더 추가적으로 생긴 것을 알 수 있다. 

전체를 보면 이렇게 각 User들의 권한이 명시되어 있고 root 계정만 모든 권한들이 다 부여되어 있는 것을 알 수 있다. 

따라서 생성한 User에 특별한 권한을 부여하고 싶다면 추가적으로 SQL을 작성해주어야 한다. 

우리는 특정 Schema에 user1의 권한을 부여해보고 싶다. 

그러면 아래와 같이 SQL을 작성해줄 수 있다. 

grant all on `schema_name`.* to 'user1'@'localhost' with grant option; 

특정 schema의 모든 권한을 user1에게 주고 싶다면 위와 같이 작성할 수 있다.

여기서 "with grant option"이란 다른 사람에게 부여한 권한을 또 다른 사람에게 줄 수 있는 옵션이다. 

권한을 추가해준 뒤 

user1의 현재 권한을 알고 싶다면 아래와 같이 SQL을 작성해 줄 수 있다. 

show grants for 'user1'@'localhost';

그러면 위와 같이 우리가 추가해준 

특정 스키마의 모든 권한, 즉 "ALL PRIVILEGES"까지 출력되는 것을 알 수 있다. 

오늘은 Intellij Database Navigator를 사용하여 MySQL 상 user를 생성해보고 권한을 부여해보는 SQL을 작성하는 포스팅을 해 보았다.

적절한 권한을 가진 user를 생성하여 올바르게 Backend 상에서 유저를 사용해보자. 

[PostgreSQL] 대소문자 변경 함수- UPPER, LOWER, INITCAP

오늘은 PostgreSQL에서 대소문자 변경하는 함수에 대해 알보겠다. 

SQL에서 substring 등을 비교할 때 어떤 것은 소문자, 어떤 것은 대문자로 들어오면 정확히 substring 등을 비교할 수 없기 때문에 모두 대문자로 변환하던지 모두 소문자로 변환하여 비교를 하는 것이 필요하다. 

오늘은 모두 대문자로 만들거나, 모두 소문자로 만들거나, 각 단어의 첫 문자는 대문자, 나머지 문자는 소문자로 변경하는 함수를 알아보도록 하겠다. 

1. 모두 대문자로 만들기 

UPPER(스트링) 

이렇게 써주면 된다. 

예를 들어 UPPER('hello') 는 SQL에서 'HELLO'로 변환될 것이다. 

2. 모두 소문자로 만들기

LOWER(스트링)

이렇게 써주면 된다.

LOWER('HeLlo')는 SQL에서 'hello'로 변환될 것이다.

3. 단어의 첫 문자는 대문자, 나머지 문자는 소문자로 변경하기

INITCAP(스트링)

이런 형태로 써주면 된다. 

INITCAP('HELLO WorLD")는 SQL에서 'Hello World' 로 변환된다. 

Intellij Community Edition에서 Database Navigator 사용하기, plugins 설치, Intellij Database 연동

Intellij에서 기본적으로 제공하는 Data Grip은 Ultimate 유료 버전에서는 Database라는 탭으로 기본 제공이 된다. 

하지만 Intellij Communitiy 무료 Edition에서는 기본적으로 제공하고 있지 않은 기능이라 추가 Plugins를 통해 설치를 해주어야 한다.

플러그인을 설치하는 것은 어렵지 않다. 

Intellij Settings에 들어가서 "plugin"으로 검색 -> 이후에 Marketplace와 Installed 두 개의 탭이 나올 텐데 Installed는 미리 설치한 분들이 가서 찾으면 되고 우리는 미리 설치해주지 않았기 때문에 Marketplace 탭에 들어가 "Database Navigator"를 다운로드 받는다. 

Install 하는데는 시간이 오래 걸리지 않고, 다 Install이 된 다음에는 IDE를 재시작하여 변경사항이 반영되도록 한다. 

Intellij를 재시작하면

위와 같이 View->Tool Windows에 DB Browser를 찾을 수 있다. 

DB Browser를 클릭하여 

원하는 데이터베이스를 추가해줄 수 있다. 

이렇게 오늘은 Intellij에서 Database를 연동하는 방법, 특히 Community Edition에서 Database Navigator Plugin을 Install하여 쉽게 사용할 수 있는 방법에 대하여 알아보았다. 

[Redshift] interval values with month or year parts are not supported 에러 해결법

 Redshift, oracle, postgre 등 다양한 DB가 있지만 오늘은 Redshift를 사용했을 때, 

"interval values with month or year parts are not supported" 

라는 에러 시 sql을 어떻게 수정할 수 있는지에 대하여 알아보도록 하겠다. 


내가 날린 sql은  

특정 칼럼 (데이터 타입: DATE) + interval '1 months' between '2023-01-01' and '2023-12-31'

이다.  

그런데 위와 같이 sql을 적으니  
interval values with month or year parts are not supported 라는 에러가 떴다.  

심지어 초반 sql 실행을 했을 때는 잘 돌아가는 듯 하다가 
중반쯤 되니 에러를 뱉으면서 돌아가지 않아서 굉장히 의아했다.
 보통 syntax error시에 쿼리는 수행조차 되지 않기 때문에 이 경우는 뭐지하는 생각이 들었다.  

하지만 에러 그대로 redshift는 month와 year 에 대해서 interval values를 지원하지 않는다는 것이다.  

따라서 이 쿼리를 수정하기 위해서는,  

dateadd라는 function을 사용해주면 되는데, 

위와 같이 +interval '1 months'를 표현하기 위해서는  


dateadd(month, 1, 원하는 칼럼)


이렇게 사용하면 된다.  

마찬가지로 1년을 더하고 싶으면

dateadd(year, 1, 원하는 칼럼)


이런형태로 적어주면 된다.

            

SQL 자료형 비교하기 NUMERIC VS. BIGINT, INT

 SQL의 자료형은 간단하면서도 다양한 자료형으로 인해 자칫 잘못 사용하면 원하는 바와 다른 결과를 얻을 수 있다. 예를 들어 소수점까지 표현을 해야하는 것인데 Bigint 자료형을 쓰게 되면 반올림되면서 정수형태로 표현이 될 수 있다. 

오늘은 대표적인 SQL 자료형에 대해 알아보도록 하겠다.

---

1. INT 형식의 자료형 (bigint, int, smallint, tinyint)

 

Data Type	Bytes	범위
bigint	8 bytes	( - 2^63 ) ~ ( 2^63 -1 )
int	4 bytes	( - 2^31 ) ~ ( 2^31 -1 )
smallint	2 bytes	( - 2^15 ) ~ ( 2^15 -1 )
tinyint	1 Byte	0 ~ 255

data type의 이름이 암시하는 것과 같이 bigint에서 tinyint로 갈수록 storage bytes의 크기도 줄어들게 되고 그에 따라 표현하는 범위에도 변동이 있다.

---

2. Numeric (전체 자리수 max, 소수점 아래 자리수 max)

int 형식만 표현할 수 있었던 위 bigint, int, smallint, tinyint와는 다르게 numeric 자료형을 사용하면 소수점 아래까지 표현할 수 있다. 

 

예를 들어, 

numeric(8,2)

위와 같은 자료형이 있다고 가정해보자.

 

그러면 총 8자리까지 표현할 수 있는 것이다. 중요한 것은 소수점 아래 자리까지 하여 8자리이며, 소수점 아래로는 2자리까지 표현할 수 있다는 것을 의미한다. 

 

즉 numeric(a,b)의 형식으로 일반화를 시켜보면 아래와 같은 그림으로 a와 b의 자릿수를 이해하면 된다. 

 

numeric과 유사한 데이터 형태로 decimal을 사용할 수도 있다.

SQL merge 문 한방에 이해하기

 SQL에는 insert 문과 update문이 있다. Insert문과 Update문을 결합한 문법 또한 존재하는데 바로 MERGE문이다. 

 

Merge는 Insert와 Update 명령을 결합한 것으로 merge문 하나를 사용하면 update문을 여러 번 사용하지 않아도 된다는 장점이 있다. 따라서 문법만 정확히 알고 있다면 굉장히 편한 것이 merge 문이다. 

 

/*merge문 기본 골격*/

MERGE INTO TABLE_NAME ALIAS
USING TABLE/VIEW/SUBQUERY ALIAS
ON (JOIN CONDITION) --테이블 수정, 입력여부 결정조건
WHEN MATCHED THEN --일치하였을 경우
	UPDATE SET col1=col1_p, col2=col2_p
WHEN NOT MATCHED THEN --일치하지 않았을 경우
	INSERT (LIST to be inserted) 
    VALUES (values) ;

 

이런식으로 merge 문은 match되었을 경우와 match되지 않았을 경우로 나누어서 문법이 구성된다.

ON절의 조건에 따라서 테이블의 update, insert 여부가 결정이 된다. 

 

위와 같이 기본 골격을 보면 헷갈릴 수 있는데, 오히려 간단한 예시를 보면 이해하기가 쉬울 것이다.

 

사원정보를 담은 두 가지 테이블이 있다고 가정해보자.

EMP1과 EMP2라는 테이블이 있는데, 이 중 EMP1 테이블에는 누락된 칼럼 정보가 있을 수 있는 반면, EMP2 테이블에는 사원 정보 자체가 누락되어있을 수 있다.

예를 들어 EMP1 테이블에는 사원 A에 관한 열은 존재하나 월급 같은 column 에 해당되는 정보가 누락되어 있고 해당 정보는 EMP2 테이블에 존재한다. 반면 EMP2 테이블에는 신규 사원 같은 직원들의 정보가 아예 누락되어 있는 경우이다.

 

원래 같았다면 EMP2 테이블을 참고하여 EMP1 테이블을 업데이트하여야 하고, EMP2 테이블에 누락되어 있는 것들을 insert 해야 하는 경우인데 이 프로세스를 merge문을 사용하여 한 번에 해결할 수 있다. 

 

/*MERGE문 사용예제*/

MERGE INTO EMP2 T2
USING EMP1 T1
ON (T1.emp_no = T2.EMP_NO) -- JOIN 조건 : 사번 
WHEN MATCHED THEN
	UPDATE SET T1.sal=T2.sal --사번이 일치할 경우 EMP1 테이블에 salary 정보 update
WHEN NOT MATCHED THEN --일치하지 않을 경우 T2 테이블 insert
	INSERT (EMP_NO, EMP_NM, SAL)
    	VALUES (T1.EMP_NO, T1.EMP_NM, T1.SAL);

 

이런식으로 간편하게 해결할 수 있다.

경우에 따라서는 WHEN MATCHED THEN / WHEN NOT MATCHED THEN 중에서 하나만 사용하여도 무방하다. 

 

MERGE문은 데이터 웨어하우징 응용프로그램에 적합하며, 편리하다는 장점이 있다. 

JOIN 조건에서 key값이 아닌 다른 조건을 사용하면 중복데이터가 발생할 수도 있으니 JOIN 조건을 신중히 결정하면 될 듯하다.

 

MERGE문은 간편하지만 UPDATE나 INSERT 문에 의해 덜 직관적이라는 단점은 존재한다. 하지만 문법을 익히면 매우 편리한 SQL 기능이므로 알아두자.

[PostgreSQL] 데이터를 배열로 나열하기, 배열을 string으로 변경 (ARRAY_AGG, ARRAY_TO_STRING 사용법)

PostgreSQL에서 데이터를 배열로 나열하는 방법에 대하여 알아보자. 단순히 배열을 만들 때 유용할 뿐만 아니라 group by를 사용하는데 max와 min을 쓰고 싶지 않은 경우에도 유용하게 사용할 수 있다. 

크게 사용할 수 있는 문법은 두 가지가 있다. 

1. ARRAY_AGG

이전 포스팅과 동일하게 위의 사원 정보를 담고 있는 사원 테이블이 있다고 가정해보자. 

여기서 2024년도 입사, 즉 사번이 2024로 시작하는 사원들의 이름을 배열로 받고 싶을 경우에는 어떻게 SQL을 작성하면 될까? 

SELECT 
ARRAY_AGG(EMP_NM)
FROM 
사원테이블
WHERE EMP_NO like '2024%';

위와 같이 작성하면 된다. 

결과값은 

{John,Tom}

이렇게 나올 것이고 중괄호가 쳐져 있는 배열 형태로 return이 된다. array 형태이기 때문에 string 형태로 받고 싶을 때 사용하는 것이 두 번째 방법이다.

2. ARRAY_TO_STRING

위에서는 {John,Tom} 이런 식의 array 형태로 출력이 되었는데 이를 중괄호가 없어진 string 형태로 원할 때 사용할 수 있는 것이 ARRAY_TO_STRING이다. 

SELECT 
ARRAY_TO_STRING(ARRAY_AGG(EMP_NM), ',')
FROM 
사원테이블
WHERE EMP_NO like '2024%';

이런 식으로 SQL을 작성해주면 

John,Tom

이라고 String 형태로 출력이 될 것이다. 

ARRAY_TO_STRING의 경우 첫 번째 인자로는 array 인자를, 두 번쨰 인자로는 배열을 구분지을 구분자를 넣어주면 된다.

여기서는 배열의 각 항목을 ',' 쉼표로 구분하여 string을 만들어주겠다는 뜻이다. 

SELECT 
ARRAY_TO_STRING(ARRAY_AGG(EMP_NM), ' ; ')
FROM 
사원테이블
WHERE EMP_NO like '2024%';

위와 같이 ; 로 구분을 하여

John ; Tom 

이런 식의 string을 만드는 것도 가능하고 

SELECT 
ARRAY_TO_STRING(ARRAY_AGG(EMP_NM), ' 구분자 ')
FROM 
사원테이블
WHERE EMP_NO like '2024%';

이런 식으로 다른 string을 넣어

John 구분자 Tom

이렇게 return을 받아 구분하는 것도 가능하다. 

[PostgreSQL] GREATEST/MAX, LEAST/MIN 차이점

오늘은 PostgreSQL에서 자주 사용되는 GREATEST, MAX, LEAST, MIN 차이점에 대하여 알아보자. 

먼저 위와 같은 사원 테이블이 있다고 가정해보자.

EMP_NO에는 사번, EMP_NM에는 사원이름, AGE에서는 사원 나이 정보를 담고 있다. 

위 데이터 내에서 가장 크고, 작은 데이터를 구하고 싶다면 max와 min을 사용하면 된다. 

1. MAX/MIN: 데이터 레코드 중 최대/최소 값을 구하고 싶을 때 사용

만약 사번이 가장 큰 사원을 찾고 싶다면 아래와 같이 SQL을 작성할 수 있다. 

SELECT 
MAX(EMP_NO) 
FROM 
사원테이블; 

마찬가지로 나이가 가장 어린 사원의 나이를 알고 싶다면

SELECT 
MIN(AGE)
FROM 
사원테이블; 

위와 같이 작성할 수 있다. 

다만 만약 여러 칼럼들의 데이터 중에서 최대, 최소인 데이터를 알고 싶다면 어떻게 할까? 

예를 들어 나는 사원 중에 가장 나이가 적은 사람의 나이가 20살보다 적은지 많은지를 알고 싶다. 그럴 때는 GREATEST, LEAST 를 사용할 수 있는 것이다. 

2. GREATEST/LEAST : 여러 칼럼들 중에서 최대/최소값을 구하고 싶을 때 사용

SELECT 
LEAST(MIN(AGE), 20) 
FROM 
사원테이블; 

이런 식으로 적으면 사원테이블 중 나이가 가장 어린 사원의 나이와 20이라는 숫자 중에서 더 작은 값을 return하는 것이다. 

여기서는 MIN(AGE)가 25이므로 사실상 

LEAST(25, 20) 을 계산하는 것과 동일하며 20이 더 적기 때문에 20을 출력하게 된다. 

마찬가지로 GREATEST는 여러 칼럼 중에 최대값을 보여준다. 

GREATEST와 LEAST는 단순 두 칼럼의 비교뿐 아니라 여러 칼럼의 비교도 가능하기 때문에 

GREATEST(1,2,3,4,5) 

이런 식으로 여러 칼럼을 비교해도 된다. 

즉, GREATEST와 MAX, LEAST와 MIN은 비슷해보이고 헷갈릴 수 있지만 사용법이 완전히 다르다는 것이 중요하다. 

[PostgreSQL] Date type 칼럼 데이터를 varchar 형식으로 바꾸기 [YYYYMMDD]

 PostgreSQL을 사용하다 보면 날짜 변환에 있어서 어려움이 생기는 경우가 있다. 

예를 들어 '2022-01-01' 형식으로 들어오는 물리 데이터 타입이 DATE인 칼럼을 

'202201'로 들어오는 varchar 형식의 칼럼과 비교를 하거나 해당 형태로 변환하고 싶을 때는 어떤 식으로 SQL을 작성해주면 될까? 

만약 DATE 타입의 칼럼 이름이 DATE_ST_DT라고 해보자

그러면 

SELECT TO_CHAR(DATE_ST_DT::date, 'YYYYMM') AS DATE_YRMM 

이런식으로 작성할 수 있다. 

만약 DATE_ST_DT에 '2022-12-01'로 들어오는 칼럼이었다면 '202212'로 해서 데이터가 보여질 것이다.

만약 년월일에서 일자까지 보여주고 싶게 한다면 

SELECT TO_CHAR(DATE_ST_DT::date, 'YYYYMMDD') AS DATE_YRMM 

라고 쓰면 된다.

'2022-12-27'의 데이터였다면 형태만 달라져서 '20221227' 로 데이터가 보여질 것이다. 

여기서 YYYY는 year를 의미, MM은 month를 의미, DD는 date를 의미하기 때문에 

단순 랜덤한 알파벳으로 

SELECT TO_CHAR(DATE_ST_DT::date, 'AAAABBCC') AS DATE_YRMM 

이렇게 사용하면 정말 AAAABBCC라는 형태로 찍히니 이 점을 유의해서 

유의미한 YYYYMMDD 중에 원하는 형태로 사용해야 한다. 

즉 TO_CHAR( 변경 원하는 칼럼, 원하는 형식) 

이런 식의 형태의 함수인데 여기서 DATE_ST_DT를 date 형태의 칼럼으로 한 번 인식시키기 위해 ::date를 붙인 것이라고 생각하면 된다. 

PostgreSQL COALESCE 함수란? null 체크 sql

 PostgreSQL에서 사용하는 COALESCE 함수에 대해 알아보자.

sql에서 select를 하다 보면 null 값이 나올 경우를 대비해야 한다. left join 등을 하여 특정 칼럼 값이 null이 나왔는데 이를 0 으로 인식한다던지 하는 경우가 필요하다. 

이럴 때 PostgreSQL에서 사용할 수 있는 문법이 바로 COALESCE이다.

COALESCE(특정 칼럼, 대체값1, 대체값2, .... 대체값n)

이렇게 사용할 수 있다. 

즉, 대체값도 null일 경우를 대비하여 여러개를 사용할 수 있다. 

예를 들어, 

사원정보라는 테이블에 

EMP_NM라는 칼럼으로 홍길동, 강백호가 있다고 쳐 보자. 

EMP_NM	EMP_ENGL_NM
홍길동	Daniel
강백호	null

이런 식으로 특정 사원은 한글 이름만 있고 영어 이름은 없는 경우가 있을 수 있다. 

그런 경우에 우리는

SELECT 

EMP_NM, COALESCE(EMP_ENGL_NM, '')

FROM

사원기본;

이런 식으로 sql을 작성해줄 수 있다. 

만약 NULL값이 있을 수 있다면 그냥 empty string으로 출력하겠다는 의미이다. 

empty string이 아니라 만약 다른 것으로 출력을 원한다면 다르게 사용할 수 있다. 

SELECT 

EMP_NM, COALESCE(EMP_ENGL_NM, EMP_NM, '')

FROM

사원기본;

이런식으로 사용하면 영어이름이 없을 경우 EMP_NM 칼럼을 쓰고, 그것도 null일 경우 empty string으로 출력을 하겠다는 것인데

보통의 경우 EMP_NM은 사원기본의 key값이므로 null일 가능성이 없다. 

대부분 

SELECT 

EMP_NM, COALESCE(EMP_ENGL_NM, EMP_NM)

FROM

사원기본;

이 정도만 써도 null safety하게 sql를 구성할 수 있다. 

SQL distinct 사용법 (멀티, 단일 칼럼의 중복값 제거하기)

만약 name, department, age라는 세 개의 칼럼을 가지고 있는 worker 테이블이 있다고 가정해보자. 그리고 해당 테이블에 아래와 같은 DB 정보가 존재한다고 가정해보자. 

만약 여기서

 select * from worker;  

이라고 하면 회사의 모든 직원이 출력될 것이다. (위 예시의 경우 총 6명) 

하지만 만약 회사의 동명이인을 제외한 unique 한 이름을 가진 사람이 몇 명 있는지 알고 싶다면 어떻게 하면 될까? 

바로 

 SELECT DISTINCT name FROM worker; 

이렇게 작성되면 될 것이다.

이렇게 하고 실행을 시켜보면 James, Kate, John 이렇게 세 명의 unique한 이름이 출력 될 것이다. 

하지만 만약 관리자가 팀 구분자까지 하여 unique한 이름을 알고 싶다고 하면 어떻게 하면 될까? 즉 A 팀과 B 팀에 동명이인이 있더라도 이것은 다른 한 쌍으로 간주하겠다는 것이다.

 SELECT DISTINCT name, department FROM worker;  

이런 식으로 작성하면 된다. 

즉, "DISTINCT"가 name과 department에 모두 걸려있다는 것이다. 

위 sql을 실행시켜보면 name과 department가 모두 중복되는 건은 Kate와 Management밖에 없으니 한 건의 중복만 삭제되어 총 5건이 출력 될 것이다. 

만약 중복이 제거된 workers의 개수를 알고 싶다면 distinct 위에 count를 씌울 수도 있다. 

 SELECT COUNT(DISTINCT(name)) FROM worker ; 

이렇게 하면 직접 우리가 결과 값을 count 하지 않고도 3이라는 숫자가 바로 출력되어 unique한 이름이 몇 개인지 알 수 있다.