[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로 나타낼 수 있기 때문에 아스키코드와 맵핑을 시켜 편리하게 출력해줄 수 있다. 

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

[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' 로 변환된다. 

[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를 구성할 수 있다.