목차

1. 유용한 함수 및 기능
   1-1. union all, union (SQL 공용)
   1-2. 여러개의 데이터를 한줄로 표시 (PostgreSQL, Oracle)
   1-3. 데이터 랜덤 셀렉트 (SQL 공용)
   1-4. 한국 타임존으로 변환
   1-5. 테이블 초기화
   1-6. 날짜 생성해주는 SQL (PostgreSQL)
   1-7. 날짜 시간 함수 (PostgreSQL)
   1-8. Null 값을 0으로 세팅 해주는 함수
   1-9 json값으로 다중 값 엮기
2. 마치며

1. 유용한 함수 및 기능

1-1. union all, union (SQL 공용)

• 다수의 테이블의 칼럼을 한번에 출력하기 위해 사용합니다.

• 두 집합을 결합한 합집합의 형태로 반환합니다.

• 두 함수의 차이점은 다음과 같습니다.

함수	기능
UNION	두 테이블의 결과를 중복 제거하여 출력
UNION ALL	두 테이블의 결과를 중복하여 출력

• 사용 예시입니다.

SELECT 
 A.TEMP_NAME, A.TEMP_CHECK, A.TEMP_LOG
FROM
(
SELECT 
	A.TEMP_NAME
    , '' AS TEMP_CHECK
    , A.TEMP_LOG
FROM 
	TEMP A
UNION all
SELECT 
	B.TEMP_NAME
    , B.TEMP_CHECK
    , B.LOGS AS TEMP_LOG
FROM 
	TEMP B
) A;

• 위와 같이 칼럼의 명칭, 순서를 동일하게 해주고 데이터 타입까지 맞춰주어야합니다.

1-2. 여러개의 데이터를 한줄로 표시 (PostgreSQL, Oracle)

• PostgreSQL

SELECT 
	COUNTRY
	, ARRAY_TO_STRING(ARRAY_AGG(COUNTRY_NM),',') 
FROM 
	TB_COUNTRY
GROUP BY 
	COUNTRY;

• Oracle

SELECT 
	COUNTRY
	, WM_CONCAT(COUNTRY_NM)
FROM 
	TB_COUNTRY
GROUP BY 
	COUNTRY;

• 위 SQL문을 실행하면 ARRAY_AGG가 배열로 묶고 그 결과 값을 ARRAY_TO_STRING이 STRING 값으로 변환하여 한줄로 표시할 수 있습니다.

1-3. 데이터 랜덤 셀렉트 (SQL 공용)

• PostgreSQL

SELECT 
	column 
FROM 
	table 
ORDER BY 
	RANDOM() 
LIMIT 
	1

• MySQL

SELECT 
	column 
FROM 
	table 
ORDER BY 
	RAND()
LIMIT 
	1

• Oracle

SELECT 
	column 
FROM 
	(
    	SELECT
        	column
        FROM
        	table
        ORDER BY
        	dbms_random.value
)
WHERE
	ROWNUM = 1

• Microsoft SQL Server

SELECT 
	TOP 1 
FROM 
	table 
ORDER BY 
	NEWID()

• 위 SQL문을 사용하면 테이블에서 랜덤으로 LIMIT 값만큼 출력합니다.

1-4. 한국 타임존으로 변환

SELECT
	to_timestamp(reg_dt) AT TIME ZONE 'Asia/Seoul' as reg

• AT TIME ZONE과 지역을 입력해 해당 나라의 타임존으로 변환할 수 있습니다.

1-5. 테이블 초기화

TRUNCATE TABLE table_name
RESTART IDENTITY;

• TRUNCATE TABLE 테이블을 초기화해주고 세팅 값을 유지 시켜줍니다.

• Idx는 초기화되지 않기 때문에 RESTART를 통해 Idx를 초기화합니다.

	ALTER 
		SEQUENCE table_column_seq restart with 3 owned by table.column;

전체 초기화를 하지 않고 원하는 시퀀스부터 증가하게 하는 쿼리입니다. owned 앞의 숫자를 바꾸면 n-1까지 남기고
n부터 증가하게 됩니다.

1-6. 날짜 생성해주는 SQL (PostgreSQL)

SELECT 
	to_char(
    	generate_series(
        	'2021-01-01 00:00'::timestamp,'2021-12-31 12:00', '1 month'
    ),'YYYY-MM'
) AS arraymonth

• generate_series 함수를 통해 날짜를 생성합니다.

• 1번째 부분에 시작날짜가 들어가며, 2번째 부분은 종료날짜, 3번째 부분은 간격이 들어갑니다.

• 날짜가 필요할때 with절로 가져와서 조인하면 성능 및 가시성이 월등해집니다.

1-7. 날짜 시간 함수 (PostgreSQL)

1-7-1. 날짜를 시간 타입으로 캐스팅

SELECT 
	'20210101'::date, '2021-01-01'::date;
 
SELECT 
	date '2021-01-01';
 
SELECT 
	to_date('01012021','MMDDYYYY'), to_date('2021-01-01', 'YYYY-MM-DD');
 
SELECT 
	to_timestamp('2021-01-01 12:12:12', 'YYYY-MM-DD HH:MI:SS');

• 날짜를 시간 타입으로 캐스팅합니다.

1-7-2. 현재시간 날짜 구하는 함수

SELECT 
	now(), current_date, current_time

• 현재시간 날짜 구하는 함수입니다.

1-7-3. 시간을 더하고 빼는 함수

SELECT 
	'2021-01-01'::date + interval '2 sec'
    , '2021-01-01'::date - interval '2 sec';
    
SELECT 
	'2021-01-01'::date + interval '2 min'
    , '2021-01-01'::date - interval '2 min';
 
SELECT 
	'2021-01-01'::date + interval '2 hour'
    , '2021-01-01'::date - interval '2 hour';
 
SELECT 
	'2021-01-01'::date + interval '2 day'
    , '2021-01-01'::date - interval '2 day';
 
SELECT 
	'2021-01-01'::date + interval '2 week'
    , '2021-01-01'::date - interval '2 week';
 
SELECT 
	'2021-01-01'::date + interval '2 month'
    , '2021-01-01'::date - interval '2 month';
 
SELECT 
	'2021-01-01'::date + interval '2 year'
    , '2021-01-01'::date - interval '2 year';
 
SELECT 
	('2021-01-01'::date + interval '2 day') + interval '2 hour';
 
SELECT 
	now() + (600 * interval '1 sec')
    , now() + interval '600 sec';
 
SELECT 
	now(), now() + interval '15h 2m 12s';

• interval를 통해 날짜를 더하고 뺄 수 있습니다.

1-7-4. 해당 날짜 구하기

SELECT 
	extract(year from now());

• extract 다음 부분에 year을 입력하면 년, month를 입력하면 달, day를 입력하면 일, dow를 입력하면 요일이 나옵니다.

dow - 일요일(0) ~ 토요일(6)
isdow - 월요일(1) ~ 일요일(7)

1-7-5. 시간 포맷팅하기

SELECT 
	to_char(now(), 'YYYY-MM-DD HH24:MI:SS')
 
SELECT 
	to_char(now(), 'MONTH:Month:month:MON:Mon:mon:WW:W:D')

• 현재 시간을 출력하는 now() 함수에 to_char함수를 결합하여 원하는 포맷팅으로 변환할수 있습니다.

1-8. Null 값을 0으로 세팅 해주는 내장 함수

SELECT
	COALESCE(column, 0)

• Null값이 들어오면 0으로 변환해주는 함수입니다. 보통 통계나 숫자 값으로 리턴가야되는 값들에 사용합니다.

1-9. json값으로 다중 값 엮기

SELECT
	JSONB_AGG(
    		JSONB_BUILD_OBJECT(
            		'column', column
                    , 'column2', column2
            ) ORDER BY column ASC
    ) AS json
FROM
	table
               

• JSONB_BUILD_OBJECT를 통해 다중 값을 하나에 담고 그 담은 값을 JSONB_AGG를 통해 배열로 묶어서 값을 가져옵니다.

• 상당히 유용하며 원하는 결과 값들을 가공해서 보낼 수 있습니다.

SELECT
	(
	   SELECT 
		row_to_json(_)
	   FROM 
		(
	           SELECT
		         *) AS _
 ) AS json
FROM
	table

• JSONB_BUILD_OBJECT의 단점은 각 칼럼들을 전부 다 입력 해주어야 하는 것인데 위 함수를 사용하면 앞에 있는 alias값
  을 입력하지 않고 사용할 수 있습니다.

2. 마치며

• 제가 사용하며 유용했던 함수들을 정리하였습니다.

• 도움이 되셨길 바랍니다.