SQL with 빅쿼리 - 집계함수, 그룹함수, 기본키(Primary Key), 외래키(Foreign Key), 조인, 서브쿼리

1. 데이터 집계하기

1️⃣ 집계함수란?

집계함수는 여러 데이터 값을 계산하여 하나의 결과를 반환하는 함수

COUNT() : 데이터의 개수를 구하는 함수

# 전체 학생 수 
# COUNT(*)이면 NULL 값 포함
SELECT COUNT(*)
FROM 학생

# 점수 컬럼의 행의 개수
# NULL 값을 제외한 컬럼명의 개수
SELECT COUNT(점수)
FROM 학생

SUM() : 숫자 데이터의 합계

# 전체 학생의 점수 합
SELECT SUM(점수)
FROM 학생

AVG() : 평균값

#전체 학생의 평균 점수
SELECT AVG(점수)
FROM 학생

MAX() : 가장 큰 값

#전체 학생에서 최고 점수
SELECT AVG(점수)
FROM 학생

MIN() : 가장 작은 값

#전체 학생에서 최저 점수
SELECT AVG(점수)
FROM 학생

• MAX(컬럼명)과 MIN(컬럼명)는 문자열 컬럼에도 적용 가능, 높고 낮은 순(날짜순, 알파벳 순(Z-A)등)으로 연산

집계함수의 특징

• 집계함수 내부에 필요한 조건만 집게하고 싶은 경우 조건문을 사용할 수 있음

형태	의미
COUNT(CASE WHEN 조건 THEN 1 END)	조건 만족 행 개수
SUM(CASE WHEN 조건 THEN 값 ELSE 0 END)	조건 만족 값 합계
AVG(CASE WHEN 조건 THEN 값 END)	조건 만족 평균

# 합격자, 불합격자 몇 명인지 카운트
SELECT
COUNT(CASE WHEN 점수 >= 60 THEN 1 END) AS 합격자,
COUNT(CASE WHEN 점수 < 60 THEN 1 END) AS 불합격자
FROM 학생

# 전자 제품 매출만 합계
SELECT
SUM(CASE WHEN 카테고리 = '전자' THEN 매출 ELSE 0 END) AS 전자매출
FROM 판매

• 집계함수의 결과값끼리 계산할 수 있음

SUM(컬럼명) / COUNT(컬럼명)

2️⃣ 데이터 그룹화하기(GROUP BY)

# 반끼리 묶어서 각 반의 평균 점수
SELECT 반, AVG(점수) AS 평균점수
FROM 학생
GROUP BY 반

# GROUP BY 1은 SELECT 절의 첫 번째 컬럼을 의미
# GROUP BY 1 = GROUP BY 반
SELECT 반, AVG(점수) AS 평균점수
FROM 학생
GROUP BY 1

3️⃣ 그룹화한 데이터 조건주기(HAVING)

GROUP BY로 그룹을 만든 후 집계 결과에 조건을 적용하는 함수

# 반끼리 묶어서 각 반의 평균 점수 중 80점 이상인 반
SELECT 반, AVG(점수) AS 평균점수
FROM 학생
GROUP BY 반
HAVING AVG(점수) >= 80

🚨 WHERE와 HAVING의 차이

구분	WHERE	HAVING
적용 시점	그룹 생성 전	그룹 생성 후
사용 목적	행 필터	그룹 필터
집계함수 사용	불가능	가능
사용 위치	GROUP BY 전에	GROUP BY 뒤에

# WHERE
# 점수 70점 이상인 학생만 남기고 그 다음 반별 평균 계산
SELECT 반, AVG(점수)
FROM 학생
WHERE 점수 >= 70
GROUP BY 반

# HAVING
# 반별 평균을 계산하고 평균이 70점 이상인 반만 출력
SELECT 반, AVG(점수)
FROM 학생
GROUP BY 반
HAVING AVG(점수) >= 70

# WHERE + HAVING
# 점수 60점 이상 학생만 선택하고 반별 평균 계산 후 평균 75점 이상 반만 출력
SELECT 반, AVG(점수)
FROM 학생
WHERE 점수 >= 60
GROUP BY 반
HAVING AVG(점수) >= 75

📝 작성 순서

SELECT
FROM 
WHERE
GROUP BY
HAVING
ORDER BY 
LIMIT

2. 여러 개의 테이블 사용하기

1️⃣ 기본키 (Primary Key, PK)

• 기본키는 각 행(row)을 유일하게 구분할 수 있는 컬럼
• 한 테이블에서 각 데이터를 식별하는 고유한 값
• 하나의 컬럼이 지정되어 있으며, 해당 컬럼의 데이터는 테이블 내 유일성을 보장
• 값이 중복될 수 없음
• NULL 값 불가능
• 한 테이블에 1개만 존재 (하지만 여러 컬럼을 묶어서 만들 수 있음)
• 테이블이 하나로 합쳐져 있다면 값을 수정해야 하는 상황에서 여러 테이블의 데이터를 전부 다 바꿔줘야 해서 비효율적
• 관리 목적에 따라 테이블을 구분하는 것이 잘 설계된 데이터 베이스라고 할 수 있음
• 학생 테이블 : 학번이 기본키(모든 학생의 학번이 서로 다르고 학생을 유일하게 식별 가능, 이름은 같을 수 있지만 학번은 절대 같을 수 없음)

학번 (PK)	이름	학과
1001	김철수	컴퓨터
1002	이영희	경영
1003	박민수	수학

2️⃣ 외래키 (Foreign Key, FK)

• 외래키는 항상 다른 테이블의 기본키를 참조
• 테이블과 테이블을 연결하는 역할
• 외래키는 데이터 관계를 유지하기 위해 사용
• 수강 테이블 : 여기에서 학번이 외래키(존재하는 학생만 수강이 가능 → 외래키가 없으면 존재하지 않는 학생이 수강하는 문제가 발생할 수 있음 → 외래키는 이런 문제를 막음)

수강ID	학번 (FK)	과목
1	1001	데이터베이스
2	1002	인공지능

💡 기본키(PK) vs 외래키(FK) 정리

구분	기본키(PK)	외래키(FK)
의미	테이블의 고유 식별자	다른 테이블의 PK 참조
중복	불가능	가능
NULL	불가능	가능
개수	테이블당 1개	여러 개 가능
역할	데이터 식별	테이블 연결

3️⃣ 다중 테이블 사용하기(JOIN)

JOIN은 두 개 이상의 테이블을 공통 컬럼을 기준으로 연결하는 것

데이터베이스에서는 보통 정보를 여러 테이블로 나누어 저장하기 때문에 JOIN을 많이 사용한다고 함

그리고 JOIN을 할 때, 테이블에 별칭을 많이 사용함

# 2개 테이블 합치기
SELECT 컬럼명
FROM 테이블1 AS A
JOIN 테이블2 AS B
ON A.id = B.id

# 3개 테이블 합치기
SELECT 컬럼명
FROM 테이블1 AS A
JOIN 테이블2 AS B
ON A.id = B.id
JOIN 테이블3 AS C
ON A.id = C.id

INNER JOIN

• 가장 기본적인 JOIN
• 두 테이블에 공통으로 존재하는 데이터만 가져옴
• 실무에서 많이 씀

OUTER JOIN(LEFT JOIN)

• 왼쪽 테이블의 모든 데이터 유지
• 없는 값은 NULL로 채워짐
• 실무에서 많이 씀

OUTER JOIN(RIGHT JOIN)

• 오른쪽 테이블의 모든 데이터 유지

JOIN 종류	의미
INNER JOIN	두 테이블에 공통 데이터만
LEFT (OUTER) JOIN	왼쪽 테이블 모두 + 일치 데이터
RIGHT (OUTER) JOIN	오른쪽 테이블 모두 + 일치 데이터
FULL JOIN	양쪽 테이블 모든 데이터

4️⃣ UNION

테이블을 아래로, 세로로 붙이는 형태로 새로운 행을 생성

데이터가 너무 많으면 테이블을 쪼개서 관리할 수 있음

UNION 특징

조건	설명
컬럼명	달라도 적용 가능함, 위 테이블의 컬럼명으로 합쳐짐
컬럼 개수	같아야 함 다르면 에러 발생
컬럼 순서	같아야 함 다르면 에러 발생
데이터 타입	같아야 함 다르면 에러 발생

# 중복 포함
SELECT *
FROM 테이블1
UNION ALL 
SELECT *
FROM 테이블2

# 중복 제거
SELECT *
FROM 테이블1
UNION 
SELECT *
FROM 테이블2

5️⃣ 서브쿼리 원리와 방식(Subquery)

• 서브쿼리(Subquery)는 SQL에서 하나의 쿼리 안에 또 다른 쿼리를 넣어서 사용하는 방법
• 쿼리 안에 들어가는 쿼리, 중첩 쿼리라고도 함
• JOIN과 달리 새로운 컬럼이 필요하지 않을때 서브쿼리를 사용
• 완전히 다른 테이블에서 데이터 값을 조회하여 메인쿼리의 조건으로 사용하려할 때 유용
• 메인 쿼리가 서브쿼리를 포함하는 종속적인 관계

서브쿼리의 특징

• 괄호를 묶어서 사용
• 실행순서 : 서브쿼리 실행 → 메인쿼리 실행
• 서브쿼리 내에서는 ORDER BY 문법이 지원되지 않음

1. WHERE절 서브쿼리

• 서브쿼리의 결과는 메인쿼리의 조건으로 사용
• 조건 계산

단일행 서브쿼리

• 서브쿼리의 실행 결과가 항상 1건 이하인 서브쿼리
• 단일행 비교연산자(=, !=, >, < 등)와 함께 사용

SELECT 컬럼명
FROM 테이블명
WHERE 컬럼명 = (SELECT 컬럼명
              FROM 테이블명 
              WHERE 조건절)

# 평균 점수보다 높은 학생 찾기
SELECT 이름
FROM 학생
WHERE 점수 > (
    SELECT AVG(점수)
    FROM 학생
)

다중행 서브쿼리

• 서브쿼리의 실행 결과가 여러 건인 서브 쿼리
• 다중행 비교연산자(IN, NOT IN 등)와 함께 사용

SELECT 컬럼명
FROM 테이블명
WHERE 컬럼명 IN (SELECT 컬럼명
                         FROM 테이블명
                         WHERE 조건절)

# 우수반과 일치하는 반의 이름들을 출력
SELECT 이름
FROM 학생
WHERE 반 IN (
    SELECT 반
    FROM 우수반
)

2. FROM 서브쿼리

• 서브쿼리를 작성해서 결과를 하나의 테이블처럼 사용하는 방법
• 서브쿼리를 임시 테이블처럼 사용
• FROM 서브쿼리는 반드시 별칭(alias)이 필요

인라인뷰 (Inline View)

• FROM절 서브쿼리를 인라인뷰라고 부름

SELECT 컬럼
FROM (
    SELECT 컬럼
    FROM 테이블
) AS 별칭

SELECT *
FROM (
    SELECT 반, AVG(점수) AS 평균점수
    FROM 학생
    GROUP BY 반
) AS result
WHERE 평균점수 >= 75

3. SELECT 서브쿼리

• 컬럼 계산

SELECT
이름,
점수,
(SELECT AVG(점수) FROM 학생) AS 평균
FROM 학생

julianday()

• 날짜를 "Julian Day Number"라는 숫자로 변환하는 함수
• 날짜 차이 계산할 때 사용
• 날짜가 숫자 값으로 변환됨

# 기본 문법
julianday('2026-03-05')

# 날짜 차이 계산 
#오늘 기준 경과 일수
SELECT julianday('now') - julianday('2024-01-01')

• 날짜 차이를 구할 때 MySQL 등과 같은 DBMS에서는 날짜와 관련된 함수, 예를 들면 DATEDIFF 등의 함수를 사용하지만 pandasql과 SQLite에서는 julianday 함수를 사용한다고 함
• 예시

order_date	delivery_date
2024-01-01	2024-01-05
2024-02-01	2024-02-03

SELECT
delivery_date,
order_date,
julianday(delivery_date) - julianday(order_date) AS 배송일수
FROM orders

delivery_date	order_date	배송일수
2024-01-05	2024-01-01	4
2024-02-03	2024-02-01	2

📌 pandasql에서 사용 가능한 주요 날짜 함수

함수	설명
date()	날짜
datetime()	날짜 + 시간
julianday()	날짜를 숫자로 변환
strftime()	날짜 포맷 추출

# 날짜 차이
SELECT julianday(end_date) - julianday(start_date)

#연도 추출
SELECT strftime('%Y', date)

#월 추출
SELECT strftime('%m', date)