PostgreSQL에서 집합 함수와 연산자 사용하기

원본출처 : http://www.whiterabbitpress.com/lg/issue70/williams.html

PostgreSQL에서 집합 함수와 연산자 사용하기

By Branden R Williams

한글번역 전정호

이 글은 한글번역판입니다. 원문은 여기에서 볼 수 있습니다.

--------------------------------------------------------------------------------

서문:

이 글은 독자가 SQL 데이테베이스에서 SELECT, INSERT, UPDATE, DELETE의 기본을 안다고 가정한다. 이들이 뭘하는지 모른다면 다른 글을 우선 참고하라. SELECT 질의를 할 수 있다면 이 글을 읽는데 충분하다. 그러면 집합 함수로 가자!

요약:

이 다소 종합적인 글에서 처음에는 PostgreSQL에서 가장 기본적인 집합 함수 5개를 다룬다. 이들은 count(), min(), max(), avg(), sum()이다. 그후 자주 사용되는 여러 연산자를 다룰 것이다. 개발환경에 따라 연습하는 좋은 방법은 직접 DBMS에서 실행하여 그대로 사용할 수 있는 결과를 얻는 것이다. 이 방법은 집합 함수를 사용할 때 분명하다. 마지막으로 집합 함수와 함께 연산자를 사용하는 법을 다룰 것이다. 이 글은 정확히 원하는 자료를 질의하기위해 간단한 연산자를 사용하는 법을 설명한다.

집합(aggregate) 함수란 무엇인가?

집합 함수는 count()나 sum()과 같이 총합을 계산하기위한 함수이다. SQL 집합 함수로 여러 통계값을 얻을 수 있다. 집합 함수는 데이터베이스에서 정보를 얻는데 필요한 코드량을 크게 줄인다.

(PostgreSQL 7.1 메뉴얼에서 발췌)

aggregate_name (표현식)

aggregate_name (ALL 표현식)

aggregate_name (DISTINCT 표현식)

aggregate_name ( * )

aggregate_name는 이미 정의된 집합 함수이고, 표현식은 그 자체로 집합 함수를 포함하지 않는 표현식이다.

첫번째 경우는 NULL이 아닌 필드를 가진 모든 행에 적용된다. (실제로 NULL을 무시할지 여부는 집합 함수에 달렸다. 그러나 모든 표준 함수는 무시한다.) 두번째는 ALL이 기본값이기 때문에 첫번째와 같다. 세번째 경우는 NULL이 아닌 필드를 가진 모든 구별되는 행에 적용된다. 마지막 경우는 NULL 여부와 관계없이 모든 행에 적용된다. 특별한 입력값이 지정되지 않았기 때문에 보통 count() 집합 함수에서만 유용하다.

책 판매를 기록하는 프로그램을 작성한다고 가정하자. 책 제목, 가격, 판매일을 기록하는 "sale"이란 테이블이 있다. 2001년 3월에 판매한 책의 총가격을 알고싶다. 집합 함수가 없다면 2001년 3월에 판매된 모든 행을 select해야 한다. 지금은 10 행밖에 없다면 크게 문제될 것이 없다. (물론 한달에 책을 10권 밖에 팔지못했다면 큰 돈을 벌 수 없을 것이다.) 그러나 한달에 평균 2000권을 파는 서점을 생각해보자. 행들을 하나씩 살펴보는 것이 효율적으로 보이지는 않는다.

집합 함수를 사용한다면 단순히 2001년 3월에 해당하는 책 가격 열의 sum()을 select하면 된다. 질의는 한 값만을 반환하고, 코드에서 행들을 하나씩 살펴볼 필요가 없다!

SUM() 함수

sum() 함수는 위의 예처럼 매우 유용한다. 다음과 같은 가상적인 테이블을 가정하자.

table sale (
	book_title varchar(200),
	book_price real,
	date_of_purchase datetime
)

집합 함수를 사용하지 않으면:

SELECT * FROM sale WHERE date_of_purchase BETWEEN '03/01/2001' AND '04/01/2001';

그러면 2001년 3월에 판매한 모든 열을 반환한다.

집합 함수를 사용하면:

SELECT SUM(book_price) AS total FROM sale WHERE date_of_purchase BETWEEN '03/01/2001' AND '04/01/2001';

그러면 total이라는 열로 2001년 3월에 판매한 총 금액을 담은 한 행을 반환한다.

또 sum()안에 수학 연산자를 사용할 수 있다. 예를 들어 책 가격의 20%인 이윤의 합을 알고 싶다면,

SELECT SUM(book_price) AS total, SUM(book_price * .2) AS profit FROM sale WHERE date_of_purchase BETWEEN '03/01/2001' AND '04/01/2001';

더 크게 본다면 sum() 함수의 다른 용도도 찾을 수 있을 것이다. 예를 들어 수수료를 계산하고, 자세한 보고서를 만들고, 통계적인 총합을 생성할 수 있다. 보고서를 작성한다면 수천 수만 행을 하나씩 살펴보는 것보다는 SQL이 계산하여 간단히 결과를 출력하는 것이 훨씬 더 쉽다.

count() 함수

다른 유용한 집합 함수는 count()이다. 이 함수는 조건을 만족하는 행의 갯수를 반환한다. 예를 들어 뉴스를 저장하는 데이터베이스에서 하나씩 세지않고 데이테베이스의 모든 뉴스 갯수를 알 수 있다. 다음과 같이 간단하다.

SELECT COUNT(*) AS myCount FROM news;

그러면 데이테베이스의 모든 뉴스 갯수를 반환한다.

MAX()와 MIN() 함수

이 두 함수는 지정한 열에서 최대값이나 최소값을 반환한다. (서점의 예에서) 판매한 가장 비싼 책과 가장 싼 책을 빨리 알 수 있어서 유용하다. 이 질의는 다음과 같다.

SELECT MAX(book_price) AS highestPrice, MIN(book_price) AS lowestPrice FROM sale WHERE date_of_purchase BETWEEN '03/01/2001' AND '04/01/2001';

이 경우에도 데이테베이스에서 모든 것을 select하여 하나씩 살펴보며 계산하지 않아도 된다.

AVG() 함수

이 집합 함수는 특별히 매우 중요하다. 여러 필드의 평균값을 구하고 싶을 때 avg()를 사용하면 된다. 이 집합 함수를 사용하지 않는다면 모든 행을 얻을 후, 다 더하고 행의 갯수로 나눠야한다. 서점의 예에서 2001년 3월 동안 팔린 책들의 평균값을 다음과 같이 알 수 있다.

SELECT AVG(book_price) AS avg_price FROM sale WHERE date_of_purchase BETWEEN '03/01/2001' AND '04/01/2001';

연산자는 무엇인가?

연산자는 주위 값에 연산을 한다. 예를 들어 수학 연산자를 들 수 있다. select 문에서 두 필드의 값을 빼고 싶다면, 빼기(-) 연산자를 사용하면 된다.

SELECT salesperson_name, revenue - cost AS commission FROM sales;

각 판매원이 판 금액에서 비용을 빼서 그들의 수수료를 반환한다.

salesperson_name commission

Branden Williams 234.43

Matt Springfield 87.74

연산자는 복잡한 계산을 할때나 따로 처리하는 코드없이 정확한 결과가 필요할 때 매우 유용하다.

서점의 예에서 서점의 출입구 근처에 전시하기 위해서 (한 권당) 이윤이 가장 높은 책을 찾는 프로그램을 작성하고 하자. 결과에서 하나씩 계산하지않고 적확히 원하는 결과를 얻을 수 있다.

table inventory (
	book_title varchar(200),
	book_cost real,
	selling_price real
)

SELECT book_title, selling_price - book_cost AS profit ORDER BY profit DESC;

다음과 비슷한 결과를 얻는다.

book_title profit

How To Scam Customers Into Buying Your Books 15.01

How To Crash Windows 2000 13.84

이제 어떤 책이 가장 이윤이 높은지 빨리 알 수 있다.

연산자는 한 테이블에서 다른 테이블로 정보를 옮기는데도 유용하다. 예를 들어 주 자료 테이블에 자료를 집어넣기전에 확인을 위해 자료를 임시 테이블에 먼저 집어넣기도 한다. 장바구니가 좋은 예이다. 상품 테이블에서 정보를 얻어 임시 테이블에 넣고, 취소하거나 수량을 늘리거나 할인률을 적용한 후 주문 테이블에 입력한다.

이 경우 필요한 여러 정보를 찾고 계산하여 다시 임시 테이블에 집어넣는 복잡한 과정을 거지고 싶지 않을 것이다. 연산자를 사용하여 간단히 한번의 질의로 이런 과정을 처리할 수 있다. 또한 동적인 (변경되는) 자료를 다룰때도 쉽게 처리할 수 있다. 데이터베이스가 동적인 자료를 더 잘 다룰 수 있다.

이제 몇몇 연산자와 그 기능을 살펴보자. 전체 연산자 목록을 보려면 pgsql에서 '\do'를 입력하라.

+, -, *, / 연산자

PostgreSQL에서 사용할 수 있는 기본 산술 연산자이다. 이미 위에서 이 연산자들의 예를 보았을 것이다. 다음과 같은 예도 생각해볼 수 있다.

세금 계산 (SELECT subtotal * tax AS taxamount)

개별 비용 계산 (SELECT extendedcost / quantity AS unitcost)

산술 연산자의 다른 예는 아래 연산자와 집합 함수를 결합하는 곳에서 볼 수 있다.

비교 (<, >, <=, >=) 연산자

이 연산자들은 보통 WHERE 절에서 사용한다. 예를 들어,

SELECT book_title FROM inventory WHERE selling_price >= '30.00';

판매가가 .00 이상인 책을 모두 찾는다. 이전 이윤 예제를 다음과 같이 확장할 수 있다.

SELECT book_title, selling_price - book_cost AS profit WHERE selling_price - book_cost >= '14.00' ORDER BY profit DESC;

다음과 같은 결과가 나온다.

book_title profit

How To Scam Customers Into Buying Your Books 15.01

질의에서 하한선을 사용하는 것은 보고서에서 매우 유용하다.

|| (결합) 연산자

문자를 결합할때 이 연산자가 유용하다. 예를 들어 여러 상품 종류가 있다면, 구매서에 상품명과 종류를 같이 출력할 수 있다.

SELECT category || CAST(': ' AS VARCHAR) || productname AS title FROM products;

CAST() 함수 사용을 주목하라. 결합을 위해서 대상에 대해 알아야한다. 그래서 연산자가 작동하기 위해서 PostgreSQL에게 문자열 ': '이 VARCHAR 형이라고 알려줘야한다.

결과는 다음과 같다.

title

Music CDs: Dave Matthews, Listener Supported

DVDs: Airplane

이제까지 연산자와 집합 함수의 간단한 사용법과 이를 이용하여 프로그램을 (개발과 실행) 빠르게하는 법을 보았다. 연산자와 집합 함수의 진정한 힘은 둘을 서로 결합할 때 나온다. 데이터베이스가 대신 일을 하게하여 프로그램의 코드량을 줄일 수 있다. 이제 이 방법을 다룬다.

우리의 상황:

당신은 웹기반의 상점 프로그램을 작성하는 일을 맏았다. 주문 테이블은 아래와 같다.

create table orders (
	orderid integer (autoincrement),
	customerid integer,
	subtotal real,
	tax real,
	shipping real
)

create table orderdetail (
	orderid integer,
	productid integer,
	price real,
	qty integer
)

create table taxtable (
	state varchar(2),
	rate real
)

create table products (
	productid integer,
	description varchar(100),
	price real
)

create table cart (
	sessionid varchar(30),
	productid integer,
	price real,
	qty integer
)

여기서 장바구니 정보를 세션에 저장하지않고 데이터베이스에 저장하기로 결정했다. 그러나 데이터베이스에서 변화를 기록하기위해 sessionID는 필요하다. cart 테이블은 현재 결제하지않은 장바구니를 저장한다. orders와 orderdetail은 완전한 주문과 항목을 저장한다. 돈을 청구할때 부분들을 합쳐서 각 주문의 전체합을 알 수 있다. 마지막으로 products 테이블은 가격과 설명을 저장하는 상품 테이블이다.

여기서 중요한 것은 가능한 많은 계산을 데이터베이스로 넘겨서 프로그램은 데이터베이스 질의와 작업에 필요한 코드량을 줄이는 것이다. 또 여러 항목이 데이터베이스 테이블에 저장되있기때문에 바뀔 수 있다. 이런 항목은 부분합계, 세금, 운송비 계산에 영향을 준다. 연산자와 집합 함수를 (아마도 하위질의를) 사용하지 않는다면 데이터베이스에 많은 질의를 하여 프로그램의 추가 부담을 준다. 예를 각 항목으로 나눈후 마지막에서 하나로 합칠 것이다.

부분합계 계산

비교적 간단한 계산이여서 집합 함수와 간단한 연산자만으로 얻을 수 있다.

SELECT SUM(price*qty) AS subtotal FROM cart WHERE sessionid = '9j23iundo239new';

필요한 것은 모든 price * qty의 합이다. 이 예는 연산자와 집합 함수의 결합을 잘 보여준다. SUN 집합 함수는 각 행단위의 계산의 총합을 반환한다. 이 순서를 잊지마라!

세금 계산

이 계산은 간단한 SQL로 하기 힘들다. 여기서 실제 세률을 얻기위해 COALESCE를 사용한다. COALESCE는 두 아규먼트를 받는다. 첫번째 아규먼트의 결과가 null이라면 두번째 아규먼트를 반환한다. (null이 아니라면 첫번째 아규먼트의 결과를 반환한다.) 이는 지금과 같은 경우에 매우 유용하다. 질의는 다음과 같다. 주의: _subtotal_은 현재 가상의 값이다.

SELECT _subtotal_ * COALESCE(tax, 0) AS tax FROM tax WHERE state = 'TX';

아래에서 각 부분을 합쳐 가상의 값을 제거할 것이다.

운송비 계산

간단히 각 항목당 운송비가 이라고 가정하다. 나중에 이를 쉽게 확장할 수 있다. 무게 필드를 상품 테이블에 추가하여 운송비를 계산할 수도 있을 것이다. 여기서는 단순히 장바구니 항목의 갯수에 3을 곱한다.

SELECT COUNT(*) * 3 FROM cart AS shipping WHERE sessionid = '9j23iundo239new';

모두 합치자

각 계산 결과를 따로 얻는 방법을 알았으니 이를 하나의 큰 쿼리로 합치자. 이 쿼리는 모든 계산을 하여 orders 테이블에 저장한다.

INSERT INTO orders (customerid, subtotal, tax, shipping) VALUES (customerid, (SELECT SUM(price*qty) FROM cart WHERE sessionid = '9j23iundo239new'), (SELECT SUM(price*qty) FROM cart WHERE sessionid = '9j23iundo239new') * (SELECT COALESCE(tax, 0) FROM tax WHERE state = 'TX'), (SELECT COUNT(*) * 3 FROM cart WHERE sessionid = '9j23iundo239new'));

추가로 orders 테이블에 총합 필드가 있다면 다른 쿼리로 부분항목을 모두 합치거나 INSERT 쿼리로 추가할 수 있다. 두번째 방법을 사용한다면 다음과 같다.

UPDATE orders SET grandtotal = subtotal+tax+shipping WHERE orderid = 29898;

다음 두 쿼리는 순서대로 사용하여 장바구니에 남은 항목을 orderdetail 테이블로 옮긴다.

INSERT INTO orderdetail (orderid, productid, price, qty) values SELECT _yourorderid_, productid, price, qty FROM cart WHERE sessionid = '9j23iundo239new';

DELETE FROM cart WHERE sessionid = '9j23iundo239new';

결론:

집합 함수는 SQL 서버가 여러 계산을 처리하여 프로그램을 단순하게하고 속도를 빠르게 한다. 더 복잡한 프로그램에서도 보고서 등의 목적으로 여러 테이블에서 원하는 결과를 얻는데 사용할 수 있다. 연산자는 테이터베이스가 반환하는 결과의 질을 높인다. 연산자와 집합 함수를 잘 사용하면 프로그램의 속도와 정확성을 높이는 것은 물론이고, 결과를 반복하며 계산하는 불필요한 코드를 줄여준다.

이 글을 쓰면서 즐긴만큼 당신도 즐겁게 이 글을 읽고 배웠길 바란다!

Branden R Williams

Branden은 현재 그가 올해 공동선립한 e-비지니스와 보안 컨설팅 회사인 Elliptix의 컨설턴트이다. 그는 인터넷관련 기술, 유닉스 관리, 네트웍 디자인/구성, 많은 프로그램 언어에 10년이상 경험이 있다. 최근 6년 동안 기업수준의 전자상거래 프로그램을 설계/개발했다. 그는 Arlongton에 있는 Texas대에서 마케팅을 전공했다. 그는 brw@brw.net로 연락할 수 있다.