DBMS 기초(4)- 계수함수와 정규화, 그룹화

*이 글을 읽기전에 작성자 개인의견이 있으니, 다른 블로그와 교차로 읽는것을 권장합니다.*

들어가기 앞서, sql 쿼리문이 어떤 순서로 실행되는지 알면 쿼리를 설계할 때 도움이 됩니다. SQL쿼리순서는 다음과 같습니다.

• SQL 문법 순서
  • SELECT
  • FROM
  • JOIN
  • WHERE
  • GROUP BY
  • HAVING
  • ORDER BY

 

1. 계수함수

SQL에서 집계함수는 데이터베이스에서 데이터를 요약하고 분석하는데 사용합니다.  GROUP BY와 함께 사용하여 그룹화된 데이터에 대해 통계 정보를 계산하거나 데이터의 특성을 파악하는데 사용합니다. 주로 사용하는 집계함수는 다음과 같습니다. 함수는 주로 column_이름 의 밖에 감싸지는 형태로 사용합니다.

사용형태: select 함수(column_이름) from table_이름;

• count()
• sum()
• avg()
• min()
• max()

1-1. count()

행(row)의 갯수를 세는 함수입니다.

# count(): 로우의 갯수를 세는 함수
select * from member;
select count(*) from member;

6개의 행을 가졌으므로, 숫자 6이 출력됩니다. (*는 전체를 의미)

# 전체 데이터의 갯수를 출력하려면 null이 없는 필드를 선택 -> primary key를 선택하는 것을 추천
select count(userid) from member;
select count(zipcode) from member; #null은 세지 않음
select count(userid) as totalcount from member;

첫줄은 userid열의 행갯수를 출력, 둘째줄은 zipcode열의 행 갯수를 출력, 셋째줄은 userid열의 행 갯수를 출력

1-2. sum()

행의 값을 더합니다.

# sum(): 로우의 값을  더함
select sum(point) as 'totalpoint' from member;

point열의 합계를 구합니다. 집계함수는 column_이름 과 함께 사용해야 합니다. as 별명에 붙여 사용할 수 없습니다.

에러

1-3. avg()

행의 평균을 구합니다.

# avg(): 로우의 평균을 구함
select avg(point) as 'avg' from member;

point열의 평균을 구합니다.

1-4. min()

행의 최솟값을 구합니다.

# min(): 로우의 최솟값을 구함
select min(point) as 'min' from member;

point열의 최솟값을 구합니다.

1-5. max()

행의 최댓값을 구합니다.

# max(): 로우의 최댓값을 구함
select max(point) as 'max' from member;

point열의 최댓값을 구합니다.

# 포인트가 100을 초과하는 member 중에서 남자, 여자 그룹으로 나눠 포인트의 평균을 구하고
# 평균 포인트가 150이상인 성별에 대해 출력(단, 포인트가 많은 성별을 우선으로 출력)

select gender, avg(point) as avg from member where point >=100 
group by gender having avg> 150 order by avg desc;

 

2. 정규화

2-1. 데이터 정규화와 데이터베이스의 정규화

데이터 정규화란? 데이터베이스를 설계할 때 중복을 최소화하여 데이터의 분포를 일정한 범위나 척도로 조절하는 과정을 말합니다. 데이터의 크기, 단위 등이 다를 경우 발생하는 문제를 해결하고, 모델의 성능을 향상시키는 등 데이터 분석과 모델 적용에 도움이 됩니다. 

그러나 데이터베이스의 정규화는 데이터 정규화와 다른 개념입니다. 데이터베이스 설계에서 관계형 데이터베이스의 테이블을 정규화하는 과정을 의미합니다. 각 단계마다 중복을 최소화하고 데이터의 무결성과 일관성을 보장하기 위해 수행합니다.

• 1NF(1정규화, First Normal Form) : 하나의 셀(열)에는 하나의 값(value)만 있어야 하며, 중복되는 그룹은 분해되어야 합니다.
• 2NF(2정규화, Second Normal Form) : 1정규화를 충족하면서 부분 함수 종속성 제거합니다. 테이블의 모든 열들은 기본키(Primary Key)에 종속되어야 합니다.
• 3NF(3정규화, Thrid Normal Form) : 2정규화를 충족하면서 이행적 종속성을 제거합니다. 비주요 특성 간의 종속성을 제거하여 데이터의 중복을 최소화 합니다.
• 비정규화(Denormalization) : 데이터베이스의 정규화 원칙(중복X)을 우회(위배)하여, 테이블 간의 관계를 허용하는 것입니다. 테이블을 조회하는 용도로 사용하거나 데이터가 너무 많이 나뉘어 메모리 성능이 저하된다면 조회 성능 최적화를 위해 사용합니다. 

#테이블생성
create table profile (
	userid varchar(20) not null,
    height double,
    weight double,
    mbti varchar(10),
    foreign key(userid) references member(userid)
);

insert into profile values('apple',160,50,'ISTP');
select * from profile;
insert into profile values ('grapes',170,70,'ESTP'); #member에 없는 데이터
insert into profile values('avocado',180,80,'INFP');
insert into profile values('orange',170,70,'ENFP');

member table에 없는 value값을 추가합니다.

2-2. join

두 개 이상의 테이블을 결합하여 새로운 테이블을 생성하는 연산입니다. 또한, 정규화를 통해 테이블을 분리할 때, join을 사용하여 필요한 정보를 다시 결합할 수 있습니다.

사용형태: select table_이름.column_이름 from table_이름1 [inner, left, right, full] join table_이름2 on table_이름1.column_이름 = table_이름2.column_이름

2-2(1). inner join

두 테이블간의 교집합을 반환합니다. 공통 된 열에 맞는 행만 반환됩니다.

member table

profile table

# inner 조인
select m.userid, name, gender, mbti from member as m inner join profile as p 
on m.userid = p.userid;

meber table을 m, profile table을 p로 alias시키며, m.userid = p.userid는 inner join 으로 교집합처럼 연결되며, primary key를 userid를 기준으로 name, gender mbti 열의 교집합 value값만 출력합니다.

2-2(2). left, right join

# 두 테이블이 조인될 때 왼쪽 또는 오른쪽을 기준으로 테이블의 데이터를 모두 출력
select m.userid, name, gender, mbti from member as m left join profile as p 
on m.userid = p.userid;

select m.userid, name, gender, mbti from member as m right join profile as p 
on m.userid = p.userid;

member table을 m, profile table을 p로 alias시키며, left join으로 왼쪽 table인 member table을 기준으로 userid, name, gender열을 출력합니다.

반대로 right join일 경우, 오른쪽 table인 profile table을 기준으로 userid열에 대한 name, gender, mbti열을 선택합니다.

2-2(3). full join

SELECT *
FROM table1
FULL JOIN table2 ON table1.id = table2.id;

다만, MySQL에서는 full join을 지원하지 않습니다.

3. 그룹화

Group by와 having은 데이터를 그룹화하고 필터링하는데 사용됩니다. 

3-1. Group by

데이터를 특정 기준에 따라 그룹화하는데 Group by절은 특정 열을 기준으로 그룹화하고, 집계 함수를 사용하여 계산을 수행합니다.

select gender from member group by gender;
select gender, count(userid) as '인원' from member group by gender;

3-2. having

having은 group by절 다음에 사용되어, 그룹화된 결과에 조건을 적용하여 특정 그룹을 필터링합니다. where절과 차이점이라면 where절은 행을 필터링, having절은 그룹을 필터링합니다.

select gender, count(userid) as '인원' from member group by gender having gender='여자';