세븐 데이터 베이스를 읽고(4) PostgreSQL 고급 쿼리와 실행 코드 및 룰

PostgreSQL 고급 쿼리와 실행 코드 및 룰

이전 글에서 CRUD, Join, 인덱싱에 대해 공부해봤다. 이번 글에서는 데이터를 쿼리할 수 있는 여러 가지 방법에 대해 더 깊게 알아볼 것이다.

 

집계 함수

집계 쿼리는 주이진 기준에 맞추어 여러 행들은 그룹으로 분류한다. 그 결과는 행들의 개수, 숫자 타입 열들의 평균등이 된다. 공부하기에 앞서 events 테이블에 데이터 하나를 추가한다. 

 

서브쿼리 

insert를 진행할 때 서브쿼리를 사용하면 venue_id를 일일이 찾지 않아도 된다. 

INSERT INTO events (title, starts, ends, venue_id)
VALUES('Moby', '2012-02-06 21:00', '2012-02-06 23:00', 
(SELECT venue_id FROM venues WHERE name = 'Crystal Ballroom') 
);

INSERT INTO events (title, starts, ends, venue_id)
VALUES('Valentine''s Day', '2012-02-14 00:00:00', '2012-02-26 23:59:00', 
(SELECT venue_id FROM venues WHERE name = 'Crystal Ballroom') );

INSERT INTO events (title, starts, ends, venue_id)
VALUES('Wedding', '2012-02-26 21:00', '2012-02-26 23:00', 
(SELECT venue_id FROM venues WHERE name = 'Crystal Ballroom') );

위와 같이 name이 같은 venue_id를 반환해주는 서브쿼리문을 작성해주면  venue_id칼럼에 넣어주면 직접 넣지 않아도 된다. 

 

그럼 추가된 데이터를 이용하여 몇가지 집계 쿼리를 해보자. 가장 간단한 집계 함수는 count()이다. 쿼리의 결과는 3이될 것이다. 이는 April, Cristmas, Valentine 등을 카운트하여 결과를 반환한 결과이다.

SELECT count(*) 
FROM events
WHERE title LIKE '%Day%';

결과는 3이 나올것이다.

 

또한 가장 빠르게 시작하는 시각, 가장 늦게 끝나는 시각등을 알아보기 위해서는 min(), max() 집계함수를 사용하면 된다. 

 

SELECT min(starts), max(ends) 
FROM events JOIN venues
ON events.venue_id = venues.venue_id
WHERE venues.name = 'Crystal Ballroom';

 

집계함수들은 유용하지만 그 나름의 한계가 있다. 만일 개최되는 모든 이벤트의 개수를 각 장소별로 알고 싶다면, 다음과 같이 venue_id마다 쿼리를 따로 작성해야한다. 

SELECT count(*) FROM events WHERE venue_id = 1;
SELECT count(*) FROM events WHERE venue_id = 2;
SELECT count(*) FROM events WHERE venue_id = 3;
SELECT count(*) FROM events WHERE venue_id IS NULL;

 이럴 때는 GROUP BY 절을 사용한다. 

 

그룹으로 분류하기

GROUP BY를 사용하면 바로 위의 쿼리들을 단 한 번의 쿼리로 할 수 있다. 즉. 그룹별로 행들을 분류하고 실행하도록 만든다. 

 

SELECT venue_id, count(*)
FROM events
GROUP BY venue_id;

Group NULL, 4가 하나씩 나온다. 다르게 변환해서 사용하면 어떻게 될까? 만약 Group BY를 title로 기준을 잡게 된다면..

 

SELECT title, count(*)
FROM events
GROUP BY title;

이번에는 count에 조건을 추가하여 원하는 값을 가져오겠습니다. 

 

SELECT venue_id, count(*) 
FROM events 
GROUP BY venue_id
HAVING count(*) >= 2 AND venue_id IS NOT NULL;

SELECT venue_id FROM events GROUP BY venue_id;
SELECT DISTICNT venue_id FROM events;

count 없이 Group BY를 사용하면 단순히 그룹을 보여준다. 이런식으로 자주  사용해서 DISTINCT 키워드를 추가하여 사용할 수 있다. 

 

트랜잭션

트랜잭션은 관계형 데이터베이스의 데이터 일관성을 유지해주는 방어벽이다. '모 아니면 도'가 트랜잭션의 모토이다. 즉 트랜잭션은 한 집합의 모든 명령들이 함께 실행 또는 취소되도록 보장해준다. 만일 그 중 어떤 명령이라도 실행에 실패하면 모든 명령들이 롤백된다.

 

PostgreSQL의 트랜잭션은 ACID 규약을 따르는데, 이것은

1. 원자성(Atomic, 한 트랜잭션의 모든 연산이 성공 또는 실패함) - 트랜잭션 단위로 데이터가 처리된다. 

2. 일관성(Consistant, 트랜잭션 단위의 데이터 변경시 데이터가 항상 일치하는 상대가 됨) - 커밋이 된 후 데이터 베이스가 일괄적으로 업데이트 된다.

3. 독립성(Isolated, 트랜잭션들이 상호 간섭하지 않음) - 다른 트랜잭션에 영향을 끼치지 않음.

4. 지속성(Durable, 설사 서버에 장애가 생긴 후라도 커밋된 트랜잭션은 안전함.) - 결과가 영구적으로 적용됨.

 

트랜잭션은 BEGIN TRANSACTION 블록으로 둘러싼다. 원자성을 보장하는지 검증하기 위해 다음과 같이 ROLLBACK 명령을 사용해서 고의적으로 트랜잭션을 취소시킬 것이다. 

 

BEGIN TRANSACTION;
 DELETE FROM events;
 ROLLBACK;
 SELECT * FROM events;

결과를 확인하면 events 테이블의 모든 데이터가 삭제되기 전의 상태인 것을 알 수 있다. 

 

트랜잭션을 유용하게 사용할 수 있는 예가 은행의 입출금 시스템에서 돈이 한 계좌에서 다른 계좌로 이체되는 경우이다. 

 

BEGIN TRANSACTION;
 SQL 코드
 END;

만일 사이에 있는 SQL 문이 잘못되면 돈을 잃게 된다. 그러나 TRANSACTION을 해주었기 때문에 은행 계좌에 문제가 생겨도 ROLL BACK이 진행되므로 안전하다.