Oracle SQL - 데이터 그룹화, 테이블 조인, CASE WHEN
데이터 그룹화
집계함수
- AVG() : 열의 평균값을 반환한다. (NULL값 존재시 계산에서 제외)
-- DLL01의 공급업체에 대한 제품 평균
SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';
- COUNT() : 열의 행 개수를 반환한다.
- COUNT(*) : 모든 행의 개수 (NULL 포함)
- COUNT(컬럼) : NULL을 제외한 행의 개수.
-- cust_email 열에 값이 있는 고객의 수 계산
SELECT COUNT(cust_email) as num_cust
FROM Customers;
- MAX() : 열의 최대값을 반환한다. (텍스트 사용가능, NULL무시)
- MIN() : 열의 최소값을 반환한다.
--Products 테이블에서 가격이 가장 비싼 / 저렴한 제품의 가격
SELECT MAX(prod_price) AS max_price
FROM Products;
SELECT MIN(prod_price) AS min_price
FROM Products;
- SUM() : 열 값의 합계를 반환한다.
-- 주문번호 20005번에 대한 총 금액 반환
SELECT SUM(item_price * quantity) AS total_price
FROM OrderItems
WHERE order_num = 20005;
- DISTINCT : 중복 제거
-- 같은 가격 중복 제거
SELECT DISTINCT prod_price
FROM Products
WHERE vend_id = 'DLL01';
SELECT 절의 순서
| ↓Optional |
|---|
| SELECT |
| FROM |
| WHERE |
| GROUP BY |
| HAVING |
| ORDER BY |
GROUP BY
집계함수를 사용할 때, 기준이 되는 컬럼을 그룹으로 만들어준다.
GROUP BY 로 묶인 컬럼만 SELECT절에 나타날 수 있다.
SELECT prod_id, SUM(quantity)
FROM OrderItems
WHERE order_num IN (20005,20007)
GROUP BY prod_id
HAVING SUM(quantity) > 100;
하위 쿼리 (서브 쿼리)
하위 쿼리를 먼저 실행하여 결과 추출하고, 그 결과를 바탕으로 메인 쿼리를 실행.
SELECT 컬럼 FROM 집합 WHERE 조건
IN절 안에 하위쿼리 사용하기
-- 상품번호가 RGAN01인 상품을 주문한 고객 ID 추출
SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01');
-- 상품번호가 RGAN01인 상품을 주문한 고객의 이름과 연락처 추출
SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01'));
하위쿼리를 계산 필드로 사용
-- 스칼라 서브쿼리
-- 고객 ID 당 주문 수 추출
SELECT cust_id, cust_name, cust_state, (SELECT COUNT(*)
FROM Orders O
WHERE O.cust_id = C.cust_id) AS orders
FROM Customers C
ORDER BY cust_name;
FROM 절에 SQL 사용
-- 인라인 뷰
-- 제품번호 앞 2자리가 BR인 주문번호, 주문항목, 제품번호 앞 2자리(TYPE)을 추출
SELECT order_num, order_item, type
FROM (SELECT order_num, order_item, SUBSTR(prod_id,1,2)AS type FROM OrderItems)
WHERE type = 'BR';
EXIST, NOT EXISTS
SELECT cust_id, cust_name, cust_address
FROM customers C
WHERE EXISTS(SELECT * FROM Orders O WHERE O.cust_id = C.cust_id);
테이블 조인
cartesian product
연결 관계를 설정하지 않으면 참조된 각 테이블의 row수를 곱한만큼 참조한다.
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products;
ANSI SQL - 내부조인(INNER JOIN) // 모든 DB에서 지원.
-- 벤더ID가 DLL01인 업체의 벤더이름, 제품이름, 제품가격을 출력한다.
SELECT V.vend_name, P.prod_name, P.prod_price
FROM Vendors V INNER JOIN Products P
ON V.vend_id = P.vend_id
WHERE V.vend_id = 'DLL01';
외부조인
외부조인은 연결관계를 충족하지 않는 row를 추출하고 싶을 때 사용한다. ex) 아직 주문하지 않은 사람을 포함한 모든 고객 목록 추출. SELECT C.cust_id, O.order_num FROM Customers C, Orders O WHERE O.cust_id (+)= C.cust_id; // Customers가 메인 테이블.
(+)= 연산자는 오라클 한정 연산자로, 다른 DB를 사용할 경우 ANSI SQL을 이용한다.
-
ANSI SQL 외부조인
- LEFT (OUTER) JOIN
- RIGHT (OUTER) JOIN
- FULL (OUTER) JOIN
메인 테이블의 위치에 따라 LEFT / RIGHT, 모든 테이블을 메인으로 둘 때는 FULL.
쿼리의 결합
UNION / UNION ALL
합집합, 2개의 SQL을 합하여 추출 UNION은 중복을 제거하며, 일반적으로는 UNION ALL을 사용한다. 열의 데이터 형식은 호환되어야 한다. (컬럼 수, 데이터 타입 동일할 것)
// IL, IN, MI 주에 있는 모든 고객과 고객명이 Fun4All이라는 고객의 정보 추출
SELECT * FROM Customers WHERE cust_state IN ('IL', 'IN', 'MI')
UNION ALL
SELECT * FROM Customers WHERE cust_name = 'Fun4All'
ORDER BY cust_name, cust_contact;
MINUS
차집합. 첫번째 집합에만 존재하는 행 리턴.
INTERSECT
교집합. 두 집합에 공통된 행을 반환.
CASE WHEN
SELECT CASE WHEN 조건1 THEN 참일때 결과 WHEN 조건2 THEN 참일때 결과 ELSE 조건 모두 불만족 시 결과 END FROM …
– 공급업체별 주 정보를 컬럼으로 표시
SELECT vend_id, vend_state,
CASE WHEN vend_state = 'MI' THEN 1 ELSE 0 END AS MI_CNT,
CASE WHEN vend_state = 'OH' THEN 1 ELSE 0 END AS OH_CNT,
CASE WHEN vend_state = 'CA' THEN 1 ELSE 0 END AS CA_CNT,
CASE WHEN vend_state = 'NY' THEN 1 ELSE 0 END AS NY_CNT
FROM vendors;
– 공급업체의 주별 수를 컬럼으로 분리 추출
SELECT SUM(CASE WHEN vend_state = 'MI' THEN 1 ELSE 0 END) AS MI_CNT,
SUM(CASE WHEN vend_state = 'OH' THEN 1 ELSE 0 END) AS OH_CNT,
SUM(CASE WHEN vend_state = 'CA' THEN 1 ELSE 0 END) AS CA_CNT,
SUM(CASE WHEN vend_state = 'NY' THEN 1 ELSE 0 END) AS NY_CNT
FROM vendors;
Comments