[SQLite] 내장 함수 - 1

SQLite3는 기본적으로 문자열 조작, 수치 계산, 집계, 날짜/시간 계산, 시스템 함수 등 다양한 내장 함수를 제공한다.

 

<문자열 조작 함수>

 

함수	내용
lower(x)	문자열 x를 소문자로 변경
upper(x)	문자열 x를 대문자로 변경
length(x)	문자열 x의 길이를 반환
trim(x, y)	문자열 x의 양 끝에서 y를 제거한 결과를 반환
ltrim(x, y)	문자열 x의 왼쪽 끝에서 y를 제거한 결과를 반환
rtrim(x, y)	문자열 x의 오른쪽 끝에서 y를 제거한 결과를 반환
substr(x, y, z)	문자열 x의 y번째 위치로 부터 z개 만큼의 부분 문자열을 반환
replace(x, y, z)	문자열 x 중에서 y와 일치하는 문자열을 z로 교체

 

- lower, upper 함수의 경우 아스키(ASCII) 형태의 문자열만 변환 가능하다.

  만약 아스키 문자가 아닌 경우에는 ICU 확장 라이브러리와 같은 외부 모듈을 이용하여 변환 가능하다.

 

sqlite> SELECT upper(name), lower(brand) from cars;   upper(name)  lower(brand) -----------  ------------ SONATA       hyundai      K5           kia          320D         bmw          520D         bmw          C220         benz         E330         benz

 

- length 함수의 경우 저장된 공백 문자를 모두 포함한 총 문자열의 글자수를 반환한다. 만약 해당 값이 NULL인 경우에는 NULL을 반환한다. 또한 BLOB의 경우는 저장된 바이트(Byte)의 크기를 반환한다.

 

sqlite> SELECT length(name), name, brand FROM cars WHERE brand='BMW';   length(name)  name        brand      ------------  ----------  ---------- 4             320d        BMW        4             520d        BMW

 

- trim은 양 끝에서, ltrim은 왼쪽, rtrim은 오른쪽 끝에서 y문자열을 x문자열에서 제거한 결과를 반환한다. y는 생략 가능한 데 이 경우 공백 문자를 제거한다.

 

sqlite> SELECT name, trim(name, 'a'), ltrim(name, 'C'), rtrim(name, 'd') FROM cars;   name        trim(name, 'a')  ltrim(name, 'C')  rtrim(name, 'd') ----------  ---------------  ----------------  ---------------- sonata      sonat            sonata            sonata           K5          K5               K5                K5               320d        320d             320d              320              520d        520d             520d              520              C220        C220             220               C220             E330        E330             E330              E330             Morning     Morning          Morning           Morning          K3          K3               K3                K3               Morning     Morning          Morning           Morning

 

- substr(x, y, z) 함수는 문자열 x의 y번째 위치부터 z 길이 만큼의 부분 문자열을 반환한다. 만약 z를 생략할 경우 y번째 위치부터 문자열 끝까지의 모든 문자열을 반환한다. BLOB인 경우에는 해당 바이트 크기 만큼 시작점을 이동한다. 또한 y가 음수 인 경우 x의 앞쪽이 아닌 뒤쪽에서 부터 y번째의 위치가 시작점이 된다.

 

sqlite> SELECT name, substr(name, 1, 3), substr(name, 2), brand FROM cars;   name        substr(name, 1, 3)  substr(name, 2)  brand      ----------  ------------------  ---------------  ---------- sonata      son                 onata            Hyundai    K5          K5                  5                Kia        320d        320                 20d              BMW        520d        520                 20d              BMW        C220        C22                 220              Benz       E330        E33                 330              Benz

 

- replace(x, y, z) 문자열 x 중에서 y와 일치하는 문자열을 z로 교체한다.

 

sqlite> SELECT name, replace(name, 'd', 'i'), brand FROM cars WHERE brand='BMW';    name        replace(name, 'd', 'i')  brand      ----------  -----------------------  ---------- 320d        320i                     BMW        520d        520i                     BMW

 

<수치 계산 함수>

 

함수	내용
max()	인자 값 중 최대 값을 반환
min()	인자 값 중 최소 값을 반환
abs(x)	x의 절대값을 반환
round(x, y)	인자 x를 소수점 y 자리에서 반올림한 결과를 반환
random()	임의의 정수값을 반환
randomblob(n)	n 바이트 크기의 임의의 BLOB 데이터를 반환
hex(x)	x를 16진수 형태로 반환

 

• max()는 최대, min()은 최소값을 반환한다. 전달 인자로는 조회할 컬럼의 이름을 전달한다. 아래의 예를 보면 더욱 쉽게 다가 올 수 있을 것이다.

 

sqlite> SELECT * from cars;   name        price       brand      ----------  ----------  ---------- sonata      1000000     Hyundai    K5          1000000     Kia        320d        1500000     BMW        520d        1700000     BMW        C220        1500000     Benz       E330        1800000     Benz        sqlite> SELECT max(price), min(price) FROM cars;   max(price)  min(price) ----------  ---------- 1800000     1000000

 

이 두 함수는 정수나 실수 값 뿐만 아니라 문자열에 대해서도 비교할 수 있는데 별도의 문자열 정렬 순서를 설정하지 않은 경우에 알파벳 순서로 그 크기를 비교한다

 

sqlite> SELECT max(name), min(name) FROM cars;   max(name)   min(name)  ----------  ---------- sonata      320d

 

그리고 두 함수의 전달 인자로 2개 이상을 설정할 수 있는 이 경우에는 각 행마다 비교를 수행하여 최대값과 최소값을 반환한다.

 

sqlite> SELECT max(name, brand), min(name, brand) FROM cars;   max(name, brand)  min(name, brand) ----------------  ---------------- sonata            Hyundai          Kia               K5               BMW               320d             BMW               520d             C220              Benz             E330              Benz

 

• abs(x) 함수는 인자 x의 절대값을 반환하는데 x의 값이 NULL인 경우에는 NULL, 숫자가 아닌 경우에는 0.0을 반환한다.

 

sqlite> SELECT name, price, abs(price), brand FROM cars;   name        price       abs(price)  brand      ----------  ----------  ----------  ---------- sonata      1000000     1000000     Hyundai    K5          1000000     1000000     Kia        320d        1500000     1500000     BMW        520d        1700000     1700000     BMW        C220        1500000     1500000     Benz       E330        1800000     1800000     Benz       Morning     -500000     500000      Kia        K3          NULL        NULL        Kia

 

• round(x, y) 인자 x값을 소수점 y 자리에서 반올림한 값을 반환하는데 y는 생략 가능하다.

y가 생략된 경우 소수점 첫째 자리에서 반올림하여 결과값을 반환한다.
 

sqlite> SELECT name, price, round(price, 2), brand FROM cars;   name        price       round(price, 2)  brand      ----------  ----------  ---------------  ---------- sonata      1000000     1000000.0        Hyundai    K5          1000000     1000000.0        Kia        320d        1500000     1500000.0        BMW        520d        1700000     1700000.0        BMW        C220        1500000     1500000.0        Benz       E330        1800000     1800000.0        Benz       Morning     500000.356  500000.36        Kia        K3          NULL        NULL             Kia

 

• random() 함수는 임의의 정수를 반환하며 randomblob(n)은 n 바이트의 임의 BLOB 데이터를 반환한다.

 

sqlite> SELECT name, random() FROM cars;   name        random()             ----------  -------------------- sonata      -8709290756250348677 K5          7016036238490827880  320d        466711023326405224   520d        6909756085219589672  C220        -3870929680675256698 E330        3258670515048649146  Morning     4961823573489217915  K3          -866775809411128059

 

• hex(x) 함수는 인자 x를 16진수 형태로 반환한다.

 

sqlite> SELECT name, price, hex(price), brand FROM cars;   name        price       hex(price)      brand      ----------  ----------  --------------  ---------- sonata      1000000     31303030303030  Hyundai    K5          1000000     31303030303030  Kia        320d        1500000     31353030303030  BMW        520d        1700000     31373030303030  BMW        C220        1500000     31353030303030  Benz       E330        1800000     31383030303030  Benz       Morning     500000.356  3530303030302E  Kia        K3          NULL                        Kia

 

<집계 함수>

 

함수	내용
avg()	평균값을 반환
sum(), total()	컬럼의 합계를 반환
count()	컬럼의 개수를 반환
group_concat()	컬럼의 문자열을 붙여서 하나의 문자열로 반환

 

* AS: AS 키워드를 사용하여 결과 집합에 별칭을 부여해 줄 수 있다.

 

sqlite> SELECT max(price) AS MAX_PRICE, min(price) AS MIN_PRICE FROM cars;   MAX_PRICE   MIN_PRICE  ----------  ---------- 1800000     500000.356

 

• avg(column) 함수는 해당 column값들의 평균 값을 반환한다.

 

sqlite> SELECT avg(price) AS Average_Price FROM cars;   Average_Price    ---------------- 1285714.33657143

 

• sum(column)과 total(column)은 모두 해당 column의 합계를 반환하지만 몇 가지 차이점을 가지고 있다.

먼저 sum함수는 SQL 표준 함수이며 total 함수는 SQLite3에서 만든 별도의 함수이다.

total 함수의 경우 data type에 상관없이 항상 부동 소수점 형태로 값을 반환하지만 sum함수의 경우는 data type이 INTEGER의 경우에는 정수 형태로 반환하고, 그렇지 않은 경우에는 부동 소수점 형태로 값을 반환한다

 

sqlite> SELECT total(price), sum(price) FROM cars WHERE brand="Benz";   total(price)  sum(price) ------------  ---------- 3300000.0     3300000

 

그리고 모든 Data가 NULL인 경우 sum 함수는 NULL값을, total 함수는 0.0을 반환한다.

 

sqlite> SELECT total(price), sum(price) FROM cars WHERE name = "K3";   total(price)  sum(price) ------------  ---------- 0.0           NULL

 

• count() 함수는 기본적으로는 해당 Column의 개수를 반환하지만 함수의 인자에 따라 동작 방식이 다르다.

 

count(*): Table의 Record 개수를 반환

count(column): 해당 column의 개수를 반환(Data 값이 NULL인 경우 제외)

 

name        price       brand      ----------  ----------  ---------- sonata      1000000     Hyundai    K5          1000000     Kia        320d        1500000     BMW        520d        1700000     BMW        C220        1500000     Benz       E330        1800000     Benz       Morning     500000.356  Kia        K3          NULL        Kia        Morning     500000      Kia         sqlite> SELECT count(*), count(price) FROM cars WHERE brand = 'Kia';   count(*)    count(price) ----------  ------------ 4           3

 

• group_concat() 함수는 해당 Column의 모든 문자열을 붙여서 하나의 값으로 반환한다.

첫 번째 인자에는 column명을 두 번째 인자에는 문자열을 연결할 구분자를 입력한다.

만약 두 번째 인자를 생략하는 경우 자동으로 ','가 구분자가 되어 문자열을 연결한다.

 

sqlite> SELECT group_concat(name, '/'), group_concat(name) FROM cars;   group_concat(name, '/')                   group_concat(name)                       ----------------------------------------  ---------------------------------------- sonata/K5/320d/520d/C220/E330/Morning/K3  sonata,K5,320d,520d,C220,E330,Morning,K3

 

* 집계 함수를 사용하여 연산 시 중복된 행을 제거한 결과 값을 얻고 싶은 경우 DISTINCT를 사용해 주면 된다

 

sqlite> SELECT count(name) AS COUNT, group_concat(name) AS CONCAT FROM cars;    COUNT       CONCAT                                           ----------  ------------------------------------------------ 9           sonata,K5,320d,520d,C220,E330,Morning,K3,Morning     sqlite> SELECT count(DISTINCT name) AS DISTINCT_COUNT, group_concat(DISTINCT name) AS DISTINCT_CONCAT FROM cars;    DISTINCT_COUNT  DISTINCT_CONCAT                          --------------  ---------------------------------------- 8               sonata,K5,320d,520d,C220,E330,Morning,K3