쿼리 구조화, CTE 문법 (BDA학회 SQL 문법 기초 연습반)
쿼리 구조화
1. 결과와 과정
우리는 쿼리를 만들 때, 결과와 과정을 생각해야한다.
결과 : 해당 쿼리를 통해 무엇을 얻고자 하는지 => 비즈니스 요구사항
과정 : 답을 찾아가는 과정에서 우리는 어떤 데이터, 조건, 컬럼이 필요한지, 사용해야하는지에 대한 흐름 설계 => 쿼리 조건
2. 최소한의 필수 조건 5가지
우리가 이것들을 어떻게 접근할 것인지,, 질문을 구조화하기 전에 최소한의 필수 조건 5가지가 있다.
- 원하는 요구사항을 정확하게 파악
- 원하는 요구사항이 DB 어디에 있는지 정확하게 파악
- DB에 대한 로직 필수적으로 이해
- 요구사항에 필요한 조건 고려
- 쿼리 역량
결국 데이터를 추출하고 제공해야하기 때문에 어떤 조건이 필요하고, 검증 방법등에 대해 잘 고민해야한다.
3. 현재와 실무의 차이점
현재 우리가 연습하는 것들은 매월 회원/비회원 고객의 주문 건수와 주문금액을 비교하여 둘의 차이를 분석하는 것이지만,,
실무에서는 CRM 액션 진행 시 트리거 포인트를 찾아야 한다는 점이다. 세그별로 유입 후 액션을 확인하여 트리거 포인트를 확인했을 때
- 유입 후 어떤 액션을 많이 하는지?
- 액션 후 어디서 이탈하는지?
- 등...
4. 효율적인 쿼리를 위한 질문들
효율적인 쿼리란
비용 : 쿼리 런타임 증이 최송한으로 가져갈 수 있는 쿼리
효율적인 쿼리를 위해 질문을 계속 고민해야 한다.
정해진 질문들은 없다. 하지만 대표적인 질문들은 있다.
- 정확히 요구하는 것이 무엇인지 => 결국 내가 추출하는 것이 무엇인가
- 어떤 테이블을 사용해야 하는가
- 어떤 조건들이 필요할까
- 어떤 방법으로 검증을 할까
5. 쿼리 구조화 잡기
내가 필요한 테이블과 컬럼을 계속 확인하며 확장해 나간다.
어떤 조인으로 들어갈 것인지 join key를 확인한다.
join을 통한 데이터 값을 검증하며 확장해 나간다.
필요 컬럼과 조건 등을 고려하여 확장해 나간다.
계속해서 검증하며 원하는 쿼리를 만들어 나가야 한다.
번외. 기본적인 쿼리문들
| SELECT | 반환할 열을 지정하는 단계 | JOIN | 두 개 이상 테이블 겨합 |
| FROM | 데이터를 가져올 테이블을 명시하는 단계 | SubQuery | 하위 SQL문 |
| WHERE | 특정 조건에 맞는 데이터를 필터링하는 단계 | Set Operators | Union, Union All, Intersect |
| GROUP BY | 데이터를 그룹화하여 집계하는 단계 | CASE, IF | 조건문 |
| HAVING | 그룹화된 데이터에 추가 조건을 적용하는 단계 | Window Functions | RANK, PARTION BY |
| ORDER BY | 데이터를 정렬하는 단계 | CTE | Common Table Expression |
CTE : Common Table Expression
임시 결과 집합
데이터베이스 쿼리에서 생성된 일시적인 데이터의 집합
CTE 공통 테이블 표현식
쿼리 내에서 임시 결과 집합을 정의할 떄 사용하는 구문
CTE 장단점
장점
- 재사용 가능 : CTE는 여러 번 사용될 수 있으며, 같은 쿼리 내에서 여러번 참조할 수 있다.
- 가독성 향상 : 복잡한 쿼리를 더 잘 구조화하고, 이해하기 쉽게 만들 수 있다.
- 스코프 제한 : CTE는 정의된 쿼리 내에서만 유효하다.
- 재귀 CTE : CTE는 재귀적으로 정의할 수 있다. 계층적 데이터를 처리하는데 유용하다. 예) 조직도, 파일 시스템 구조 탐색
단점
- 성능 : 복잡한 CTE는 성능에 영향을 줄 수 있다. 일부 DB에서는 CTE를 별도의 쿼리로 처리할 수 있어 비효율적일 수도 있다.
- 제한된 지원 : 모든 데이터베이스 시스템이 CTE를 지원하는 것은 아니다.
CTE 문법
CTE는 WITH 키워드를 사용하여 정의한다.
1. 기본 문법
WITH cteName AS (
SELECT column1, column2
FROM tableName
WHERE condition
)
SELECT *
FROM cteName
2. CTE 활용 사용 예시
-- 성적이 80점 이상인 학생들을 선택
WITH
cte_학생 AS (
SELECT 이름, 성적
FROM table_학생정보
WHERE 성적 >= 80
)
SELECT *
FROM cte_학생
3. 여러 CTE 사용
-- 상위학생과 중위학생의 정보를 함께 조회
WITH
cte_상위학생 AS (
SELECT 이름, 성적
FROM table_학생정보
WHERE 성적 >= 90
),
cte_중위학생 AS (
SELECT 이름, 성적
FROM table_학생정보
WHERE 80 <= 성적 AND 성적 < 90
)
SELECT * FROM cte_상위학생
UNION ALL
SELECT * FROM cte_중위학생
4. CTE와 재귀 쿼리
-- 최상위 부서부터 하위 부서를 재귀적으로 조회
WITH RECURSIVE cte_부서 AS (
SELECT 부서_ID, 상위_ID
FROM table_부서정보
WHERE 상위_ID IS NULL -- 최상위 부서 찾기
UNION ALL
SELECT b.부서_ID, b.상위_ID
FROM table_부서정보 b
INNER JOIN 부서 d ON b.상위_ID = d.부서_ID
)
SELECT * FROM cte_부서;
+ 번외편 :
재귀가 무엇일까
재귀 : 어떤 과정이나 함수가 자기 자신을 호출하는 것. 즉, 문제를 해결하기 위해 같은 방법을 반복적으로 사용하기에 복잡한 구조를 다루는 데에 유용하다.
재귀적 조회 : DB에서 어떤 정보를 찾기 위해 자기 자신을 반족해서 조회하는 것. 주로 나무처럼 갈라지는 구조를 다룰 때 쓰인다.
예) 부서 A
/ \
부서 B 부서 C
|
부서 D
CTE 사용 여부에 따른 코드 차이
CTE를 사용한 경우
WITH cte_Sales AS (
SELECT SUM AS
FROM table_sales
GROUP BY product_id
)
SELECT p.product_name, s.total_sales
FROM products p
JOIN cte_Sales s ON p.product_id = s.product_id;
CTE를 사용하지 않은 경우 (서브쿼리 사용)
SELECT p.product_name,(
SELECT SUM(sale_amount)
FROM sales
WHERE product_id = p.product_id
GROUP BY product_id
) AS total_sales
FROM products p;
...
CTE를 사용한 경우
WITH SalesCTE AS (
SELECT
product_id,
SUM(sale_amount) AS total_sales,
AVG(sale_amount) AS average_sales
FROM sales
GROUP BY product_id
)
SELECT
p.product_name,
s.total_sales,
s.average_sales
FROM products p
JOIN SalesCTE s ON p.product_id = s.product_id
WHERE s.total_sales > 1000;
CTE를 사용하지 않은 경우 (서브쿼리 사용)
SELECT p.product_name,
(SELECT SUM(sale_amount)
FROM sales
WHERE product_id = p.product_id) AS total_sales,
(SELECT AVG(sale_amount)
FROM sales
WHERE product_id = p.product_id) AS average_sales
FROM products p
WHERE (SELECT SUM(sale_amount)
FROM sales
WHERE product_id = p.product_id) > 1000;
차이점
- 가독성
CTE: 쿼리 구조가 명확하고 각 부분이 독립적으로 정의되어 가독성이 높습니다.
서브쿼리: 서브쿼리가 결과를 반환하는 방식이 복잡해 보일 수 있어 이해하기 어려울 수 있습니다. - 재사용성
CTE: 정의된 CTE는 쿼리 내에서 여러 번 참조할 수 있습니다. 예를 들어, 다른 JOIN이나 필터링 작업에서 같은 CTE를 재사용할 수 있습니다.
서브쿼리: 서브쿼리는 해당 쿼리 내에서만 사용되며, 다른 쿼리에서 재사용할 수 없습니다. - 성능
CTE: 데이터베이스는 CTE를 한 번 계산하고 그 결과를 재사용하기 때문에, 특히 큰 데이터셋에서 성능이 더 나을 수 있습니다.
서브쿼리: 서브쿼리는 메인 쿼리에서 각 행에 대해 반복적으로 실행될 수 있으므로, 성능이 저하될 수 있습니다. - 정리 및 유지보수
CTE: 코드가 분리되어 있어 유지보수가 쉽고, 쿼리의 각 부분이 어떤 역할을 하는지 이해하기가 더 용이합니다.
서브쿼리: 코드가 길어지고 복잡해지면, 유지보수가 어려워질 수 있습니다.
...
결론.
가장 높은 매출을 기록한 상위 5명 고객과 그 매출의 비율 분석을 할때 전체의 얼마나 비율을 가지고 있는지 계산할떄
GROUP BY를 사용한다.
직원별로 어떤 고객이 재구매 했는지, 어떤 물건을 재구매 했는지, 등등 공동테이블을 사용하냐 마냐의 차이가 생긴다.
...
