[TIL-260304] DB 기초: SET, 그룹 함수, 주요 함수

SET(집합) 연산자

• 독립된 여러 개의 쿼리를 하나로 묶는 역할.
• 최종 결과는 하나의 쿼리가 수행된 것과 같다.

❗❗현업에서 거의 안 씀❗❗
부하가 많이 나기 때문에 합칠거면 조인을 많이 한다.

종류	설명
UNION	합집합으로 묶어 줌. 중복은 제거.
UNION ALL	합집합으로 묶어 줌. 중복 포함. (진짜 잘 안 씀)
INTERSECT	교집합 처리.
EXCEPT	차집합 처리.

 

예제

--1990년 이전 출생자와 키가 175 이상인 사용자 목록(중복 제거)
SELECT userID , name, birthYear , addr
FROM userTbl
WHERE birthYear < 1990
UNION
SELECT userID , name, birthYear , addr
FROM userTbl
WHERE height >= 175;

--1990년 이전 출생자와 키가 175 이상인 사용자 목록(중복 포함)
SELECT userID , name, birthYear , addr
FROM userTbl
WHERE birthYear < 1990
UNION ALL
SELECT userID , name, birthYear , addr
FROM userTbl
WHERE height >= 175;

--1990년 이전 출생자이면서 키가 175 이상인 사용자 목록
SELECT userID , name, birthYear , addr
FROM userTbl
WHERE birthYear < 1990
INTERSECT
SELECT userID , name, birthYear , addr
FROM userTbl
WHERE height >= 175;

--1990년 이전 출생자 중에서 키가 175 이상인 사용자를 제외한 목록
SELECT userID , name, birthYear , addr
FROM userTbl
WHERE birthYear < 1990
EXCEPT
SELECT userID , name, birthYear , addr
FROM userTbl
WHERE height >= 175;

 

SET 연산자 주의사항

• 각 SELECT 문의 컬럼 수, 데이터 타입이 일치해야 한다.
• ORDER BY는 마지막 SELECT 문에만 사용 가능하다.
• INTERSECT/EXCEPT는 MySQL 8.0.31 이상에서만 지원한다. (이전 버전에서는 서브쿼리로 대체).
• 전체 결과에 대한 LIMIT는 마지막에 한 번만 사용한다.

 

---

그룹함수

집계함수

여러 행을 바탕으로 하나의 결과 값을 도출해 내기 위해 사용하는 함수로, GROUP BY 절과 사용하기도 하고, 생략되는 경우 조건에 맞는 모든 데이터에 적용된다.

• `SUM`: 지정한 데이터의 합
• `COUNT`: 지정한 데이터 개수
• `MAX`, `MIN`: 최댓값, 최솟값
• `AVG`: 지정한 데이터 평균 값

 

GROUP BY 절

특정 컬럼이나 표현식을 기준으로 데이터를 그룹화하여 소그룹에 대한 통계(집계 함수: SUM, AVG, COUNT 등)를 구할 때 사용한다.

SELECT 절에 그룹함수를 제외한 모든 컬럼, 표현식은 GROUP BY 절에 명시해야 한다.

SELECT SUM(amount), userid 
FROM buyTbl
GROUP BY userid

 

HAVING 절

GROUP BY절과 함께 쓰이며 그룹화된 결과 값의 범위를 제한하는 데 사용한다.

-- 총 구매액이 1000을 초과하는 사용자
SELECT userID, SUM(price*amount) AS '총구매액'
FROM buyTbl
GROUP BY userID
HAVING SUM(price*amount) > 1000
ORDER BY SUM(price*amount);

-- 별칭 사용 예시
SELECT userID, SUM(price*amount) AS total_amount
FROM buyTbl
GROUP BY userID
HAVING total_amount> 1000  -- MySQL에서는 별칭 사용 가능
ORDER BY total_amount;

 

WITH ROLLUP

총합 또는 중간 합계가 필요한 경우 GROUP BY 절과 함께 사용한다.

ROLLUP은 GROUP BY와 함께 쓰기 때문에 HAVING 보다 먼저 써야 한다.

 

GROUP CONCAT (MySQL 전용)

그룹화된 데이터의 문자열 값을 연결할 수 있는 함수이다.

-- 부서별 사원 이름을 콤마로 구분하여 출력
SELECT deptno, GROUP_CONCAT(ename) 
FROM emp
GROUP BY deptno;

-- 구분자 지정 및 정렬
SELECT deptno, GROUP_CONCAT(ename ORDER BY sal DESC SEPARATOR ', ') 
FROM emp
GROUP BY deptno;

실행 결과

쿼리 실행 순서

SELECT 문장의 실행 순서

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT/OFFSET

 

오라클이 별칭을 사용하지 못하는 이유가 바로 이 실행 순서 때문이다.

(select가 where, group by, having보다 나중에 실행됨)

MySql은 그냥 group by, having에서 별칭 사용할 수 있게 해 놓았다.

 

주의사항

• 별칭 사용 가능 위치
  • ORDER BY: 사용 가능
  • WHERE: 사용 불가
  • GROUP BY, HAVING: MySQL에서는 사용 가능
• WHERE vs HAVING
  • WHERE: 개별 행 필터링, 그룹화 전에 적용, 집계함수 사용 불가
  • HAVING: 그룹 필터링, 그룹화 후에 적용, 집계함수 사용 가능
  • WHERE 절은 GROUP BY 전에 실행되므로, 가능한 많은 필터링을 WHERE절에서 수행하는 것이 더 효율적

 

---

주요함수

제어 흐름 함수

• IF(수식, 참, 거짓)
  • 수식 결과에 따라 2중 분기
  • SELECT IF(100>200, ‘참’, ‘거짓’); -> 거짓 반환
• IFNULL(수식1, 수식2)
  • 수식1이 NULL이 아니면 수식1 반환, 수식1이 NULL이면 수식2 반환
  • SELECT IFNULL(NULL, ‘널!’); -> 널! 반환
• NULLIF(수식1, 수식2)
  • 수식1과 수식2가 같으면 NULL, 다르면 수식1을 반환
  • `SELECT NULLIF(100, 100);` -> NULL 반환

IFNULL과 NULLIF가 헷갈리지 않게 조심해야 한다.

왠지 IFNULL을 많이 쓸 것 같다.

• CASE ~ WHEN ~ ELSE ~ END

SELECT
CASE 10
WHEN 1 THEN '일'
WHEN 5 THEN '오'
WHEN 10 THEN '십'
ELSE '글쎄요'
END;  -- '십' 반환

-- 조건식 사용 방식
SELECT ename, sal,
CASE
WHEN sal>= 3000 THEN '상위급여'
WHEN sal>= 2000 THEN '중위급여'
ELSE '하위급여'
END AS '급여등급'
FROM emp;

실행 결과

 

문자열 함수

• CHAR_LENGTH(문자열), LENGTH(문자열)
  • CHAR_LENGTH는 문자의 개수, LENGTH는 할당된 Byte 수를 반환
  • MySQL에서 UTF-8 인코딩을 사용할 경우 영문은 1, 한글은 3바이트
• CONCAT(문자열1, 문자열2, …), CONCAT_WS(구분자, 문자열1, 문자열2, …)
  • 문자열을 연결
• FORMAT(숫자, 소수점 자릿수)
  • 소수점 아래 자릿수까지 숫자 표현하고 1000단위마다 ,로 표시
• 위치 관련 함수
  • ELT(위치, 문자열1, 문자열2, …)
  • FIELD(찾을 문자열, 문자열1, 문자열2, …)
  • FIND_IN_SET(찾을 문자열, 문자열 리스트)
  • INSTR(기준 문자열, 부분 문자열)
  • LOCATE(부분 문자열, 기준 문자열)
  • 잘 안쓰니까 필요할 때 검색해서 쓰기
• INSERT(기준 문자열, 위치, 길이, 삽입할 문자열)
  • 기준 문자열의 위치부터 길이만큼을 지우고 문자열을 끼워 넣음
• LEFT(문자열, 길이), RIGHT(문자열, 길이)
  • 왼쪽 혹은 오른쪽에서 문자열의 길이만큼 반환
• UPPER(문자열), LOWER(문자열)
  • 소문자를 대문자로, 대문자를 소문자로 변경

 

---

❕느낀점

예전에 SQLD를 공부하면서 SQL은 어느정도 알고 있다고 생각했는데 ROLLUP, GROUP_CONCAT 등 처음 보는 것도 있었다. 많이 쓰이지는 않는 것 같지만 알아두면 나쁘지 않을 것 같아서 공부는 해놓아야겠다.