[TIL] SQL 베이직 - 2회차

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

 SQL의 집계함수는 여러 행 또는 열로부터 하나의 결과값을 반환한다. 집계함수는 전체 데이터를 대상으로 사용하거나, 특정 컬럼을 기준으로 사용할 수 있다.

 집계함수는 NULL값 가진 행을 제외 하고 수행하기 때문에 count()를 이용해 행의 개수를 세었을 때 NULL 값을 제외한 수가 반환된다. AVG를 이용할 때도 NULL 값을 제외하고 평균을 구한다.

 

2. SQL 그룹화: GROUP BY와 HAVING

 GROUP BY절 집계함수에 그룹(기준) 이 더해진 개념이다. 특정 컬럼을 기준으로 데이터를 요약해서 비교하고 싶을 때 사용한다. SELECT문에 기준컬럼과 집계함수가 둘 다 있는 경우 GROUP BY는 필수이다.

# 집계함수, GROUP BY절, HAVING을 함께 사용하는 SQL문
select
	기준컬럼,
	집계함수(기준컬럼을 기준으로 여러개의 집계함수 동시사용 가능)
from
	테이블명
where
	조건 #(생략가능)
group by
	기준컬럼
having
	조건식;

필터링 구문	필터링 시점
WHERE	GROUP BY 전 데이터 필터링
HAVING	GROUP BY 후 결과값을 가지고 데이터 필터링

 

3. SQL : SUB QUERY 구문

 SUB QUERY는 컬럼들을 활용한 사칙연산, 집계함수, 윈도우 함수, 포멧팅 등 많은 연산을 하는 경우 순차적으로 기록하는 역할을 수행한다. 최종 결과를 반환하기 위한 중간테이블의 역할을 한다.

 실행 순서는 서브쿼리(안쪽에 위치한 쿼리) → 메인쿼리(바깥쪽에 위치한 쿼리) 순서로 쿼리의 가장 안쪽에서 바깥쪽으로 쿼리를 실행하며 최종 결과값을 반환한다.

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

 

- 스칼라 서브쿼리

 SELECT절에서 주로 사용한다. 컬럼이 올 수 있는 대부분의 위치에서 사용이 가능하다. 서브쿼리에 의해 나오는 결과는 하나의 행이어야 한다. 컬럼 대신 사용되는 것이므로 반드시 하나의 값만 반환해야 하며 그렇지 않을 경우 오류가 발생한다.

SELECT 이름, 나이, (SELECT AVG(나이) FROM THEGLORY) FROM THEGLORY;
------------------------------------------------------------------------------------------------
-- 스칼라 서브 쿼리 내에 WHERE절을 이용해서 메인쿼리 테이블과 서브쿼리 테이블을 연결할 수 있음
SELECT *, 
	(SELECT AUTHOR_NAME FROM AUTHOR A WHERE A.AUTHOR_ID=B.AUTHOR_ID) 
FROM BOOK B;

 

- 인라인 뷰

 가장 많이 사용하는 것으로 FROM 절과 같이 테이블 명이 올 수 있는 위치에서 사용한다. 하나의 테이블처럼 사용하며 반드시 명칭을 기재해야 한다. JOIN 및 UNION 사용시 가장 유용하게 사용할 수 있다.

# 인라인 뷰로 Join 하기
select x.나이, x.직업, y.결제금액 
from(select 나이, 직업, 이름
		  from basic.theglory
		  where 나이>=33
	  )as x, theglory2 y
WHERE x.이름 = y.이름;

 

- 중첩 서브쿼리

WHERE/HAVING 절에서 사용된다.

-- "문동은의 나이보다 나이가 많은 모든 데이터를 반환

select *
from basic.theglory 
where 나이 > (select 나이 from basic.theglory where 이름='문동은');

 

- 중첩 서브 쿼리 구분하기

 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)

 

  2) 반환하는 데이터 형태에 따른 구분

 ▷ 단일 행 서브쿼리: 서브 쿼리가 1건 이하의 데이터 반환/ 단일 행 비교 연산자와 함께 사용( =, <, >, <=, >= , !=)

 ▷ 다중 행 서브쿼리: 서브 쿼리가 여러건의 데이터로 반환/ 다중 행 비교 연산자와 함께 사용( IN, ALL, ANY, SOME, EXISTS)

 ▷ 다중 컬럼 서브쿼리: 서브쿼리가 여러 컬럼의 데이터 반환

-- 단일 행 서브쿼리 예시
select * from theglory
where 나이 > (select 나이 from basic.theglory where 이름 = '문동은');
------------------------------------------------------------------------------------------------
-- 다중 행 서브쿼리 예시
SELECT * FROM ANIMAL_INS I 
WHERE I.ANIMAL_ID NOT IN (SELECT ANIMAL_ID FROM ANIMAL_OUTS);
------------------------------------------------------------------------------------------------
-- 다중 컬럼 서브쿼리 예시
SELECT * FROM REST_INFO
WHERE (FOOD_TYPE, FAVORITES) IN 
(SELECT FOOD_TYPE, MAX(FAVORITES) FROM REST_INFO GROUP BY FOOD_TYPE);

 

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

 SELECT, INSERT, UPDATE, DELETE에서 참조가 가능한 임시의 결과값이다. WITH 절을 이용해 임시 테이블을 만들고 다른 쿼리에서 테이블 처럼 사용할 수 있다.

WITH 이름정의 AS (
	SELECT	*
	FROM 테이블명
)
SELECT * FROM 이름정의;
------------------------------------------------------------------------------------------------
WITH S AS (
SELECT BOOK_ID, SALES FROM BOOK_SALES WHERE SALES_DATE LIKE "2022-01%"
)

SELECT CATEGORY, SUM(SALES) AS TOTAL_SALES FROM
    S, BOOK AS B
WHERE S.BOOK_ID = B.BOOK_ID
GROUP BY B.CATEGORY
ORDER BY CATEGORY;

 

4. 함수 익히기

- 반올림 / 올림 / 내림함수: ROUND() / FLOOR() / CEIL()

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

 

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

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

# 중간에 있는 값 선택 (-1은 뒤에서부터 세기)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('사과,바나나,딸기,포도', ',', 2), ',', -1); --> 바나나
------------------------------------------------------------------------------------------------
-- LEFT (문자열, 시작 위치값): 왼쪽부터 값을 가져옴
SELECT LEFT("일이삼사오육칠팔구십", 3); --> 일이삼
------------------------------------------------------------------------------------------------
-- RIGHT (문자열, 시작 위치값): 오른쪽부터 값을 가져옴
SELECT RIGHT("일이삼사오육칠팔구십", 3); --> 팔구십