중급 집계 함수

Snowflake 데이터 파이프라인 자동화

Emily Melhuish

Technical Curriculum Developer, Snowflake

실행 순서

Order of operations.png

SQL은 작성한 순서대로 실행되지 않습니다

행: 데이터셋 구성
그룹: 집계
결과: 출력 형태 지정

¹ * Snowflake Learning Material

주요 집계 함수

COUNT(*) / COUNT(DISTINCT col) — 전체 행 수; 고유 값
SUM — 열 합계
AVG — 평균값
MIN / MAX — 범위

SELECT COUNT(*) AS total_shipments,
       COUNT(DISTINCT carrier_id) AS unique_carriers,
       SUM(delivery_days) AS total_days,
       AVG(delivery_days) AS avg_days
FROM shipments;

다중 쿼리의 문제점

지역 및 웨어하우스별 사용량 반환

SELECT 
region
, warehouse_name
, SUM(credits_used) 
FROM usage 
GROUP BY region, warehouse_name;

지역 소계 반환

SELECT region
, SUM(credits_used) 
FROM usage GROUP BY region;

전체 사용량 합계 반환

SELECT 
SUM(credits_used) 
FROM usage;

GROUPING SETS

입력

SELECT region
, warehouse_name
, SUM(credits_used) AS total 
FROM usage 
GROUP BY GROUPING SETS ( 
  (region, warehouse_name)
  , (region)
  , () 
);

출력

REGION	WAREHOUSE_NAME	TOTAL
EMEA	harbr_wh_prod	1240.50
EMEA	harbr_wh_dev	380.00
EMEA	NULL	1620.50
APAC	harbr_wh_prod	890.25
APAC	NULL	890.25
NULL	NULL	2510.75

참고: CASE 문을 사용하여 NULL에 의미 부여

ROLLUP

입력

SELECT 
region
, warehouse_name
, SUM(credits_used) AS total 
FROM usage 
GROUP BY ROLLUP (region, warehouse_name);

출력

REGION	WAREHOUSE_NAME	TOTAL
EMEA	harbr_wh_prod	1240.50
EMEA	harbr_wh_dev	380.00
EMEA	NULL	1620.50
APAC	harbr_wh_prod	890.25
APAC	NULL	890.25
NULL	NULL	2510.75

CUBE

입력

SELECT 
region
, warehouse_name
, SUM(credits_used) AS total
FROM usage 
GROUP BY CUBE (region, warehouse_name);

출력

REGION	WAREHOUSE_NAME	TOTAL
EMEA	harbr_wh_prod	1240.50
EMEA	harbr_wh_dev	380.00
EMEA	NULL	1620.50
APAC	harbr_wh_prod	890.25
APAC	NULL	890.25	}
NULL	harbr_wh_prod	2130.75
NULL	harbr_wh_dev	380.00
NULL	NULL	2510.75

GROUPING() 및 GROUPING_ID()

SELECT region, warehouse_name, SUM(credits_used) AS total_credits, GROUPING(region) AS is_region_agg, GROUPING(warehouse_name) AS is_wh_agg , 
GROUPING_ID(region, warehouse_name) AS grouping_level 
FROM usage 
GROUP BY ROLLUP (region, warehouse_name);

마크다운 테이블 출력 스크린샷

연습해 봅시다!

Snowflake 데이터 파이프라인 자동화