[성동3기 전Z전능 데이터 분석가] DAY 41 _ SQL _ 윈도우 함수 (ROW_NUMBER(), LAG(), RANK(), DENSE_RANK(), LEAD() / VIEW / 데이터 추출

Window Function (윈도우 함수)

 

• 행과 행 간의 관계를 쉽게 적용하기 위해 만든 함수
• ex. RANK 를 사용해 순위를 매길 수 있음
  • Syntax
    • RANK() OVER ([PARTITION BY 열명] ORDER BY 열명 [DESC]) AS RANK
• MYSQL 의 대표적인 윈도우 함수
  • ROW_NUMBER() : 순위 매기기
  • LAG() : 이전 행 값 참고하기
  • RANK() : 공동 순위를 허용하는 순위 매기기
  • DENSE_RANK() : 공동 순위 후 순차적 순위
  • LEAD() : 다음 행 값 참조하기

 

 

ROW_NUMBER()

 

• 각 행에 고유한 순번을 할당하는 윈도우 함수 → 정렬된 행 순서대로 1부터 시작하는 숫자를 부여
• Syntax
  • ROW_NUMBER() OVER ([PARTITION BY 열명] ORDER BY 열명 [ASC | DESC])

SELECT 
	FirstName,
	LastName,
    Title,
	HireDate,
	ROW_NUMBER() OVER (PARTITION BY Title ORDER BY HireDate) AS row_num
FROM Employee
WHERE HireDate IS NOT NULL;

 

PARTITION BY 는 GROUP BY 와 유사한 역할로, Title 별로 그룹화하여 HireDate 순서대로 row_num 이 매겨진다.

 

 

LAG()

 

• 함수 이전 행 값을 참조
• Syntax
  • LAG(열명, 상수) OVER ([PARTITION BY 열명] ORDER BY 열명 [ASC | DESC])

SELECT 
	FirstName,
	LastName,
	HireDate,
	LAG(FirstName, 1) OVER (ORDER BY HireDate) AS previous_hire_name,
	LAG(HireDate, 1) OVER (ORDER BY HireDate) AS previous_hire_date
FROM Employee e;

 

 

직접 해보기

 

# Q1. employee 테이블에서 각 직원의 이름, 직책, 직책별 입사 순위를 조회
#	  입사 순위는 입사일이 빠룬 순서대로 시작하며, 동일한 입사일에 입사한 직원은 동일한 순위를 갖도록 함 
SELECT 
	FirstName,
	LastName,
	Title,
	HireDate,
	DENSE_RANK() OVER (PARTITION BY Title ORDER BY HireDate) AS hireranking
FROM(
 SELECT *
 FROM Employee
 WHERE HireDate IS NOT NULL) sub;

 

 

VIEW (뷰)

 

• READ 한 데이터 기억하기
• SELECT 문을 가상의 테이블처럼 보고 사용 가능
• CREATE VIEW 를 통해, 가상의 테이블 생성 가능
• Syntax
  • CREATE VIEW viewname AS SELECT ~

 

 

데이터 추출

 

• 전처리 끝낸 데이터를 따로 파일(Excel, CSV)로 저장하기 (팀원과 파일 공유 시 필요)

1. 데이터 전체를 결과 창에 출력
2. 하단 빨간 박스 클릭
3. 빨간 원 클릭하여, 데이터 전체 선택 및 복사
4. Excel 에 붙여넣기

 

또는 결과창 하단의 데이터 추출하기 버튼을 클릭하여, CSV 파일이나 다른 파일로 만들 수 있다.

 

 

Reflection

 

이것으로 짧은 시간동안, 핵심적인 SQL DML 을 학습하였다. 중간에 아마.. 서브쿼리 쯤에서 잠시 버퍼링이 있었지만 한 줄씩 쪼개서 쿼리 작동 순서대로 실행해보고, 머릿속에도 그려보니 전보다는 조금 더 친해진 기분이다. 이제 실전이다. 실제 프로젝트에 적용해보는 과정에서 다양하면서도 정밀하게 테이블을 추출해볼 수 있도록 사전 프로젝트 기획 단계에서 최대한 명확한 문제정의와 방향성을 잡아 나아가야겠다는 생각이 든다. 다음주의 나.. 화이팅 🥲