<SQLD 39> 데이터 분석 함수에 대한 기출문제

매일 SQLD 기출 문제 풀기 챌린지

---

 

문제 39.

다음 보기 중 아래의 SQL에 대한 설명으로 가장 올바른 것은?

SELECT 분류코드,
       AVG(상품가격) AS 상품가격,
       COUNT(*) OVER
       (
       ORDER BY AVG(상품가격)
                RANGE BETWEEN 10000
                PRECEDING AND 10000 FOLLOWING
       )AS CNT
FROM 상품
GROUP BY 분류코드;

 
①   WINDOW FUNCTION을 GROUP BY(분류코드) 절과 함께 사용하였으므로 위의 SQL은 오류가 발생한다.
②   WINDOW FUNCTION의 ORDER BY 절로 인하여 문법오류이다.
③   CNT 칼럼은 분류코드별 평균상품가격을 서로 비교하여 -10000 ~ 10000 사이에 존재하는 분류코드의 개수를 구한 것이다.
④   CNT 칼럼은 상품전체의 평균상품가격을 서로 비교하여 -10000 ~ +10000 사이에 존재하는 상품의 개수를 구한 것 이다

---

해설해보기

제시된 쿼리는 "윈도우 함수(WINDOW FUNCTION)"와 "GROUP BY"절을 함께 사용하여 분류코드별 평균 상품 가격의 분포를 분석합니다. 

 

 ■ 윈도우 함수(WINDOW FUNCTION) 란? 

데이터의 일련의 행들에 대해 계산을 수행하되, 그 결과를 각 행에 대해 독립적으로 반환할 수 있게 해줍니다.
이때 "윈도우"라는 것은 계산에 사용되는 행들의 집합을 의미합니다.

즉, 특정 창(Window) 또는 그룹 내에서 데이터를 처리하고 계산하는 데 사용됩니다.
일반적으로 데이터 집합을 여러 그룹으로 나눌 필요없이 전체 데이터 집합에 대해 작동합니다.
따라서 데이터를 집계하는 GROUP BY와는 다르게 , 그룹화 하지 않고도 개별 행에 대한 계산 결과를 제공합니다.

윈도우 함수는 OVER절을 사용하여 정의하고,
윈도우의 파티션(분할 기준), 정렬 순서(ORDER BY), 윈도우 프레임(계산 대상 행의 범위) 등을 지정할 수 있습니다. 
주로 아래와 같은 상황에서 사용합니다.

-  순위를 계산하는 함수 : RANK(), DENSE_RANK(), ROW_NUMBER() 등
-  누적 합, 평균, 최소값, 최대값 계산: `SUM()`, `AVG()`, `MIN()`, `MAX()` 등

 

< 1번 >

" WINDOW FUNCTION을 GROUP BY(분류코드) 절과 함께 사용하였으므로 위의 SQL은 오류가 발생한다. "

 

이 설명은 거짓입니다.

윈도우 함수는 GROUP BY절과 함께 사용 가능합니다. 

이 때 GROUP BY 절이 먼저 수행되어 데이터를 분류한 후

윈도우 함수를 사용하여 각 그룹 내에서 추가적인 계산을 수행할 수 있습니다.
 
 < 2번 > 

" WINDOW FUNCTION의 ORDER BY 절로 인하여 문법오류이다."

 

이 설명은 거짓입니다.

윈도우 함수 내에서 ORDER BY절을 사용할 수 있습니다. 

함께 사용할 경우 데이터를 특정 순서에 따라 정렬한 후,  윈도우 함수를 적용하려 할 때 사용합니다.

 

제시된 쿼리에서는 AVG(상품가격)을 기준으로 정렬하여 평균 상품 가격에 따라 순서를 정한 뒤

윈도우 함수인 COUNT를 적용합니다.

 

 

< 3번 >

" CNT 칼럼은 분류코드별 평균상품가격을 서로 비교하여 -10000 ~ 10000 사이에 존재하는 분류코드의 개수를 구한 것이다. "

 

이 설명은 참입니다.

COUNT(*) OVER
  ( ORDER BY AVG(상품가격)
       RANGE BETWEEN 10000
       PRECEDING AND 10000 FOLLOWING )

 

이 구문을 사용하여 각 분류코드별 평균상품가격을 기준으로 해당 평균 가격에서 -10000 ~ +10000 범위 내에 있는 다른 분류 코드의 평균 가격을 가진 행의 수를 세게 됩니다.

 

`RANGE`, `PRECEDING`, `FOLLOWING` 등의 구문은 특정 행에 대해 계산될 값들의 범위를 지정하는 데 사용됩니다.

이러한 구문은 윈도우 함수의 `OVER` 절 내에서 정렬된 데이터 집합을 기준으로 특정 범위의 데이터에 대해 연산을 수행할 수 있도록 합니다.

■ COUNT(*)
현재 윈도우(또는 파티션) 내의 행 수를 세는 데 사용됩니다. " * "는 모든 행을 대상으로 카운트를 수행함을 의미합니다.

■ OVER 
윈도우 함수가 작동할 "윈도우" 또는 데이터의 범위를 정의하는 시작점입니다.

■ ORDER BY AVG(상품가격)
이 구문은 윈도우 내에서 데이터를 "상품가격"의 평균에 기반하여 정렬하라는 의미입니다.
이는 "COUNT(*)"가 적용되는 데이터 집합의 순서를 결정합니다.

■ RANGE
이 키워드는 현재 행을 기준으로 특정 범위 내의 행들을 윈도우 함수의 계산에 포함시키라는 것을 의미합니다.
"RANGE"는 현재 행의 정렬 키 값과 정확히 같거나 주어진 범위 내에 있는 모든 행을 포함합니다.

■  PRECEDING와 FOLLOWING
이 구문들은 현재 행의 정렬 키 값에 대한 상대적인 위치를 지정합니다.
"10000 PRECEDING"은 현재 행의 "AVG(상품가격)" 보다 10000 더 작거나 같은 값을 가지는 행들을 포함하라는 의미이며, "10000 FOLLOWING"은 현재 행의 "AVG(상품가격)" 보다 10000 더 크거나 같은 값을 가지는 행들을 포함하라는 의미입니다.

 

즉, 각 분류코드의 평균 상품가격 주변에 얼마나 많은 분류코드의 평균 상품가격이 분포하는지를 나타냅니다. 

 

정리하면, 이 구문은 각 행에 대해 상품가격의 평균을 기준으로 정렬된 데이터 집합에서, 현재 행의 평균 상품가격보다 10000 더 낮거나 같고 10000 더 높거나 같은 범위에 있는 모든 행들을 대상으로 행의 수를 세는 연산을 수행합니다.

 

 

< 4번 >

" CNT 칼럼은 상품전체의 평균상품가격을 서로 비교하여 -10000 ~ +10000 사이에 존재하는 상품의 개수를 구한 것 이다 "

 

이 설명은 거짓입니다.

제시된 쿼리는 상품의 평균 가격을 비교하는 것이 아니라, 

분류코드별로 그룹화된 상품의 평균 가격을 기준으로 계산을 수행합니다. 

 

따라서 CNT 컬럼은 전체 상품을 대상으로 하지 않고,

각 분류코드별 평균 상품가격을 기준으로 한 범위 내의 분류코드 개수를 구하는 것 입니다.

 

---

정답

 

③

---