[Sqlite] 5. SQLite User Guide - Function (내장함수, Aggregation 함수)

SQLite Databae에 대한 다섯번째 포스트입니다. 이번 포스트에서는 SQLite의 Core 함수에 대해서 다뤄보도록 하겠습니다.

SQLite User Guide 포스트 목차

1. SQLite User Guide - 소개. GUI Tools 등
2. SQLite User Guide - PRAGMA, 시스템 카탈로그, DATA TYPE
3. SQLite User Guide - DDL(CREATE, DROP 등)
4. SQLite User Guide - DML (SELECT, INSERT, UPDATE, DELETE 등)
5. SQLite User Guide - Function (내장함수, Aggregation 함수)
6. SQLite User Guide - DateTime 함수, DateTime Formatting
7. SQLite User Guide - Trigger
8. SQLIte User Guide - Tranaction, Lock
9. SQLite User Guide - Performance, Optimizer
10. SQLite User Guide - VDBE(Virtual Database Engine)

1.Core Function

coalesce(X,Y,...)	coalesce() 함수는 Argument 중에서 첫번째로 Not Null인 Argument값을 리턴하는 함수입니다. 만일 모든 인자가 null이면 null을 리턴합니다.
ifnull(X,Y)	ifnull() 함수는 두 인자중에서 첫번째로 Not Null인 인자값을 리턴합니다. 만일, 둘다 null이면 null을 리턴합니다. Ifnull() 함수는 인자가 두개인 coalesce() 함수와 동일합니다.
length(X)	길이값을 리턴하는 함수입니다. 만일 X 인자가 null이면 null을 리턴합니다.
like(X,Y)  like(X,Y,Z)	Like 함수는 “Y LIKE X [ESCAPE Z]”구문과 동일합니다.
lower(x)	소문자로 변환 합니다.
upper(X)	대문자로 변환 합니다.
ltrimX)  ltrim(X,Y)	ltrim(X)는 X 값 중 왼쪽편의 공백을 제거하는 함수입니다. ltrim(X,Y)는 X 문자열중에서 Y에 나타난 값을 제일 좌측부터 제거하는 함수입니다.   select ltrim(" ZZZZabcZZ ", " aZ") => 좌측문자열에서 부터 공백,a,Z 문자열이 있으면 제거하고, 처음으로 공백,a,Z가 아닌 문자열부터 출력합니다. 즉, “bcZZ”가 출력됩니다. ltrim(" ZZZZabcZZ ", " ") 는 ltrim(" ZZZZabcZZ ")과 동일합니다.
rtrim(X)  rtrim(X,Y)	rtrim(X)는 우측편 공백 제거 rtrim(X,Y)는 ltrim(X,Y)와 동일한 방식이지만 우측편부터 매칭되는 글자를 제거합니다. 예를들어 select rtrim(" ZZZZabcZZ ", " Z") 문장은 우측편부터 공백과 Z를 빼고 처음으로 공백과 Z가 아닌 글자, 즉 c 까지 글자가 나타납니다. 결과값: “ ZZZZabc”
trim(X)  trim(X,Y)	trim(X)는 양쪽 공백 제거 trim(X,Y) 는 Y에 해당되는 글자를 양쪽 끝에서 부터 제거하고 나머지 글자만 리턴함. 예) trim(" ZZZZabcZZ ", " Z") => “abc” trim은 ltrim과 rtrim을 각각 적용한것과 동일한 결과가 나타납니다.
max(X,Y,..)	인자값들 중 최대값을 리턴합니다. create table t1 (coll integer, col2 integer, col3 integer); insert into t1 values(1,2,3); insert into t1 values(5,3,1); select max(col1, col2, col3) from t1;
min(X,Y,...)	인자값들 중 최소값을 리턴합니다.
nullif(X,Y)	두 인자가 서로 같으면 null을 리턴, 서로 다르면 X값을 리턴합니다. nullif('x','y') => ‘x’ , nullif('x','x') => null 리턴
quote(X)	Quote()함수는 single quotation을 escape 해줍니다. ‘값을 ‘’ 로 변경합니다. Insert나 update 시에 사용하면 유용할 듯 합니다. select quote("girl's mouse") => 'girl''s mouse'
random(*)	-9223372036854775808 부터 +9223372036854775807 숫자 사이의 임의의 수를 리턴합니다.
randomblob(N)	N으로 지정된 bytes의 랜덤 바이너리 데이터를 생성합니다.
hex(X)	바이너리 값을 hex 값으로 변경합니다. select hex(randomblob(16))
replace(X,Y,Z)	X 문자열 중에서 Y문자열을 Z로 변경합니다. select replace('1/12/2009','1','x') => "x/x2/2009"
round(X)  round(X,Y)	반올림 함수. Y는 소수점 자리. Y가 없으면 0으로 처리합니다. Round(3.5) => 4, round(2.555, 2) => 2.56
substr(X,Y)  substr(X,Y,Z)	substr()함수는 X문자열 중에서 Y번째부터 시작해서 Z개수만큼 문자열을 가져오는 함수입니다. Z가 생략되면 Y번째 문자열부터 문자열 끝까지 리턴합니다. Y의 최소값은 1입니다. 자바에서는 0으로 시작하지만 SQLite에서는 1부터 인덱스가 시작합니다. 만일 Y가 –(마이너스)값이면 문자열 우측끝부터 카운팅을 시작합니다. select substr("string", 1, 3) => str select substr("string", 0, 3) => st select substr("string", -1, 3) => g select substr("string", -3, 3) => ing select substr("string", 2) => tring
typeof(X)	X 표현식에 대한 데이터 타입을 리턴합니다. 리턴값은 “null”, “integer”, “real”, “text”, “blob” 중의 하나입니다.

2. Aggregation Function (집합 함수)

avg(X)	그룹내의 Not Null값의 평균값을 리턴합니다. X 컬럼값이 문자열이나 BLOB이면 0으로 간주하고 처리합니다.
count(*) count(X)	count(X)는 X가 Not Null값을 가진 레코드의 개수 리턴합니다. count(*)는 그룹내의 모든 rows의 수를 리턴합니다.
group_concat(X) group_concat(X,Y)	X가 not null인 경우 그룹내의 모든 문자열을 콤마(,)를 구분자로 해서 문자열을 합쳐서 리턴합니다. Y가 주어지면 Y값이 구분자로 처리됩니다.
max(X)	그룹내의 값들중 최대값을 리턴합니다.
min(X)	그룹내의 값들중 최소값을 리턴합니다. 모든값이 Null이면 Null을 리턴합니다.
sum(X) total(X)	Sum과 total은 그룹내의 Not Null값의 합계를 리턴합니다. X의 모든 값이 Null인 경우 Sum()은 Null을 리턴하고 Total()은 0.0을 리턴합니다.

/* Aggregation Function Test */

create table t1 (
type integer,
id integer,
name varchar(10),
value real,
data text
);

insert into t1 values(1,1,'a',0.0,'1');
insert into t1 values(1,2,'b',3.5,NULL);
insert into t1 values(1,3,NULL,3.1,'2');
insert into t1 values(1,4,'c',3.5,'10');
insert into t1 values(1,5,'d',NULL,NULL);

-- count(*)와 count(X)의 차이 비교
select count(*), count(name), count(data)
from t1
group by type

-- result
count(*) count(name) count(data)
5 4 3

-- group_concat test query
select group_concat(id,' > ') as exp
from t1;

-- result
exp
1 > 2 > 3 > 4 > 5

select group_concat(data,' > ') exp
from t1
group by type

-- result
exp
1 > 2 > 10

/* sum(), tatal() function test */
select sum(data), total(data)
from t1
where id in (2,5)
group by type;

-- result
sum(data) total(data)
<null>    0

생각보다 SQLite에서 지원되는 내장 함수가 너무 적습니다. SQLite는 각 언어별로 확장 기능을 가지고 있긴 하지만, 쉽게 사용하는게 아니라서.. 한번의 SQL로 처리 가능한 것을 상당 부분 프로그램 코드에서 처리해야 하지 않을까 싶습니다. 그럼에도 불구하고 group_concat() 같은 기능의 함수는 Oracle에서도 11g가 되어서야 내장 함수로 제공할 정도로 대부분의 DB에서 제공하지 않는 함수인데.. SQLite에서 구현이 되어 있다는 게 놀라울 따름입니다.

이것으로 SQLite Function 부분을 마치며, 다음 포스트에서는 DateTime 및 DateTime Formatting에 대해서 알아보도록 하겠습니다.