window function은 aggregate function처럼 어떤 계산을 도와줍니다. 하지만 aggregate function의 결과가 하나의 row로 보여진다면, 이 window function은 row마다 결과를 보여줍니다. 결과적으로 동작이 조금 달라서 이름을 다르게 지었다고 보면 됩니다. 실제로 PostgreSQL에서 Aggregate function을 만들고, 그녀석을 window function처럼 사용할 수 있습니다.

예제

avg()

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;

위의 sql 을 해석하면 아래와 같습니다.

  • empsalary table 에서 salary에 대한 avg() 를 구함. 그런데 avg() 는 "depname 칼럼의 값"을 구분(partition)해서 구함.
  • 주의할 점은 OVER가 들어가야 window function으로 인식. 그렇지 않으면 그냥 aggregation이 됨.

만약 group by 를 사용한다면, 아래처럼 바꿀 수 있을 것입니다.

SELECT depname, avg(salary) OVER (PARTITION BY depname) FROM empsalary GROUP BY depname;

결과

depname  | empno | salary |          avg          
-----------+-------+--------+-----------------------
 develop   |    11 |   5200 | 5020.0000000000000000
 develop   |     7 |   4200 | 5020.0000000000000000
 develop   |     9 |   4500 | 5020.0000000000000000
 develop   |     8 |   6000 | 5020.0000000000000000
 develop   |    10 |   5200 | 5020.0000000000000000
 personnel |     5 |   3500 | 3700.0000000000000000
 personnel |     2 |   3900 | 3700.0000000000000000
 sales     |     3 |   4800 | 4866.6666666666666667
 sales     |     1 |   5000 | 4866.6666666666666667
 sales     |     4 |   4800 | 4866.6666666666666667
(10 rows)

rank()

SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;

empsalary table에서 depname의 값별로 salary에 대한 rank 를 구합니다.

ORDER BY

 이 window function에서 OVER부분에 들어가는 ORDER BY는 역할이 좀 다릅니다. 이 partition 안에서 row들의 집합을 window frame이라고 하는데, 많은 window function이 partition전체에서 동작하지 않고 이 window frame의 row 들에서 동작합니다.

ORDER BY가 없으면 기본적으로 window frame은 partition의 모든 row가 됩니다. 그런데 ORDER BY가 OVER에 들어가면 ORDER BY한 column 각각의 row가 window frame이 됩니다. 이 때 중복된 값은 같은 frame이 됩니다.

예제

SELECT salary, sum(salary) OVER () FROM empsalary;
salary |  sum  
--------+-------
   5200 | 47100
   5000 | 47100
   3500 | 47100
   4800 | 47100
   3900 | 47100
   4200 | 47100
   4500 | 47100
   4800 | 47100
   6000 | 47100
   5200 | 47100
(10 rows)


SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
 salary |  sum  
--------+-------
   3500 |  3500
   3900 |  7400
   4200 | 11600
   4500 | 16100
   4800 | 25700
   4800 | 25700
   5000 | 30700
   5200 | 41100
   5200 | 41100
   6000 | 47100
(10 rows)

2개의 column 값 더해서 새로운 값

이번에는 2개의 rank 를 구하고, 이것을 더해 보겠습니다.

SELECT t.code, rank+rank_2 AS sum_rank
from
(
 SELECT code, market, 
  rank() OVER (PARTITION BY market ORDER BY price DESC) AS rank,
  rank() OVER (ORDER BY price DESC) AS rank_2
 FROM test_market
) AS t 
ORDER BY sum_rank DESC;

test_market이라는 table에서 2개의 rank를 구하는데, 1개는 market값 당 price로 내림차순 정렬하여 rank를 구하고, 다른 한 개(rank_2)는 그냥 price 당 내림차순 정렬해서 구합니다. 이렇게 나온 순위(rank)를 더해서 sum_rank 를 구하고 내림차순을 한 쿼리입니다.

partition by

partition by는 위의 예제에서 확인한 것처럼 PARTITION BY로 정의하는 목록이 같은 그룹인지, 다른 그룹인지 구분하는 기준으로 사용되고, 같은 그룹 내에서는 현재 로우 값도 포함해서 계산된 윈도우 함수의 결과 값을 공유합니다.

응용

여러 개의 윈도우 함수를 함께 쓸 때는 각각 OVER 절을 사용해야하는데, 이 때 지정할 원도우 프래임이 복잡하다면, 중복 입력해야하는 이슈가 발생해 오류가 발생할 가능성이 커집니다. 이 문제점을 줄이기 위해서 WINDOW절을 이용합니다

SELECT sum(salary) OVER w, avg(salary) OVER w
  FROM empsalary
  WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

함수 종류

아래는 윈도우 함수의 리스트입니다.

  1. row_number
  2. rank
  3. dense_rank
  4. percent_rank
  5. cume_dist
  6. ntile
  7. lag
  8. lead
  9. first_value
  10. last_value
  11. nth_value


더 자세한 내용을 아래 링크에서 확인할 수 있습니다.

공식문서: https://www.postgresql.org/docs/9.5/static/tutorial-window.html

번역문서: http://postgresql.kr/docs/9.5/tutorial-window.html

PostgreSQL에서 타입을 변경하는 함수는 아래와 같이 지원됩니다. 

CAST()

먼저 SQL 표준인 CAST()입니다.

CAST(value AS type)
 
-- 예시
CAST(1234 AS TEXT)

::

다른 한가지는 PostgreSQL에서 지원하는 :: 입니다.

value::type
 
-- 예시
1234::TEXT

PostgreSQL에서 지원하는 ::이 사용되는 특별한 경우는 '{apple,cherry apple, avocado}'::text[];를 문자열 리터럴인 {apple,cherry apple, avocado}로 변환하고 PostgreSQL이 text의 어레이로 해석하도록 합니다.

함수형 형변환

마지막으로 함수형처럼 사용할 수 있는 형식입니다.

type(value)
 
-- 예시
TEXT(1234)


이 방식은 항상 작용하는 것이 아니라 유효한 유형에만 작동합니다. 예를들어 double 표현은 사용할 수 없지만 float8은 사용이 가능합니다.

SELECT DOUBLE(1234) -- 타입 변환 에러
SELECT FLOAT8(1234) -- 성공


또한 interval, time, timestamp는 아래와 같이 큰 따옴표(")로 묶여버리기 때문에 사용을 할 수 없습니다. 

SELECT TIMESTAMP('20170101') -- ERROR:  syntax error at or near "'20170101'" 에러
SELECT '20170101'::TIMESTAMP -- 성공


그러므로 함수와 유사한 타입 변환 구문은 사용에 제약이 발생하여 피하고 CAST() 또는 :: 방식을 사용하는 것을 권합니다.


자세한 내용은 https://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS-GENERIC에서 확인할 수 있습니다.

  1. 코딩하는흑구 2019.03.05 13:23 신고

    잘보고갑니다~

주어진 날짜에서 일주일의 시작 날짜와 마지막 날짜를 PostgreSQL에서 지원하는 date_trunc()함수를 사용하여 계산할 수 있습니다.

시작날짜 구하기

SELECT date_trunc('week', '2012-07-25 22:24:22'::timestamp); 
-> 2012-07-23 00:00:00

만약 timestamp가 아닌 date만 원한다면 아래와 같이 date 타입으로 변환하여 출력할 수 있습니다.


SELECT date_trunc('week', '2012-07-25 22:24:22'::timestamp)::date; 
-> 2012-07-23

마지막날짜 구하기

SELECT (date_trunc('week', '2012-07-25 22:24:22'::timestamp)+ '6 days'::interval)::date; 
->2012-07-29

응용

주어진 날짜가 이번주에 속해 있는지 확인하려면 아래와 같이 사용할 수 있습니다.

date_trunc('week', now())::date <= 임의의 날짜변수 ::date and 임의의 날짜변수::date <= (date_trunc('week', now()) + '6 days')::date


PostgreSQL의 datetime 함수에 더 자세한 내용은 https://www.postgresql.org/docs/9.5/static/functions-datetime.html에서 확인할 수 있습니다.

Connection Pool이란?

서버는 동시에 사용할 수 있는 사람의 수라는 개념이 존재합니다. 일반적인 Connection Pool을 이용하면 동시 접속자 수를 벗어나게 될 경우 에러(예외)가 발생하게 됩니다. 예외가 발생하면 그 접속자는 더이상 처리를 하지 못하므로, 사이트 이용자는 다시 접속을 시도해야하는 불편함이 있습니다. 이를 해결하기 위해 탄생한 것이 Connection Pool 입니다. Connection Pool이란 동시 접속자가 가질 수 있는 Connection을 하나로 모아놓고 관리한다는 개념입니다. 누군가 접속하면 자신이 관리하는 Pool에서 남아있는 Connection을 제공합니다. 하지만 남아있는 Connection이 없는 경우라면 해당 클라이언트는 대기 상태로 전환시킵니다. 그리고 Connection이 다시 Pool에 들어오면 대기 상태에 있는 클라이언트에게 순서대로 제공합니다. 

다시말해 데이터베이스와 연결된 Connection을 미리 만들어서 pool 속에 저장해 두고 있다가 필요할 때 Connection을 Pool에서 쓰고 다시 Pool에 반환하는 기법을 말합니다. 웹 프로그램에서는 데이터베이스의 환경설정과 연결 관리 등을 따로 XML파일이나 속성 파일을 사용해서 관리하고, 이렇게 설정된 정보를 이름을 사용하여 획득하는 방법을 사용합니다. 웹 컨테이너가 실행되면서 Connection(connection) 객체를 미리 Pool(pool)에 생성해 둡니다. DB와 연결된 Connection(connection)을 미리 생성해서 Pool(pool) 속에 저장해 두고 있다가 필요할 때에 가져다 쓰고 반환합니다. 미리 생성해두기 때문에 데이터베이스에 부하를 줄이고 유동적으로 연결을 관리 할 수 있습니다.


이렇게 Pool 속에 미리 생성되어 있는 Connection을 가져다가 사용하고, 사용이 끝나면 Connection을 Pool에 반환합니다.

설명

만약 한명의 접속자가 웹 사이트에 접속했다고 가정합니다. 해당 웹 사이트에서 접속자는 게시판을 확인하고 자신이 쓴 게시물을 수정하고 또 새로운 게시글을 등록합니다. 그럼 이 한명의 접속자로 인해 DB접속은 아래와 같이 발생합니다.

  1. 데이터 취득
  2. 검색 후 데이터 취득
  3. 데이터 갱신
  4. 데이터 새등록

한명의 접속자로 인해 단 시간에 4번의 DB 접속이 일어납니다. 만약 접속자가 1000명 이라면 몇번의 DB 접속이 일어날까요? 이러한 오버헤드를 방지하기위해 미리 Connection 객체를 생성하고 해당 Connection 객체를 관리하는것을 의미합니다. 즉 'Connection Pool에 DB와 연결을 해 놓은 객체를 두고 필요할 때마다 Connection Pool에서 빌려오는 것' 라고 생각하면 됩니다. 그리고 연결이 끝나면 다시 Pool에 돌려줍니다. Connection Pool을 너무 크게 해놓으면 당연히 메모리 소모가 클것이고, 적게 해놓으면 Connection이 많이 발생할 경우 대기시간이 발생하기때문에 웹 사이트 동시 접속자 수 등 서버 부하에 따라 크기를 조정해야 합니다.

특징

Pool 속에 미리 Connection이 생성되어 있기 때문에 Connection을 생성하는 데 드는 연결 시간이 소비되지 않습니다. Connection을 계속해서 재사용하기 때문에 생성되는 Connection 수가 많지 않습니다. Connection Pool을 사용하면 Connection을 생성하고 닫는 시간이 소모되지 않기 때문에 그만큼 어플리케이션의 실행 속도가 빨라지며, 또한 한 번에 생성될 수 있는 Connection 수를 제어하기 때문에 동시 접속자 수가 몰려도 웹 어플리케이션이 쉽게 다운되지 않습니다.

Connection Pool에서 생성되어 있는 Connection의 갯수는 한정적입니다. 동시 접속자가 많아지면 Connection Pool은 누군자 접속하면 Connection Pool에 남아 있는 Connection을 제공하는 식이다. 하지만 남아있는 Connection이 없을 경우 해당 클라이언트는 대기 상태로 전환이 되고, Connection이 반환되면 대기하고 있는 순서대로 Connection이 제공됩니다.

'DB' 카테고리의 다른 글

[DB] ETL이란  (0) 2018.12.22
[DB] CDC란  (0) 2018.12.22
[DB]Connection Pool  (0) 2017.02.11
[SQL] WHERE절에서 IF문처럼 AND 사용  (0) 2017.02.02
[DB] 클러스터  (0) 2016.11.18
[DB] 무결성 제약조건  (0) 2016.11.18


SELECT ...
FROM ...
WHERE
(
(A = 1 AND (B BETWEEN 0 AND 10) OR B=9999)
OR
(A = 2 AND (B BETWEEN 11 AND 20) OR B=9999)
)


위의 쿼리를 조건문으로 변경하면 아래와 같습니다.

if (A==1) {
	B = 0 ~ 10 OR 9999
}
else if (B==2) {
	B = 11 ~ 20 OR 9999
}


WHERE절의 조건이기 때문에 결과는 boolean타입으로 TRUE 또는 FALSE만 나오게 됩니다. 


'DB' 카테고리의 다른 글

[DB] CDC란  (0) 2018.12.22
[DB]Connection Pool  (0) 2017.02.11
[SQL] WHERE절에서 IF문처럼 AND 사용  (0) 2017.02.02
[DB] 클러스터  (0) 2016.11.18
[DB] 무결성 제약조건  (0) 2016.11.18
[DB] 트랜잭션, REDO와 UNDO 개념  (0) 2016.11.18

SQL 표준은 transaction isolation에 대해 네 가지 레벨로 정의하고 있습니다. 이 네 가지중 가장 엄격한 것은 Serializable이며, 이것은 마치 여러 세션의 같은 트랜잭션 작업을 한 줄로 세워 차례 대로 진행하는 것과 같은 결과를 보장합니다. 나머지 세 가지 레벨은 동시에 진행되는 트랜잭션들 사이 간 허용되는 작업의 범위에 따라서 구분됩니다. 표준안에서는 Serializable이 가능한 transaction isolation 수준은 동시에 진행되는 트랜잭션 사이의 상호 관계가 전혀 없어야 한다고 정의합니다. (실 세계에서는 당연히 동시에 여러 트랜잭션들이 발생할 것이고, 이것들이 어떻게 서로 상호 관계를 안 할 수 있을까? 라는 의문점을 남깁니다.)

각 수준별 이름과 상호 작용 범위는 다음과 같습니다:


dirty read

한 트랜잭션은 다른 트랜잭션에 아직 커밋하지 않은 자료도 읽을 수 있습니다.

nonrepeatable read

한 트랜잭션은 다른 트랜잭션에서 커밋한 자료를 읽을 수 있습니다. (처음 어떤 자료를 읽고, 다시 읽으려고 하는데, 그 사이 다른 트랜잭션이 자료를 변경하고 커밋했다면, 다음 읽는 값이 커밋된 값으로 읽을 수 있습니다.)

phantom read

위와 같은 상황에서 다른 트랜잭션에 의한 커밋된 자료가 있다 하더라도, 항상 자신의 트랜잭션에서 조회 했던 그 자료값 그대로 보여줍니다.

serialization anomaly

트랜잭션 그룹을 성공적으로 커밋 한 결과는 한 번에 하나씩 모든 트랜잭션을 실행할 수 있는 순서와 일치하지 않습니다.


아래는 SQL 표준과 PostgreSQL에서 구현한 트랜잭션 isolation level을 나타낸 표입니다.

Isolation levelDirty ReadNonrepeatable ReadPhantom ReadSerialization Anomaly
Read uncommitted허용, PG에서는 없음가능가능가능
Read committed불가능가능가능가능
Repeatable read불가능불가능허용, PG에서는 없음가능
Serializable불가능불가능불가능불가능


PostgreSQL에서는 네 가지 표준 transaction isolation level 중 하나를 요청할 수 있지만 내부적으로 세 가지 별도의 isolation level만 구현됩니다. 즉, PostgreSQL의 Read Uncommitted 모드는 커밋이 된 자료만 읽기가 가능합니다. 이는 PostgreSQL의 다중 버전 동시성 제어 아키텍처를 표준 isolation level에 매핑하는 유일한 방법이기 때문입니다.

또한 위의 표는 PostgreSQL의 Repeatable read 구현이 phantom reads를 허용하지 않음을 보여줍니다. 더 엄격한 동작은 SQL 표준에 의해 허용됩니다. 네 가지 isolaction level은 어떤 현상이 발생해서는 안되고 어떤 현상이 발생해야 하는지를 정의합니다. 사용가능한 isolation level의 동작은 아래에서 자세히 설명하겠습니다.

만약 트랜잭션 isolation level을 지정하려면 SET TRANSACTION 명령을 사용하면 됩니다.

  • PostgreSQL의 몇몇 자료형과 함수는 트랜잭션 내 특별한 형태로 읽기 특성을 제공합니다. 특히 자동 증가 컬럼으로 사용하는 serial 자료형과 sequence같은 객체는 rollback이 없으며, 자료 변경 즉시 다른 모든 세션에서도 그 변경된 값을 볼 수 있습니다.

PostgreSQL에서의 트랜잭션 레벨

Read Committed Isolation Level

PostgreSQL에서의 기본 레벨임.

SELECT 쿼리

  • 쿼리가 시작했을 당시의 커밋된 데이터만 참조 가능
  • 쿼리가 실행되는 도중 커밋되지 않은 데이터 혹은 동시에 실행되고 있던 트랜잭션에서 커밋된 변경들은 참조하지 않음
  • 외부 트랜잭션이 아닌 자신의 트랜잭션 도중에 일어난 커밋되지 않은 수정 사항은 참조할 수 있음
  • 트랜잭션 내부에서 select가 여러번 있을 때, 각 select 쿼리 사이에 외부 트랜잭션이 커밋을 했으면 커밋된 다음에 실행된 select 쿼리는 커밋된 데이터를 참조하게 됨

UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR SHARE 쿼리

  • select 쿼리와 동일하게 동작
  • 단지 데이터를 변경하는 도중 다른 트랜잭션이 같은 로우에 접근하면 그 트랜잭션이 끝난 후에 변경사항에 대해서 작업을 진행하게 됨. 이 때, 외부 트랜잭션 때문에 변경된 로우가 현재 명령의 where 조건에 맞는지 다시 평가를 하게 됨


Read Committed는 복잡한 검색조건이 있는 상황에 맞지 않고, 은행 잔고 변경과 같은 단순한 경우에 적합.

Repeatable Read Isolaction Level

SELECT 쿼리

  • 현재 트랜잭션이 시작하기전에 커밋된 데이터만 참조
  • 외부 트랜잭션에서 커밋되거나 커밋되지 않은 변경을 참조하지 않음
  • 외부 트랜잭션이 아닌 자신의 트랜잭션 도중에 일어난 커밋되지 않은 수정 사항은 참조할 수 있음

UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR SHARE 쿼리

  • 데이터를 변경하는 도중 다른 트랜잭션이 같은 로우에 접근하면 그 트랜잭션이 끝난 후에 변경하려던 로우가 변경됐는지 확인해서 변경사항이 없으면 원래 진행하려던 명령을 실행하고, 변경됐으면 다음과 같은 에러메세지를 내면서 트랜잭션을 롤백 (ERROR: could not serialize access due to concurrent update)
  • 읽기만 하는 트랜잭션에서는 에러가 없음


Read Committed와 차이점은 현재 트랜잭션에 여러번의 쿼리가 있을 경우, 현재 트랜잭션의 쿼리 중간에 다른 트랜잭션에서 변경이 있었더라도 참조하지 않고, 현재 트랜잭션이 시작되었을 때의 데이터와 현재 트랜잭션이 수행되던 도중에 일어난 변경사항만 참조

이 레벨을 사용하면 실패했을 때 재시도하는 것을 어플리케이션에서 처리해야함

Serializable Isolation Level

가장 엄격한 트랜잭션 레벨

  • 트랜잭션들이 동시에 일어나지 않고, 하나씩 순서대로 실행되는 것처럼 작동
  • Repeatable Read처럼 어플리케이션에서 실패했을 때 재시도 처리를 해줘야함
  • Repeatable Read와 동일하게 작동하면서 트랜잭션들이 순서대로 실행중인지 모니터링
  • 잘 고려하여 사용하지 않으면 성능저하 이슈가 존재


참조문서:

https://www.postgresql.org/docs/current/static/transaction-iso.html

http://arisu1000.tistory.com/27756

+ Random Posts