얼렁뚱땅 개발자

PostgreSQL 에서 숫자나 날짜, 시간 데이터를 생성하는 방법입니다.

generate_series(시작, 종료, [단계 혹은 인터벌])

을 이용하여 간단하게 데이터를 출력해 줄 수 있습니다.

select * from generate_series(1, 5);
select * from generate_series(1, 5, 1);

위의 쿼리를 실행하면 아래와 같은 결과를 출력합니다.

select * from generate_series(-4, 3);

음수도 생성합니다.

select * from generate_series(-4, 3, -1);

하지만 위와 같이 숫자가 커지는 데 단계를 음수로 지정할 경우 아무런 데이터도 반환되지 않습니다.

반대로 시작보다 종료인 수가 작은 데 단계를 양수로 지정하거나 지정하지 않을 경우에도 아무런 데이터도 반환되지 않습니다.

select 
	current_date + s.a date
from generate_series(0, 14,7) as s(a)

위와 같이 생성한 데이터를 이용해 날짜 데이터도 생성하는데 활용 가능합니다.

실행결과 :

select * from 
generate_series('2023-07-22 00:00'::timestamp, '2023-07-25 08:00'::timestamp, '10 hours');

실행결과 :

PostgreSQL 에서 여러 컬럼 중 최대값, 최소값을 구하는 방법입니다.

row 간의 값을 비교하는 MAX, MIN 함수의 최대값, 최소값 구하는 방식이랑 다르게

여러 컬럼간의 값을 비교하여 컬럼 중 제일 큰 값, 작은 값을 구할 수 있습니다.

최대값 : GREATEST(컬럼1, 컬럼2, ..., 컬럼n)
최소값 : LEAST(컬럼1, 컬럼2, ..., 컬럼n)

위의 그림은 설명을 위한 테스트 테이블 입니다.

select 
	greatest(column1, column2, column3, column4, column5) greatest_value, -- 최대값
	least(column1, column2, column3, column4, column5) least_value -- 최소값
from 
	(select 
		1 column1,
		2 column2,
		3 column3,
		4 column4,
		1 column5
	from dual
	union all
	select
		2 column1,
		3 column2,
		3 column3,
		4 column4,
		5 column5
	from dual)A

위의 쿼리를 실행하면 아래와 같은 결과가 나오게 됩니다.

row 별로 각각 지정해준 컬럼 값들 중 제일 큰 값, 제일 작은 값을 출력합니다.

PostgreSQL 에서는 CTE 라고 하는 Common Table Expression 을 이용하여 쿼리 결과를 일시적으로 저장해

WITH 구문으로 간단하게 구현하는 방법을 사용할 수 있습니다.

WITH temp_table AS (
	SELECT
		id,
		name,
		age
	FROM person
)
SELECT * FROM temp_table;

혹은 VALUES 구문을 사용해 임시로 테이블을 만들어서 사용할수도 있습니다.

WITH test_table(name, age) AS (
	VALUES
	('홍길동', 29),
	('김개똥', 33)
)
SELECT * FROM test_table;

PostgreSQL 에서는 INSERT, UPDATE, DELETE 후 해당되는 행을 간단하게 조회할 수 있다.

실행 쿼리 뒤에 RETURNING * 만 추가해주면 된다.

ex)

UPDATE TEST_TABLE

SET NAME = '김길동'

WHERE NAME = '홍길동'

RETURNING *;

위의 쿼리를 실행하면 NAME이 홍길동에서 김길동으로 업데이트 된 ROW가 출력된다.

SELECT * FROM TEST_TABLE WHERE NAME = '김길동';

과 같은 결과를 반환한다.

* 자리에 원하는 컬럼만 넣어서 사용 가능하다.

개발을 하다보면 통계 기능을 구현할 때가 있는데 그때 유용하게 쓸 수 있는 ROLLUP 을 정리해 보았다.

롤업을 사용하면 통계 낸 결과의 소계를 쉽게 구할 수 있다.

* Mysql(MariaDB)

SELECT

COL1, SUM(COL2)

FROM TEST_TABLE

GROUP BY COL1 WITH ROLLUP;

​

​

* ORACLE

SELECT

COL1, SUM(COL2)

FROM TEST_TABLE

GROUP BY ROLLUP(COL1);

아래는 활용예시이다.

위와 같은 테스트 테이블을 만들었다.

먼저 이름(NAME)으로 그룹핑해서 평균 급여(SALARY)를 구해보았다.

SELECT NAME, ROUND(AVG(SALARY)) FROM TEST_TABLE GROUP BY NAME;

위의 쿼리를 실행하면 다음과 같은 결과를 볼 수 있다.

여기에 롤업을 추가해서 실행해 보았다.

SELECT NAME, ROUND(AVG(SALARY)) FROM TEST_TABLE GROUP BY NAME WITH ROLLUP;

앞서 실행한 결과에 전체의 평균이 추가로 출력된다.

하지만 이는 전체 결과값의 평균이라 한번 더 감싸서 구하면 평균의 평균을 구할 수 있다.

SELECT

        NAME,

        ROUND(AVG(AVG_SALARY))

    FROM

    (SELECT

        NAME,

        ROUND(AVG(SALARY)) AVG_SALARY

    FROM TEST_TABLE A

    GROUP BY NAME) B

GROUP BY NAME WITH ROLLUP;

위의 쿼리를 실행한 결과 값이다.

평균뿐만 아니라 합계, 최대, 최소 등의 값으로 활용할 수 있다.

비슷하게 오라클에는 CUBE 도 있으니 찾아보는 것을 추천한다.

postgreSql 에서 오라클의 NVL 과 동일하게 사용할 수 있는 함수이다.

COALESCE(A, B)

​

를 사용하면 A가 널이 아닐 경우 A를, A가 널일 경우 B를 반환한다.

java 에서 구분자로 구분해서 문자열을 분리해서 가져오는 split() 처럼

SUBSTRING_INDEX 를 이용하면 mariaDB 에서도 split 같은 기능을 활용할 수 있다.

​

SUBSTRING_INDEX(컬럼명, 구분자, 인덱스)

인덱스에 양수를 넣으면 앞에서부터 몇 번째인지 지정할 수 있고

반대로 음수를 넣으면 뒤에서부터 몇 번째 자리를 가져올지 지정해 줄 수 있다.

​

ex.

​

PHONE 이라는 컬럼에 010-0001-1234 라는 데이터가 들어있다고 가정하자

​

SELECT

SUBSTRING_INDEX(PHONE, '-', 1) PHONE1,

SUBSTRING_INDEX(PHONE, '-', 2) PHONE2,

SUBSTRING_INDEX(PHONE, '-', 3) PHONE3,

SUBSTRING_INDEX(PHONE, '-', -1) PHONE4,

SUBSTRING_INDEX(PHONE, '-', -2) PHONE5,

SUBSTRING_INDEX(PHONE, '-', -3) PHONE6

FROM TEST_TABLE;

​

실행하면

010 0001 1234 1234 0001 010

라는 결과를 얻을 수 있다.

화면에 숫자 뿌려줄 때 보통 화면단에서 처리하긴 하지만

쿼리에서 콤마 찍는 함수가 필요해서 찾아보았다.

세자리 콤마 외에도 소수점까지 제어 가능하다.

FORMAT(컬럼명, 소수점 이하 자릿수)

SELECT

    amount,

    FORMAT(amount, 2),

    ​FORMAT(amount, 0)

FROM

(SELECT '123123123.333' amount FROM dual) A;

​

​

위의 쿼리를 돌려보면 아래처럼 결과를 얻을 수 있다.