[SQL] 데이터 캠프 수강 (4) - Intermediate SQL

CASE statements

• SQL 버전의 IF this THEN that 문이다.
• WHEN, THEN, ELSE로 IF-ELSE 조건을 만들고 END로 마무리 한다.
  • 여러 조건을 줄 경우 WHEN 절에서 AND를 사용
  • ELSE 절에 값을 주지 않을 경우, NULL 값 반환
    • END IS NOT NULL을 지정해주면 NULL 값이 아닌 경우를 반환한다.
    • SELECT에 CASE를 사용할 경우, ALIAS를 지정해 주어야 하고, WHERE에 CASE를 사용할 경우, ALIAS를 지정해 줄 필요가 없다.

• 데이터의 범주화, 데이터 필터링, 데이터 집계를 하는데 유용함
  • 집계 함수 내에서 CASE문을 활용 가능하다.

• 조건 만족의 결과 값으로 특정 컬럼을 지정해주면 해당 컬럼의 값을 반환해준다.
• AVG 함수 내에 CASE WHEN 절을 사용하여, 퍼센트를 구할 수 있다.
  • 해당 조건을 만족하는 경우 1, 그렇지 않은 경우 0을 지정하여 평균을 구하면 된다.

---

SUBQUERY

• 정의 : 자체적으로 실행할 수 있는 다른 쿼리 내에 중첩된 쿼리
• SELECT, FROM, WHERE, GROUP BY 등에 모두 사용할 수 있다. 
• ( ) 안에 쿼리를 입력하여 필터링 된 데이터 추출 및 변환 등을 할 수 있다.
• 서브쿼리를 사용하는 이유는 요약된 값을 데이터와 비교 가능하기 때문
• SQL은 서브 쿼리 처리 후, 나머지 쿼리를 처리하는 순서를 가진다.

WHERE

• WHERE절에 서브 쿼리를 사용함으로써, JOIN을 하지 않더라도 필터링이 가능하다.

FROM

• FROM 절에 서브 쿼리를 사용함으로써, 데이터를 재구성하고 변환한다. 
• 집계 함수 사용에 유용하다.
• FROM 절에서 하나 이상의 서브 쿼리를 만들 수 있다.
  • ALIAS를 지정해주어야 한다.
• 데이터베이스의 기존 테이블에 서브 쿼리를 조인할 수 있다.
  • 각 테이블에서 조인할 컬럼을 모두 가지는지 확인해야 함.

SELECT

• SELECT 절에 서브 쿼리는 단일 집계 값을 반환하는데 사용
• SELECT에 서브 쿼리 사용 시 주의점
  • 서브 쿼리는 단일 값을 반환해야 한다. 

---

Correlated SUBQUERY

• main query에 존재하는 하나 또는 둘 이상의 열을 참조하는 쿼리이다. 
  • 다른 열의 데이터를 일치하는데 적합하다.
  • 이는 기본 쿼리에 종속되는 쿼리로 자체적으로 실행할 수 없다.
  • 데이터들의 각 행을 참조하며 실행되기 때문에 시간이 오래 걸린다.

Nested SUBQUERY

• 여러 단계의 변환을 세분화하여 작성한 쿼리이다. 즉, 서브 쿼리 내에 서브 쿼리가 존재하는 경우를 의미한다.
  • 세분화로 인해 정확도와 재현성이 향상된다. 
  • 기본 쿼리에 종속되지 않음으로 자체적으로 실행할 수 있다.

Common table Expressions

• 쿼리 시작시 서브 쿼리를 활용해 테이블을 선언하여 활용한다.
  • 많은 수의 서로 다른 정보를 비교하는데 좋다.  
  • Nested Subquery의 대안으로 사용된다.
  • 서브 쿼리를 가독성 있게 작성할 수 있다.
  • WITH name AS ( subquery ) 로 CTE 만든다.
  • 여러개의 CTE를 생성하는 경우 ',' 를 이용해 나열하면 된다.
  • CTE는 메모리에 저장되므로 참조 가능하고 쿼리 실행 속도를 향상할 수 있다.
  • SELF JOIN을 통해 자신을 참조할 수 있다.

WITH s AS (
	SELECT col1, col2
    FROM tableA as A
    WHERE ~~
)

SELECT table2.col3
	COUNT(s.col1)
FROM tableB as B
INNER JOIN B
ON A.col2 = B.col3

WITH s1 AS (
	SELECT col1, col2
    FROM tableA as A
    WHERE ~~
),
s2 AS ( sub query );

---

Window Functions

집계 값과 집계되지 않은 값을 비교할 수 없다. 이 때, Window Function을 사용하여 해결할 수 있다.

 

• 이미 생산된 결과 셋(Window) 에 대해 계산을 수행하는 함수 클래스이다.
• 집계 계산 수행
  • SELECT 내의  subquery와 유사하다.
  • 누적 합계, 순위 등의 계산을 하는데 유용하다.
• OVER 절을 사용하여 Window Function을 표시한다.
  • SELECT에서의 subquery  결과와 유사하게 작동한다.
  • 순위 : RANK() OVER(ORDER BY)
  • PARTITION BY
    • 여러 Category에 대한 별도의 값을 계산할 수 있다.
    • OVER(PARTITION BY column1, column2 ...)
• Window Function은 최종 ORDER BY 문을 제외한 전체 쿼리의 실행 후에 처리 된다.
  • 데이터베이스를 직접 사용하는 대신 결과 집합을 사용하여 계산한다.
  • SQLite에서는 사용할 수 없다. 나머지 Oracle 등에서는 사용 가능하다.
• Sliding Windows
  • 현재 행을 기준으로 계산을 수행하는 함수이다.
  • 한 행을 집계하는 다양한 정보를 누적하여 계산할 수 있다.
  • OVER() 내에서 사용한다.
  • ROWS BETWEEN <start> AND <finish>
    • PRECEDING, FOLLOWING : 행의 수를 지정하는데 사용한다. (계산에 포함 할 현재 행의 앞 또는 뒤)
    • UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING : 지정된 행 이전 or 이후의 모든 행을 지정한다.
    • CURRENT ROW : 현재 행에서 계산을 중지하는데 사용한다.

*EXTRACT (MONTH from date) : 날짜에서 월을 추출해준다.