4-7강 SQL 응용-응용 SQL 작성

과목4. 데이터베이스 구축, 7강. SQL 응용-응용 SQL작성

 

[ 목차 ]

1. 집계성 SQL

2. 집계 함수

3. 그룹화

4. 그룹 함수

5. 윈도우 함수

6. 순위 관련 함수 특징

7. 집계 관련 함수

8. 행 순서 관련 함수

9. 비율 관련 함수

 

1. 집계성 SQL

1) 총합, 평균 등의 데이터 분석을 위해 복수 개의 행을 기준으로 데이터를 분석하는 SQL문

2) 집계 함수, 그룹 함수, 윈도우 함수

 

2. 집계 함수

1) GROUP BY 절과 함게 사용되어 복수 행에 대한 개수, 합계, 평균, 최소, 최대값 등을 계산(그룹 함수와 유사하거나 포함되는 개념)

2) SELECT 과목, COUNT(*) AS 수강생수 FROM 수강생 WHERE 수강료>=100 GROUP BY 과목 HAVING COUNT(*)>=2;

HAVING 절을 이용해 검색 결과로 나타날 그룹 제한 가능

3) COUNT, SUM, AVG, MAX, MIN ,STDDEV(표준편차), VARIANCE(분산값)가 존재

4) 특징 

  1] 한 개의 애트리뷰트(속성)에 적용되어 단일 값을 반환

  2] SELECT 절과 HAVING 절에만 나타남

  3] COUNT(*)를 제외한 모든 집계 함수는 널값을 제거한 후 계산

  4] COUNT(*)는 결과 릴레이션의 모든 행들위 총 개수를 반환(널 포함)

  5] COUNT(속성)은 널 값이 아닌 값들의 개수를 반환

 

3. 그룹화

1) GROUP BY 절을 이용하여 분석할 데이터 행을 분류할 기준을 정의

2) GROUP BY 절에 사용된 속성에 동일한 값을 갖는 튜플들이 각각 하나의 그룹으로 묶여서 각 그룹별로 하나의 결과를 생성

3) SELECT절에 집단 함수, 그룹함수에 사용된 속성들만 나타날수 있음

 

4. 그룹 함수

1) 소크룹의 소계, 총계와 같이 레벨별로 집계 결과를 조회하기 위해 사용

2) ROLLUP, CUBE, GROUPING SETS 함수가 대표적

3) ROLLUP( ) 함수

  1] 소계 등 중간 집계값을 산출하기 위해 사용(총계 값이 추가)

  2] 칼럼의 수보다 하나 더 큰 레벨 만큼의 중간 집계값을 생성

  3] ROLLUP의 지정 칼럼은 계층별로 구성되기 때문에 순서가 바뀌면 수행 결과가 바뀜

  4] SELECT 그룹핑칼럼, 집계함수 FROM 테이블명 [WHERE] GROUP BY 그룹핑칼럼 ROLLUP (소계계산칼럼)  [HAVING]

4) CUBE( ) 함수 - 다차원 그룹별 소계

  1] 결합 가능한 모든 값에 대해 다차원 집계를 생성하는 그룹 함수

  2] 내부적으로 대상 칼럼의 순서를 변경하여 또 한번의 쿼리를 수행하고 총계는 양쪽 쿼리에서 모두 수행 후 한족에서 제거

  3] ROLLUP에 비해 계산이 많음

  4] SELECT 그룹핑칼럼, 집계함수 FROM 테이블명 [WHERE] GROUP BY 그룹핑칼럼 CUBE (소계계산칼럼)  [HAVING]

OLAP : 온라인 분석 처리
1) 데이터 웨어하우스를 다차원적으로 분석하고 시각화하는 과정
  EX. 수강생 수를 강의별 or 지역별 or 기간별 등 다양한 차원에서 즉시 분석

5) GROUPING SETS( ) 함수

  1] 집계 대상 칼럼에 대한 개별 집계를 구하고 ROLLUP이나 CUBE와는 달리 칼럼 간 순서와 무관한 결과를 생성

  2] GROUP BY 한 SQL들을 UNION ALL 해서 보여주는 결과와 같음

  3] SELECT 그룹핑칼럼, 집계함수 FROM 테이블명 [WHERE] GROUP BY GROUPING SETS(그룳핑칼럼)[HAVING]

 

5. 윈도우 함수

1) 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수

2) = 분석 함수, 순위 함수 OLAP 함수, RANK 함수, ANALYTIC 함수

3) 집계 함수나 순위 함수 등을 이용해 그룹을 나눠 분석을 수행

4) 순위 함수 : RANK, DENSE_RANK, ROW_NUMBER

  ex. SELECT 사번, 나이 RANK( ) OVER(ORDER BY 나이 DESC) AS 순위 FROM 부서;

  ex. SELECT 사번, 나이 RANK( ) OVER(PARTITION BY 부서 ORDER BY 나이 DESC) AS 순위 FROM 부서;

5) 집계 함수 : SUM, MAX, MIN, AVG, COUNT

6) 행 순서 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD

7) 비율관련 함수 : CUME_DIST, PERCENT_RANK, NTILE

WINDOOW_FUNCTION : 순위, 집계, 행순서, 비율 관련 윈도우 함수를 기술

PARTITION BY : 전체 집합을 소그룹으로 나누기 위한 기준 칼럼을 기술

ORDER BY : 어떤 항목에 대해 순위를 지정할 지 기준 칼럼을 기술

WINDOWING : 함수의 대상이 되는 행 기준의 범위를 ROWS 또는 RANGE로 지정, ROWS는 현재 행의 앞뒤 행들의 범위로 지정(물리적 순서)하고 RANGE는 현재 행의 데이터 값을 기준으로 앞뒤 데이터 값의 범위(논리적 순서)를 표시

WINDOWING 절 
1) ROWS BETWEEN CURRENT ROW AND INDBOUNDED FOLLOWING : 현재 행을 포함한 마지막 행까지

2) ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING : 현재 파티션 내에서 앞의 한건, 현재 행, 뒤의 한 건

3) RANGE UNBOUNDED PRECEDING : 현재 행을 기준으로 파티션 내의 첫 번째 행까지
 
4) RANGE BETWEEN 50 PERCEDING AND 150 FOLLOWING : 현재 행 값을 기준으로 -50에서 +150의 범위


CURRENT ROW : 파티션 내에서 현재 행을 시작 행 또는 마지막 행으로 이용
UNBOUNDED FOLLOWING : 파티션 내에서 지정된 행 이후의 모든 행을 포함
1 PRECEDING : 이전 1건
1 FOLLOWING : 이후 1건
UNBOUNDED PRECEDING : 파티션 내에서 지정된 행 이전의 모든 행을 포함

6. 순위 관련 함수 특징

1) RANK( )

  1] 동일한 값에 대해서는 동일한 순위를 부여

2) DENSE_RANK( ) 

  1] 동일한 순위를 하나의 건수로 간주

3) ROW_NUMBER( ) 

  1] 동일한 값이라도 고유한 순위를 부여

 

7. 집계 관련 함수

1) 분석할 대상 파티션을 나눈 후 집계 함수를 이용해 분석을 수행

 

8. 행 순서 관련 함수

1) FIRST_VALUE( ) : 파티션별 윈도우에서 가장 먼저 나온 값을 구함(= MIN)

2) LAST_VALUE( ) : 파티션별 윈도우에서 가장 마지막에 나온 값을 구함(= MAX)

3) LAG( ) : 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져옴

4) LEAD( ) : 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져옴

 

9. 비율 관련 함수

1) PERCENT_RANF( ) : 파티션별로 윈도우에서 제일 먼저 나온느 것을 0, 제일 늦게 나오는 것을 1로 하여 값이 아닌 ㅐ행의 순서별 백분율을 구하고, 순서상 몇 번째 위치인치 이로 표현

2) CUME_DIST( ) : 파티션별 윈도우에서 전체건수에 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 구함

3) INTLE( ) : 파티션별 전체 건수를 ARGUMENT 값으로 N등분한 결과로, 출력 결과를 사용자가 지정한 그룹 수로 나누어 출력

4) RATO_TO_REPORT( ) : 파티션 내 전체 SUM(칼럼) 값에 대한 행별 칼럼 값을 백분율을 소수점으로 구함