SLECET문 - 집계함수 및 문자,숫자,날짜 관련 함수

1. 집계(집약) 함수란?

SQL 집계(집약) 함수는 데이터베이스에서 데이터를 집계하거나 요약하는데 사용되는 함수이다. 이러한 함수들은 SELECT 문에서 사용되며, 데이터를 그룹화하고 집계하여 결과를 반환합니다.

 

1) 집계(집약) 함수 목록

함수명	인수	반환값
COUNT	* 또는 컬럼명	레코드 또는 컬럼 수
SUM	컬럼명	컬럼의 합계값
MAX	컬럼명	컬럼의 최댓값 문자열은 사전순으로 최대 날짜는 최신순
MIN	컬럼명	컬럼의 최솟값 문자열은 사전순으로 최소 날짜는 오래된 순
AVG	컬럼명	컬럼의 평균값
STDENV	컬럼명	컬럼의 표준편차
VARIANCE	컬럼명	컬럼의 분산값

이러한 함수들은 주로 GROUP BY절과 함께 사용된다. GROUP BY 절은 데이터를 그룹화하는 데 사용되며, 집계 함수는 각 그룹에 대한 결과를 반환한다.

2) 집약 함수 규칙

(1) 집약 함수를 사용할 수 있는 곳이 정해져 있다. (SELECT구, HAVING구, ORDER BY구에서 사용 가능)
*SELECT 구 FROM 구 WHERE 구 GROUP BY 구 HAVING 구 ORDER BY 구

(2) 1개 값을 반환하는 것만 취급할 수 있다.
(3) NULL을 조심해야 한다.

*집계하는 컬럼의 안에 NULL이 있는 경우 COUNT(*) 이외는 전부 NULL 값을 무시하고 집계한다.

TIP> SELECT 구에서 상수, 집약 함수, DISTINCT, 연산자 등을 같이 사용할 수 있다. 예를 들어, COUNT 함수를 이용하되, 중복된 값을 제외하여 카운트하고 싶은 경우 아래와 같이 구문을 작성할 수 있다.

SELECT COUNT (*) AS cnt FROM stud_score;
# 조회 결과: NULL값을 포함한 모든 행 카운트한 값 출력

SELECT COUNT(music_score) AS music_cnt FROM stud_score;
# 조회 결과: music_score를 보유하고 있는 행 카운트한 값 출력 (NULL값 제외)

SELECT COUNT(eng_score) as eng_cnt FROM stud_score;
# 조회 결과: eng_score를 보유하고 있는 행 중 NULL값과 중복된 값 제외하여 카운트한 값 출력

2. 데이터 그룹화

GROUP BY절을 이용하여 데이터를 그룹화할 수 있다. SELECT문과 함께 사용되며, SELECT 문에서 선택된 열을 기준으로 데이터를 그룹화한다. GROUP BY절은 결과 집합을 집계하기 전에 데이터를 그룹화하여 요약된 데이터를 반환하기 위해 사용한다.

1) 기본 문법

**# GROUP BY 기본 문법**
SELECT   -- 가져온다.
컬럼명   -- 무엇을
FROM
테이블명 -- 어디에서
GROUP BY
컬럼명;  -- 그룹마다

# 예제
SELECT pref, age, count(*) from inquiry group by pref;
# Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'tenneeds.inquiry.age' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
# 원인: 그룹화했을 때 결과가 여러행이 될 수 없음.

SELECT pref, age, count(*) from inquiry group by pref, age;
# 결과: 정상 조회
# pref   ㅣ age ㅣ count(*)
# 경기도 ㅣ 40  ㅣ 1
# 서울시 ㅣ 20  ㅣ 2
# 서울시 ㅣ 30  ㅣ 1
# 충청도 ㅣ 20  ㅣ 1
# 충청도 ㅣ 30  ㅣ 1
# 해석: **집약 키가 여러개**인 경우, **지정한 집약 키 중 먼저 적은 집약 키부터 차례대로 그룹화**한다.

**# GROUP BY와 WHERE구문**
select pref, avg(star) from inquiry where star >= 3 group by pref;
# 해석: **WHERE 구가 있는** 경우, 먼저 **WHERE 구에서 레코드를 축소한 후 GROUP BY로 그룹화 시행**함

2) 그룹에 조건 설정

**# 그룹에 조건 주는 방법: HAVING 구문**
select				-- 가져온다
pref, count(*) 		-- 무엇을
from
inquiry 			-- 어디에서
group by
pref 				-- 그룹마다
having
count(*) >= 2; 		-- 조건을 줘서

**# WHERE구문과 HAVING 구문 차이**
select pref from inquiry where pref != '서울시' group by pref;
select pref from inquiry group by pref having pref != '서울시';
# 해석1: 결과는 동일함
# 해석2: SELECT문은 구마다 정해신 순서대로 실행됨
# 해석3: 데이터가 많을 경우, 처리 속도 차이가 발생될 수 있음
# 해석4: 때문에 where구에서 먼저 레코드 수를 축소화하는 것이 처리 속도 측면에서 더 효율적

3) 집계함수와 GROUP BY 사용 예시
[ 테이블 ]

이름	나이	성별
강호동	24	여성
유재석	32	남성
전현무	28	여성
송지효	35	남성
권나라	29	여성

*위의 table을 SQL 문을 이용하여 데이터를 그룹화하고 COUNT(), AVG(), MAX() 함수를 사용하여 반환된 결과를 조회할 수 있다.

 

[ SQL 코드 ]

sqlCopy code
SELECT 성별, COUNT(*), AVG(나이), MAX(나이)
FROM 데이터
GROUP BY 성별;

 

[ 조회 결과 ]

성별	COUNT(*)	AVG(나이)	MAX(나이)
여성	3	27.0	29
남성	2	33.5	35

결과 해석: GROUP BY 절은 성별에 따라 데이터를 그룹화하고, COUNT(*) 함수를 사용하여 각 그룹에 대한 데이터의 수를 계산한다. 그리고 AVG함수는 평균값을 MAX함수는 최댓값을 반환한다.

3. 문자, 숫자, 날짜 관련 함수

1) 문자 관련 함수

(1) LENGTH(문자열)

문자열의 길이를 반환한다.

 

(2) CONCAT(문자열1, 문자열2)

문자열1과 문자열2를 결합하여 새로운 문자열을 반환한다.

 

(3) SUBSTR(문자열, 시작위치, 길이)

문자열에서 지정한 위치부터 길이만큼의 문자열을 반환한다.

 

(4) UPPER(문자열)

문자열을 대문자로 변환하여 반환한다.

 

(5) LOWER(문자열)

문자열을 소문자로 변환하여 반환한다.

 

(6) INITCAP(문자열)

첫 글자는 대문자 나머지는 소문자로 변환하여 반환한다.

 

(7) TRIM(문자열)

문자열의 양쪽 끝에서 공백을 제거한 문자열을 반환한다.

# LOWER
SELECT id, name, LOWER(name) as name_cap
FROM vendor_info;
# 조회 결과: id, name, name_cap(name 소문자) 출력

# UPPER
SELECT id, name, UPPER(name) as name_cap
FROM vendor_info;
# 조회 결과: id, name, name_cap(name 대문자) 출력

# LENGTH
SELECT id, name, LENGTH(name) as name_cnt
FROM vendor_info;
# 조회 결과: id, name, name_cnt(이름 길이) 출력

# SUBSTR
SELECT id, name, SUBSTR(name,2,3) as name_cnt
FROM vendor_info;
# 조회 결과: id, name, name_cnt(이름 2번째 글자부터 3자리수까지 문자열) 출력

2) 숫자 관련 함수

(1) ROUND(숫자, 소수점자리수)

숫자를 지정한 소수점 자리수로 반올림하여 반환한다.

 

(2) FLOOR(숫자)

숫자보다 작거나 같은 가장 큰 정수를 반환한다.

 

(3) TRUNC(숫자, 자릿수)

지정한 자릿수까지만 반환한다.

 

(4) CEILING(숫자)

숫자보다 크거나 같은 가장 작은 정수를 반환한다.

 

(5) ABS(숫자)

숫자의 절댓값을 반환한다.

 

(6) MOD(나누는수, 나누는수)

나눗셈의 나머지를 반환한다.

 

(7) SIGN(숫자)

숫자의 부호를 반환한다. (양수인경우 1, 음수인 경우 -1, 0인 경우 0)

# ROUND 
SELECT prod_id, total_sales, ROUND(total_sales, 0) as sales_rev
FROM prod_sales;
# 조회 결과: prod_id, total_sales, sales_rev(total_sales 소수점 첫째자리에서 반올림한 데이터) 출력

SELECT prod_id, total_sales, ROUND(total_sales, -1) as sales_rev
FROM prod_sales;
# 조회 결과: prod_id, total_sales, sales_rev(total_sales 정수 첫째자리에서 반올림한 데이터) 출력

# MOD
SELECT prod_id, total_sales, sales_num, MOD(total_sales, sales_num) as sales_balance
FROM prod_sales;
# 조회 결과: prod_id, total_sales, sales_num, sales_balance(total_sales를 sales_num으로 나눈 값) 출력

# ABS
SELECT prod_id, econ_income, ABS(econ_income) AS PRFT
FROM prod_sales;
# 조회 결과: prod_id, econ_income, PRFT(econ_income의 절대값) 출력

3) 날짜 관련 함수

(1) NOW()

현재 날짜와 시간을 반환한다.

 

(2) DATE(날짜)

날짜에서 시간을 제외한 날짜 부분만 반환한다.

 

(3) DAY(날짜), MONTH(날짜), YEAR(날짜)

날짜의 일, 월, 년도 부분을 반환한다.

 

(4) DATEDIFF(날짜1, 날짜2)

두 날짜 사이의 차이를 일 단위로 반환한다.

 

(5) ADD_MONTHS(날짜, 개월 수)

날짜에 개월 수를 더한 값을 반환한다.

 

(6) SYSDATE

현재 시스템의 날짜 데이터를 반환한다.

 

(7) LAST_DAY

해당 월의 마지막 날짜를 반환한다.

# ADD_MONTHS
SELECT id, birth_dt, ADD_MONTHS(birth_dt, 1) as birth_p1
FROM clerk;
# 조회 결과: id, birth_dt, birth_p1(birth_dt에 1개월 더한 값) 출력