8.1 그룹화란
select 그룹화_칼럼, 집계_함수(일반_칼럼)
from 테이블명
where 조건 # 필터링 조건이 없으면 생략
group by 그룹화_칼럼;그룹화: 데이터 분석 기법의 하나,
데이터를 특정 기준에 따라 여러 그룹으로 나누고 그룹별 데이터를 요약하거나 분석하는 것
group by 절의 특징
- 집계 함수와 함께 사용: 유의미한 데이터 분석 결과를 얻으려면, 그룹화 한 후 반드시 후속 집계 작업을 해야 함.
- 여러 칼럼으로 그룹화: 2개 이상의 칼럼을 기준으로 그룹화할 수 있음
select 그룹화_칼럼1, 그룹화_칼럼2, 집계_함수(일반 칼럼)
from 테이블명
where 조건
group by 그룹화_칼럼1, 그룹화_칼럼2;3. select 절에 올 수 있는 칼럼이 제한적: 그룹화한 쿼리의 select 절에 올 수 있는 칼럼은
group by 절에서 지정한 그룹화 칼럼 or 집계 함수의 일부로 사용되는 칼럼
8.2 그룹화 필터링, 정렬, 조회 개수 제한
select 그룹화_칼럼, 집계_함수(일반_칼럼)
from 테이블명
where 일반_필터링_조건
group by 그룹화_칼럼
having 그룹_필터링_조건;그룹화 필터링: 그룹화한 결과에서 특정 조건을 만족하는 그룹의 데이터만 가져오는 것,
group by 절에 having 절을 추가해 수행
where 절은 개별 튜플에 대해 필터링 수행, having 절은 그룹화한 결과, 즉 그룹 단위로 필터링 수행
select *
from 테이블명
where 조건
order by 정렬_칼럼1 [asc | desc], 정렬_칼럼2 [asc | desc], ...;정렬: 쿼리 결과의 데이터를 오름차순(기본값) 또는 내림차순으로 배열, order by 절 사용
select 칼럼1, 칼럼2, ...
from 테이블명
limit n offset m; #상위 n개 튜플까지 조회, m: 건너뛸 튜플의 개수조회 개수 제한: 상위 N개 튜플을 조회할 때 사용, limit 절 이용
9.1 서브쿼리란
select 칼럼명1, 칼럼명2, ...
from 테이블명
where 칼럼명 연산자 (
서브쿼리
);서브쿼리: 쿼리 안에 포함된 또 다른 쿼리, 안쪽 서브쿼리의 실행 결과를 받아 바깥쪽 메인쿼리가 실행
서브쿼리의 특징
- 중첩 구조: 서브쿼리는 메인쿼리 내부에 중첩해 작성, 괄호 안에 작성
- 메인쿼리와는 독립적으로 실행
- 다양한 위치에서 사용 가능: select, from, join, where, having 절 등에서 사용
- 단일 값 또는 다중 값을 반환: 단일 값 서브쿼리: 비교 연산자와 함께 사용, 다중 값 서브쿼리: in, any, all, exists 연산자와 함께 사용
- 조건 필터링 결과 또는 데이터 집계 결과를 반환
9.2 다양한 위치에서의 서브쿼리
-- select 절에서의 서브쿼리 실습
select payment_type as '결제 유형',
amount as '결제 금액',
amount - (select avg(amount) from payments) as '평균 결제 금액과의 차이'
from payments;select 절에서의 서브쿼리: 단일 값, 즉 하나의 행과 하나의 칼럼(1x1)을 반환
-- from 절에서의 서브쿼리 실습
-- 메인쿼리: 1회 주문 시 평균 상품 개수 집계
select avg(sub.total_count) as '1회 주문 시 평균 상품 개수'
from (
-- 서브쿼리: 주문 아이디별 총 삼품 개수 집계
select order_id, sum(count) as total_count
from order_details
group by order_id
) as sub;from 절에서의 서브쿼리: 반드시 별칭을 지정해야 함, 반환하는 행과 칼럼의 개수에 제한X
-- join 절에서의 서브쿼리 실습
-- 메인쿼리: 상품별 주문 개수 집계
select name as '상품명', total_count as '주문 개수'
from products
join (
-- 서브쿼리: 상품 아이디별 주문 개수 집계
select product_id, sum(count) as total_count
from order_details
group by product_id
) as sub on products.id = sub.product_id;join 절에서의 서브쿼리: 별칭 지정해야 함, 반환하는 행과 칼럼의 개수에 제한X
-- where 절에서의 서브쿼리 실습
-- 메인쿼리: 평균 가격보다 비싼 상품 조회
select name as '상품명', price as '가격'
from products
where price > (
-- 서브쿼리: 상품의 평균 가격 집계
select avg(price) as avg_price
from products
);where 절에서의 서브쿼리: 단일 값(1x1) 또는 다중 행의 단일 칼럼(nx1)을 반환
-- having 절에서의 서브쿼리 실습
-- 메인쿼리: 크림 치즈 보다 매출이 높은 상품 조회
select name as '상품명', sum(price * count) as '매출'
from products
join order_details on products.id = order_details.product_id
group by name
having sum(price * count) > (
-- 서브쿼리: 크림 치즈의 매출 합계 집계
select sum(price * count) as cream_cheese_sales
from products
join order_details on products.id = order_details.product_id and name = '크림 치즈'
);having 절에서의 서브쿼리: 그룹화 필터링을 수행하므로
where 절의 서브쿼리와 같이 단일 값(1x1) 또는 다중 행의 단일 칼럼(nx1) 반환
9.3 IN, ANY, ALL, EXISTS
select 칼럼명1, 칼럼명2, ...
from 테이블명
where 칼럼명 in (쉼표로_구분된_값_목록);
-- 또는
select 칼럼명1, 칼럼명2, ...
from 테이블명
where 칼럼명 in (다중_행의_단일_칼럼을_반환하는_서브쿼리);IN 연산자: 지정된 집합에 포함되는 대상을 찾기
별칭 지정시 as 생략 가능
select 칼럼명1, 칼럼명2, ...
from 테이블명
where 칼럼명_비교_연산자_ any (다중_행의_단일_칼럼을_반환하는_서브쿼리);ANY 연산자: 지정된 집합의 모든 요소와 비교 연산을 수행해 하나라도 만족하는 대상 찾기
select 칼럼명1, 칼럼명2, ...
from 테이블명
where 칼럼명 비교_연산자 all (다중_행의_단일_칼럼을_반환하는_서브쿼리);ALL 연산자: 지정된 집합의 모든 요소와 비교 연산을 수행해 모두를 만족하는 대상 찾기
select 칼럼명1, 칼럼명2, ...
from 테이블명
where exists (서브쿼리); #where not exists (서브쿼리);
-- 예시
-- exist 연산자 실습
-- 메인쿼리: 적어도 한 번은 주문한 사용자 조회
select *
from users u
where exists (
-- 서브쿼리: 주문자 아이디가 사용자 테이블에 있다면 1 반환
select 1
from orders o
where o.user_id = u.id
);EXISTS 연산자: 서브쿼리를 입력받아 서브쿼리가 하나 이상의 행을 반환하는 경우 TRUE를 ,그렇지 않으면 FALSE를 반환
NOT EXISTS 연산자: EXISTS 연산자와 반대로 동작
상관쿼리: 메인쿼리의 각 행에 대해 반복 실행되는 서브쿼리, 서브쿼리가 메인쿼리의 칼럼 값을 참조
상관쿼리의 주요 특징
- 의존성: 메인쿼리의 값을 참조해 데이터 필터링 수행
- 반복 실행: 메인쿼리의 각 행에 대해 반복적으로 실행
- 성능 저하: 메인쿼리의 각 행마다 서브쿼리를 실행하므로 쿼리 전체의 성능이 저하될 수 O
Part 4. 데이터 모델링
10.1 데이터 모델링이란
데이터 모델링: 현실 세계의 어떤 사물이나 현상을 추상화(모형화)해 데이터 간 구조와 관계를 정의한 '데이터 모델'을 만드는 과정
데이터 모델링의 이점
- 비즈니스 요구사항을 반영
- 의사소통 개선에 기여
- 데이터 무결성 보장
- 성능 최적화
- 유연한 확장 가능
- 비용 절감
개념적 데이터 모델링: 현실 세계를 구성하는 주요 개체(entity)와 그들 간 관계(relation)을 정의하는 단계,
기술적인 부분은 고려X
논리적 데이터 모델링: 개념적 데이터 모델링의 결과로 만들어진 데이터 모델의 세부 내용을 정의하는 단계,
엔티티의 속성, 식별자, 관계의 유형을 정의, 정규화 진행,
특정 DBMS에 종속되지 않고 반복적인 검증을 통해 개선해 나가는 방식으로 수행
물리적 데이터 모델링: 논리적 데이터 모델링의 결과로 만들어진 데이터 모델을 특정 DBMS에 맞게 최적화하는 단계
10.2 데이터 모델의 구성 요소
엔티티: 데이터베이스에서 관리하려는 핵심 대상
테이블: 엔티티를 실제 데이터베이스에 구현한 것, 행과 열의 표 형태
인스턴스: 엔티티의 실제 데이터, 테이블에 저장된 행 단위의 데이터
속성: 엔티티의 특성이나 정보를 나타내는 항목
칼럼: 속성을 실제 데이터베이스에 구현하면 테이블의 칼럼
식별자: 엔티티의 각 인스턴스를 고유하게 식별하는 데 사용하는 속성 또는 속성의 조합
기본 식별자(PK): 인스턴스를 고유하게 식별하는 속성 또는 속성의 조합, 값이 반드시 존재하고 유일
대체 식별자(AK): 기본 식별자로 선택되지 않은 후보 식별자, 값이 반드시 존재하고 유일
외래 식별자(FK): 한 엔티티의 식별자가 다른 엔티티의 식별자를 참조해 관계를 만드는 식별자
속성이 식별자로 사용되기 위한 4가지 조건
- 유일성
- 불변성
- 존재성
- 최소성
관계: 두 엔티티 간 연결된 방식을 나타내는 것
카디널리티: 엔티티의 한 인스턴스가 다른 엔티티의 몇 개 인스턴스와 연결되는지
ER 다이어그램: 데이터 모델을 정의하는 시각적 도구, 엔티티, 속성, 관계를 기호로 그린 것
엔티티: 사각형
속성: 엔티티 내부의 텍스트
관계: 직선, 수직선 or 까치발 모양
선택성: 필수(카디널리티 1 ~) or 선택(카디널리티 0 ~)
-- 고유키 지정 방법 1: 칼럼 선언 시 지정
create table users (
id integer auto_increment,
email varchar(100) unique -- 고유키 지정
...
);
-- 고유키 지정 방법 2: 별도로 지정
create table users (
id integer auto_increment,
email varchar(100),
...
unique (email) -- 고유키 지정
);고유키: 테이블 내에서 값이 고유해야 하는 특정 칼럼 또는 칼럼의 조합
-- 인덱스 생성 방법1: 따로 인덱스 생성
create index 인덱스명 on 테이블명 (칼럼명);
-- 인덱스 생성 방법2: 테이블 생성하면서 인덱스 생성
create table 테이블명 (
칼럼명1 자료형1,
칼럼명2 자료형2,
...
index 인덱스명 (칼럼명1) -- 칼럼명1에 인덱스 생성
);인덱스: 데이터베이스에서 검색 및 정렬 성능을 최적화하는 데이터 구조, 검색 성능 향상, 저장 공간에 따른 비용 발생
10.4 정규화
정규화: 데이터 모델링할 때 데이터가 중복으로 저장되는 것을 최소화하고 데이터 무결성을 보장하기 위해 데이터를 구조화하는 작업
제1정규형
- 각 속성은 원자값을 가져야 한다
- 같은 속성이라면 모두 동일한 자료형을 가져야 한다
- 각 인스턴스는 고유해야 한다
원자화: 속성을 더 이상 분해할 수 없을 때까지 잘게 나눠야 한다
이상 현상: 테이블 설계가 잘못됐거나 비효율적일 때 발생하는 문제
제2정규형
- 제1정규형을 만족해야 한다
- 기본키가 아닌 일반 속성은 기본키의 전체 속성에 의해 결정돼야 한다
완전 함수 종속: 일반 속성이 기본키 전체에 종속된 경우
부분 함수 종속: 일반 속성이 기본키의 일부에만 종속된 경우
제3정규형
- 제2정규형을 만족해야 한다
- 기본키가 아닌 일반 속성은 다른 일반 속성에 의해 결정되지 않아야 한다.
이행적 함수 종속: 기본키가 아닌 일반 속성에 의해 결정되는 또 다른 일반 속성이 있는 경우
'데이터 분석 > SQL' 카테고리의 다른 글
| [SQL 데이터베이스 입문] 3주차 (0) | 2025.03.05 |
|---|---|
| [SQL 데이터베이스 입문] 2주차 (0) | 2025.02.27 |
| [SQL 데이터베이스 입문] 1주차 (0) | 2025.02.22 |
