[SQL] 집계함수, 그룹화, SUB QUERY

seonyoung·2024년 7월 24일
0

📁 SQL 집계함수 : COUNT, MAX, MIN, SUM, AVG

  • SQL의 집계함수는 여러 행 또는 열로부터 하나의 결과값을 반환
  • 집계함수는 전체 데이터를 대상으로 사용하거나, 특정 컬럼을 기준으로 사용할 수 있음
  • 특히, 특정 컬럼을 기준으로 집계함수를 사용할 경우, GROUP BY 절을 반드시 사용해 줘야 함
  • 집계함수는 SELECT 문에서 사용됨
집계함수기능
COUNT테이블의 행 수 반환
SUM테이블의 열 합계 반환
AVG테이블의 열 평균 반환
MIN테이블의 열 최소값 반환
MAX테이블의 열 최대값 반환
  • 집계함수는 NULL값 가진 행을 제외하고 수행함
    ex)
# 나이 컬럼의 값이 30, 40, 50이 아니면 나이 컬럼을 NULL로 업데이트해주는 쿼리
UPDATE THEGLORY SET 나이 = NULL WHERE 나이 NOT IN (30, 40, 50);
  1. COUNT()를 이용해 행의 개수를 세면 NULL이 아닌 값의 개수를 반환
  • SELECT COUNT(날짜) FROM THEGLORY; → 5개 반환
  • COUNT()의 경우 행에 값이 하나라도 있으면 count가 됩니다. SELECT COUNT() FROM THEGLORY; → 7개 반환
  • 여기서 주의할 점은 빈값(””) 은 NULL이 아니기 때문에 각 행이 COUNT가 됨
  1. AVG를 이용해 평균을 구할 때 전체 7개의 행이 있어도, 평균을 구하려는 컬럼에 NULL이 4개, 숫자가 3개 있을 경우 숫자가 있는 3개의 행에 대해서만 평균을 구하게 됨
  • SELECT AVG(나이) FROM THEGLORY; → 40 반환
  • NULL값은 0으로 치환한 상태에서 평균 값을 구하고 싶은 경우
SELECT AVG(나이), SUM(나이)/COUNT(*), AVG(IFNULL(나이, 0)) FROM THEGLORY;

< 기타 집계 함수 >

  • STD(): 표준편차
  • VARIANCE(): 분산

< COUNT 예시 >

SELECT COUNT(*) FROM ANIMAL_INS;
  • COUNT(*)을 할 경우 주어진 행에 값이 하나라도 있으면 COUNT가 됨
  • COUNT(NAME) 을 하게 될 경우엔 NAME 컬럼의 값이 NULL이 아닌 행의 개수가 카운트 됨

< MIN 예시 >
-- 1회차 강의 때 LIMIT를 이용해 풀었던 문제입니다. MIN을 이용해서 더 쉽게 풀 수 있어요.

SELECT MIN(DATETIME) FROM ANIMAL_INS;

📁 SQL 그룹화: GROUP BY와 HAVING

  • GROUP BY절은 앞에서 배운 집계함수에 그룹(기준)이 더해진 개념
  • 전체 데이터를 기준으로 조회할 때는 GROUP BY절이 필요하지 않지만,
  • 특정 컬럼을 기준으로 데이터를 요약해서 비교하고 싶을 때 주로 사용
  • GROUP BY 뒤에는 그룹핑의 기준이 되는 컬럼이 오게 됨

<SQL 작성방법>

select
	기준컬럼,
	집계함수(기준컬럼을 기준으로 여러개의 집계함수 동시사용 가능)
from
	테이블명
where
	조건 #(생략가능)
group by
	기준컬럼;

(1) SELECT 뒤 기준컬럼 작성
(2) 집계함수(COUNT, MAX, MIN, AVG, SUM) 작성
(3) WHERE 절 뒤 GROUP BY 기준컬럼 작성 (WHERE 절은 생략 가능)
📌 SELECT문에 기준컬럼과 집계함수가 둘 다 있을 때는 GROUP BY 필수!

<문법 예시>

SELECT 성별, 이름, sum(나이) FROM THEGLORY t GROUP BY 성별, 이름;

ex) 입양 시각 구하기(GROPU BY + COUNT + DATE_FORMAT() + BETWEEN)

SELECT DATE_FORMAT(DATETIME, "%H") AS HOUR, COUNT(*) FROM ANIMAL_OUTS
WHERE DATE_FORMAT(DATETIME, "%H") BETWEEN 9 AND 19
GROUP BY HOUR
ORDER BY HOUR;

< HAVING ≠ WHERE ! >

  • Having절
    • Having절은 GROUP BY에 의한 결과를 필터링 할 때 사용
    • 주로 데이터를 그룹핑한 후, 특정 그룹을 골라내고 싶을 때 사용
    • SQL 구문에서는 GROUP BY 절 뒤에 위치
      ex)
 # 집계함수, GROUP BY절, HAVING을 함께 사용하는 SQL문

select
	기준컬럼,
	집계함수(기준컬럼을 기준으로 여러개의 집계함수 동시사용 가능)
from
	테이블명
where
	조건 #(생략가능)
group by
	기준컬럼
having
	조건식;
  • WHERE절
    • WHERE절은 GROUP BY 전 데이터를 기준으로 필터링을 한다면, HAVING절은 GROUP BY 후 결과값을 기준으로 데이터를 필터링
필터링 구문필터링 시점
WHEREGROUP BY 전 데이터 필터링
HAVINGGROUP BY 후 결과값을 가지고 데이터 필터링

ex) 동명 동물 수 찾기
Q. 동물 보호소에 들어온 동물 이름 중 두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 조회하는 SQL문을 작성 / 이때 결과는 이름이 없는 동물은 집계에서 제외하며, 결과는 이름 순으로 조회

SELECT NAME, COUNT(*) AS COUNT
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME
HAVING NAME >=2
ORDER BY NAME

📁 SQL : SUB QUERY 구문

  • SUB QUERY 를 통해, 데이터를 구조화 할 수 있음
  • 컬럼들을 활용한 사칙연산, 집계함수, 윈도우 함수, 포맷팅 등 많은 연산을 하는 경우, SUB QUERY 는 이를 순차적(구조적)으로 기록하는 역할을 수행
  • 복잡한 연산시, 최종 결과를 반환하기 위한 중간테이블의 역할

<사용 이유>

  • N 번의 쿼리문을 수행해야 하는데, N 번의 쿼리문 실행을 1번의 쿼리문으로 실행하기 위해 쿼리의 결과값을 가지고 추가 연산을 하기 위해 사용
  • JOIN, UNION 동작 수행을 대체 할 수 있는 방법을 제공

<실행 순서>

  • 서브쿼리 실행(안쪽에 위치한 쿼리) → 메인쿼리(바깥쪽에 위치한 쿼리) 실행
  • 쿼리의 가장 안쪽부터, 바깥쪽 쿼리를 실행하며 최종 결과값을 반환

<특징>

  • () 안에 SELECT, FROM 을 반드시 명시해 주어야 함
  • 서브쿼리 마지막에 ; 기호를 사용할 수 없음

<위치에 따른 구분>

위치명칭
SELECT 절스칼라 서브쿼리
FROM 절인라인 뷰
WHERE, HAVING 절중첩 서브쿼리

<스칼라 서브쿼리>

  • 주로 SELECT 절에서 사용
  • 컬럼이 올 수 있는 대부분의 위치에서 사용 가능
  • 하나의 컬럼처럼 사용
    - 예를 들어, THEGLORY테이블 옆에 ‘평균나이’를 나타내는 컬럼을 추가하고 싶은데 JOIN 하기는 번거로울 때 아래와 같이 사용 가능
    ex)
SELECT 이름, 나이, (SELECT AVG(나이) FROM THEGLORY) FROM THEGLORY;
  • 스칼라 서브 쿼리 내에 WHERE 문을 이용해서 메인쿼리 테이블과 서브쿼리 테이블을 연결할 수 있음
    ex) 
SELECT *, 
	(
    SELECT AUTHOR_NAME 
    FROM AUTHOR A WHERE A.AUTHOR_ID=B.AUTHOR_ID
    ) 
FROM BOOK B;
  • 실행 결과: 메인쿼리에 있는 테이블인 BOOK 테이블 옆에 서브쿼리에서 가져온 AUTHOR_NAME 컬럼이 추가됨
    ⚠️ 스칼라 서브쿼리를 작성할 때 주의할 점 : 서브쿼리에 의해 나오는 결과는 하나의 행이어야 함
    → 컬럼 대신 사용되는 것이기 때문에 반드시 하나의 값만을 반환해야 하며 그렇지 않을 경우 오류 발생

<인라인 뷰 (가장 많이 사용)>

  • FROM 절과 같이 테이블 명이 올 수 있는 위치에서 사용
  • 하나의 테이블처럼 사용 (뷰: 저장 장치 내에 물리적으로 존재하진 않는 가상 테이블)
  • AS 구문을 사용하여 명칭을 반드시 기재해야 함
  • 향후 JOIN 및 UNION 시 가장 유용하게 사용
    ex) 나이가 33세 이상인 모든 행 중 나이와 직업 컬럼 반환하기
select x.나이, x.직업
from(select *
		  from basic.theglory
		  where 나이>=33
	  )as x

ex) 인라인 뷰로 Join 하기

select x.나이, x.직업, y.결제금액 
from(select 나이, 직업, 이름
		  from basic.theglory
		  where 나이>=33
	  )as x, theglory2 y
WHERE x.이름 = y.이름;

<중첩(일반) 서브쿼리 (많이 사용)>

  • WHERE/HAVING 절에서 사용
    ex) 문동은의 나이보다 나이가 많은 모든 데이터를 반환하고 싶은데, 문동은 나이 하나 찾자고 Join문을 작성하는 것이 번거로울 때 사용 가능
select *
from basic.theglory 
where 나이 > (select 나이 from basic.theglory where 이름='문동은');
  • 서브쿼리인 select 나이 from basic.theglory where 이름='문동은' 의 실행 결과가 40이기 때문에 basic.theglory 테이블에서 나이가 40 초과인 행이 선택됨
  • 따라서 아래에 작성된 것과 같은 의미임
select *
from basic.theglory 
where 나이 > 40;

<중첩 서브 쿼리 구분하기>
1. 메인 쿼리와의 관계에 따른 구분

설명
비연관 서브쿼리메인쿼리와 관계를 맺고 있지 않음 / 서브 쿼리 내 메인 쿼리의 컬럼이 존재하지 않음
연관 서브쿼리메인쿼리와 관계를 맺고 있음 / 서브 쿼리 내 메인 쿼리의 컬럼 존재
  • 비연관 서브쿼리 예시
select * from theglory
where 나이 > (select 나이 from basic.theglory where 이름 = '문동은');
  • 연관 서브쿼리 예시
SELECT * FROM REST_INFO A
WHERE FAVORITES = (SELECT MAX(FAVORITES) FROM REST_INFO B WHERE B.FOOD_TYPE = A.FOOD_TYPE)
  • WHERE FAVORITES = (SELECT MAX(FAVORITES) FROM REST_INFO B WHERE B.FOOD_TYPE = A.FOOD_TYPE) : 서브쿼리가 반환하는 값과 A 테이블의 Favorites 값을 비교하는 조건
  • SELECT MAX(FAVORITES) FROM REST_INFO B WHERE B.FOOD_TYPE = A.FOOD_TYPE : REST_INFO 테이블에서 음식 종류가 같은 (B.FOOD_TYPE = A.FOOD_TYPE) 레코드 중 즐겨찾기 수가 가장 많은 값을 찾는 서브쿼리
    • WHERE B.FOOD_TYPE = A.FOOD_TYPE : 메인 쿼리(A테이블) 에서 현재 처리 중인 레코드의 FOOD_TYPE 과 동일한 FOOD_TYPE 을 가진 레코드를 B 테이블에서 찾음
    • SELECT MAX(FAVORITES) : 위 조건을 만족하는 레코드들 중 FAFORITES 가 가장 큰 값을 선택

<CTE, Common Table Expressions (a.k.a WITH 절)>

  • SELECT, INSERT , UPDATE, DELETE에서 참조가 가능한 임시의 결과값
  • WITH 절로 정의
  • 기본문법
WITH 이름정의 AS (
	SELECT	*
	FROM 테이블명
)

SELECT * FROM 이름정의;
  • 위와 같이 WITH 절을 이용해 임시 테이블을 만들고 다른 쿼리에서 테이블 처럼 사용 가능
  • 이를 통해 쿼리를 더 읽기 쉽고 구조화된 방식으로 작성 가능

📁 함수 익히기

<반올림&올림&내림함수 - ROUND() / FLOOR() / CEIL()>

  • ROUND : 숫자, 반올림할 자릿수(몇자리까지 남길것인지)
    ex)
SELECT ROUND(3.14, 1); --> 3.1
  • FLOOR(숫자): 소수점 이하 모두 버림
    ex) 가격대 별 상품 개수 구하기
SELECT 
    FLOOR(PRICE / 10000) * 10000 AS PRICE_GROUP, -- 가격을 만 단위로 그룹화
    COUNT(*) AS PRODUCTS -- 해당 가격대에 속하는 상품 개수
FROM 
    PRODUCT
GROUP BY 
    PRICE_GROUP -- 가격대별로 그룹화
ORDER BY 
    PRICE_GROUP ASC; -- 가격대를 기준으로 오름차순 정렬
  • CEIL(숫자): 소수점 이하 모두 올림
    ex)
SELECT CEIL(3.14);

<문자열 자르기 함수 - SUBSTRING(), SUBSTRING_INDEX(), LEFT(), RIGHT()>

  • SUBSTRING (문자열, 시작 위치값, 가져올 길이값)
    ex) 
SELECT substring("일이삼사오육칠팔구십", 3, 5); --> 삼사오육칠
  • SUBSTRING_INDEX (문자열, 구분자, 구분자 INDEX값)
    ex)
SELECT SUBSTRING_INDEX('사과,바나나,딸기,포도', ',', 2); --> 사과, 바나나

# 중간에 있는 값 선택. (-1은 뒤에서부터 세기)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('사과,바나나,딸기,포도', ',', 2), ',', -1); --> 바나나
  • LEFT (문자열, 시작 위치값) : 왼쪽부터 값을 가져옴
    ex) 
SELECT LEFT("일이삼사오육칠팔구십", 3); --> 일이삼
  • RIGHT (문자열, 시작 위치값) : 오른쪽부터 값을 가져옴
    ex)
SELECT RIGHT("일이삼사오육칠팔구십", 3); --> 팔구십
profile
seonyoung
원하는 바를 이루고 싶은 사람입니다.
이전 포스트

SQL 되돌아보기

다음 포스트

[SQL] UNION 함수, DB정규화, JOIN 함수

0개의 댓글