SQLD · SQL 개발자
그룹 함수와 윈도우 함수
집계는 행을 줄이고 윈도우 함수는 행을 유지합니다. 이 한 줄이 두 영역을 가르는 기준이고, 시험은 바로 그 경계를 묻습니다. ROLLUP·CUBE·GROUPING SETS 같은 소계 함수와 RANK 계열 순위 함수가 특히 자주 나옵니다.
이 주제에서 다루는 내용
- ROLLUP, CUBE, GROUPING SETS 가 만들어 내는 소계 행의 차이
- RANK, DENSE_RANK, ROW_NUMBER 의 동점 처리 차이
- PARTITION BY 와 GROUP BY 의 차이
- LAG, LEAD, FIRST_VALUE, LAST_VALUE
- 집계 함수의 NULL 취급 — COUNT(*) 와 COUNT(컬럼)
자주 틀리는 지점
- RANK 와 DENSE_RANK 를 바꿔 고르는 실수. 동점 뒤 번호가 건너뛰면 RANK 입니다.
- COUNT(컬럼) 이 NULL 을 센다고 보는 실수. COUNT(*) 만 전체 행을 셉니다.
- CUBE 와 ROLLUP 의 소계 조합 수를 거꾸로 세는 경우.
연습문제 3문항
아래 문항은 오리지널 문제입니다. 기출문제가 아닙니다.
다음 중 성능/정합성 관점에서 올바른 판단은? 아래 SALES 테이블에서 월별 평균 판매량이 100 이상인 월의 총 판매량을 조회하는 쿼리들이다. SALES 테이블: | month | product_id | quantity | |-------|------------|----------| | 1 | A | 80 | | 1 | B | 120 | | 1 | NULL | 50 | | 2 | A | 90 | | 2 | B | 110 | | 3 | A | 70 | | 3 | B | 130 |
- A. WHERE 절에서 AVG(quantity) >= 100 조건을 사용하면 집계 함수 실행 전 필터링이 가능하므로 성능상 유리하다.
- B. HAVING 절에서 AVG(quantity) >= 100 조건을 사용해야 하며, NULL 값은 AVG 계산에서 자동 제외되므로 1월 평균은 100이다.
- C. GROUP BY month HAVING COUNT(*) >= 3 조건을 추가하면 데이터가 충분한 월만 선별할 수 있어 더 정확한 결과를 얻는다.
- D. WHERE quantity IS NOT NULL 조건을 먼저 적용한 후 GROUP BY month HAVING AVG(quantity) >= 100을 사용하는 것이 가장 적절하다.
정답: B
해설. 집계 함수 조건은 WHERE가 아닌 HAVING 절에서만 사용 가능하다. AVG 함수는 NULL 값을 자동으로 제외하므로, 1월의 경우 (80+120+50)/3이 아닌 (80+120)/2=100이 된다. A는 WHERE 절에서 집계 함수를 사용할 수 없어 문법 오류이고, C는 COUNT(*)가 3 이상인 조건과 평균 조건은 별개 문제이며, D는 NULL 제외가 불필요하다.
다음 제품 재고 테이블과 SQL문을 보고, 실행 결과로 옳은 것은? [INVENTORY 테이블] PRODUCT_ID | CATEGORY | STOCK_QTY | WAREHOUSE_ID -----------+----------+-----------+------------- P001 | 부품 | 150 | W01 P002 | 부품 | 200 | W01 P003 | 완제품 | 80 | W02 P004 | 완제품 | 120 | W02 P005 | 부품 | NULL | W03 [SQL문] SELECT CATEGORY, AVG(STOCK_QTY) FROM INVENTORY GROUP BY CATEGORY;
- A. 부품: 175, 완제품: 100
- B. 부품: 116.67, 완제품: 100
- C. 부품: 140, 완제품: 100
- D. 부품: NULL, 완제품: 100
정답: A
해설. AVG 함수는 NULL 값을 제외하고 계산합니다. 부품 카테고리의 경우 P001(150), P002(200)만 계산되어 (150+200)/2 = 175가 됩니다. P005의 NULL 값은 평균 계산에서 제외됩니다. 완제품 카테고리는 P003(80), P004(120)로 (80+120)/2 = 100입니다. 옵션 B는 NULL을 0으로 처리한 오답이고, 옵션 C는 전체 평균을 구한 오답, 옵션 D는 NULL이 있으면 결과도 NULL이라는 잘못된 이해입니다.
제조회사의 생산실적 테이블 PRODUCTION(공정별 생산량)에 대해 아래 SQL을 실행하였다. SUM(QTY) OVER (...) 윈도우 함수 결과에 대한 설명으로 옳은 것은? PRODUCTION | LINE | PROD_DATE | QTY | |------|-----------|-----| | A | 20240101 | 100 | | A | 20240102 | 200 | | B | 20240101 | 300 | | B | 20240102 | 400 | SELECT LINE, PROD_DATE, QTY, SUM(QTY) OVER (PARTITION BY LINE) AS LINE_SUM FROM PRODUCTION;
- A. PARTITION BY로 인해 그룹화되어 결과가 LINE별 2행으로 축약된다.
- B. GROUP BY 절이 없으므로 윈도우 함수는 문법 오류가 발생한다.
- C. 모든 행의 LINE_SUM이 전체 합계인 1000으로 동일하게 출력된다.
- D. 결과는 원본 4행이 그대로 유지되며, LINE_SUM은 A라인 행에서 300, B라인 행에서 700이 된다.
정답: D
해설. 윈도우 함수는 GROUP BY 없이도 단독 사용 가능하며 행을 축약하지 않는다. PARTITION BY LINE으로 A라인은 100+200=300, B라인은 300+400=700이 각 행에 부여되어 원본 4행이 유지된다. A는 행 축약 오해, B는 GROUP BY 불필요, C는 전체합 오류.