SQL_Analysis 3

GROUP BY & Aggregate 함수

• 테이블의 레코드를 그룹핑하여 그룹별로 다양한 정보를 계산
• 이는 두 단계로 이뤄짐
  • 먼저 그룹핑을 할 필드를 결정(하나 이상의 필드가 될 수 있음)
    • GROUP BY로 지정(필드 이름을 사용하거나 필드 일련번호를 사용)
  • 다음 그룹별로 계산할 내용을 결정
    • 여기서 Aggregate 함수를 사용
    • COUNT, SUM, AVG, MIN, MAX, LISTAGG,…
    • 보통 필드이름을 지정하는것이 일반적
• 월별 레코드수를 계산하는 SQL

  • LEFT(field, 7)

  • SUBSTRING(field, 1,7)

  • TO_CHAR(field, ‘YYYY-MM’)

  • DATE_TRUNC(’month’, field)

    날짜 필드에서 앞에 7자리를 잘라 년,월만을 가져와 group by로 묶어 count처리해줌

CATS: SELECT를 이용하여 테이블 생성하기

• 간단하게 새로운 테이블을 만드는 방법
• 자주 조인하는 테이블이 있다면 이를 CTAS를 사용해서 조인해두면 편리해짐

항상 시도해봐야하는 데이터 품질 확인방법

• 중복된 레코드 체크
• 최근 데이터 존재여부 체크
• Primary key uniqueness가 지켜지는지 체크
• 값이 비어있는 컬럼들이 있는지 체크하기

중복 제거

COUNT 를 이용한 중복제거

SELCET COUNT(1)
FROM TABLE

SELECT COUNT(1)
FROM (
SELECT DISTINCT field FROM TABLE
)

후 두 결과 비교

field를 DISTINCT한 뒤 COUNT한 값과 그냥 COUNT 한 값이 같으면 중복되는 값이 없다고 판단이 가능함

CTE를 사용해서 중복 제거후

with ds AS(
SELECT DISTINCT field FROM TABLE
)

SELECT COUNT(1)
FROM ds;

with를 이용해서 모든 중복값을 제거한 테이블을 만든뒤 해당 테이블을 사용함으로서 중복값을 없앨수있음

최근 데이터의 존재여부 체크

SELECT MIN(ts), MAX(ts)
FROM TABLE

레코드가 가지고있는 타임스탬프 field를 확인

Primary key uniqueness 가 지켜지는지 체크

SELECT primaryfield,count(1)
FROM table
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1

프라이머리키를 기준으로 GROUP BY를 실행하여 COUNT해준뒤 내림차순으로 정렬하여 가장 위값을 가져옴으로서 해당 COUNT 값이 1이 아니면 Primary key uniqueness 가 지켜지지않고 있는것을 알수있음

값이 비어있는 컬럼들이 있는지 체크

SELECT
COUNT(CASE WHEN field is NULL THEN 1 END) field_null_conut
FROM TABLE

CASE문을 이용하여 field값이 NULL이면 1을 넘겨줌으로서 field 값이 NULL일 경우만을 COUNT할수 있음