멋사 AI스쿨8기

[멋사AI스쿨8기] WIL - SQL(ROLLUP, WINDOW함수)

JJouni 2023. 2. 9. 16:10

SQL 문법 강의도 듣고 있다.

제주에서 코딩캠프?를 하시는 이범재 강사님께 듣고 있는데, 그간 구몬학습법처럼 하나의 문법을 세세하게 예제를 풀어보며 손에 익혔는데, 요번주는 쪼끔 어려워서, 이번주 WIL은 이범재 강사님의 수업으로 해보고자 한다.

그리고 월, 화 몸살기운+신경성 위장염으로 앓아 누워 수업을 많이 못따라갔기에. . . .

 

복습

JOIN : 키 값을 연결해서 table1과 table2를 연결할 수 있음

  • inner join : 교집합
  • left : 왼쪽 테이블의 모든 것+부가적인 것을 붙여 넣음(table2 내용)
  • right : 반대
  • outer : 모든 걸 넣는 합집합

트랜젝션 데이터

서브쿼리, WITH, FROM

 

 

ROLLUP : 각각 단계에 따른 합계, 소계를 구해줍니다.

한 가지에 대한 합계, 여러가지에 대한 합계, 소계를 모두 구할 수 있다. 

 

BEFORE

SELECT country, 
	COUNT(id) AS count_user
FROM `thelook_ecommerce.users`
GROUP BY country;

AFTER

SELECT country,
	COUNT(id) AS count_user
FROM `thelook_ecommerce.users`
GROUP BY ROLLUP(country);

첫번째 행의 null이 전 세계 국가의 총계를 나타냅니다.

 

WINDOW 함수 : 현재 행과 관련이 있는 테이블 행들에 대해 계산을 수행한다

일단 함수 입니다. input -> 처리 -> output

예를 들어, user id 별로 주문수를 하려면 1: user_id에서 count 하고, 2: order에서 count를 하여 구한 값을 join 해야 함. Join을 통해 하나로 만들어야지만 소계가 가능함. 그렇게 하지 않고도 바로 조회하지 않고 뽑아낼 수 있는 게 window함수이다.

 

윈도우 함수는 OVER 절을 포함합니다. 이 절은 평가 중인 행을 중심으로 행의 기간을 정의합니다. 각 행에 대한 윈도우 함수 결과는 선택된 행 윈도우를 입력으로 사용하여 집계 방식으로 계산됩니다.

윈도우 함수를 사용하면 이동 평균, 항목의 순위, 누적 합계를 계산하고, 기타 분석을 수행할 수 있습니다.

집계 함수와 다르게 윈도우 함수는 각 행마다 단일 값을 반환합니다.

* 윈도우 함수의 분류

  • 그룹 내 순위 관련 함수(RANKING FAMILY)
    • RANK, DENSE_RANK, ROW_NUMBER
  • 그룹 내 집계 관련 함수(WINDOW AGGREGATE FAMILY)
    • SUM, MAX, MIN, AVG, COUNT
  • 그룹 내 행 순서 관련 함수
    • LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE
  • 그룹 내 비율 관련 함수
    • CUME_DIST, PERCENT_RANK, NTILE

 

1.  문법

  • 함수 이름(컬럼, OFFSET) OVER (PARTITION BY 파티션_컬럼 ORDER BY 정렬_컬럼)
    • OFFSET : 값을 가져올 행의 위치. 기본 값은 1이고 생략 가능
  • 함수 이름(컬럼) OVER (PARTITION BY 파티션_컬럼 ORDER BY 정렬_컬럼)
  • 필요에 따라 PARTITION BY는 생략 가능

 

2. 순위 윈도우 함수

2.1 RANK()

파티션 내에서 현재 행의 순위를 부여한다. 동일 값인 경우 동일 순위가 부여되고, 다음 순위는 동일값의 수만큼 건너뛰어 부여된다.

 

2.2 DENSE_RANK()

파티션 내에서 현재 행의 순위를 부여한다. 동일 값인 경우 동일 순위가 부여되고, 다음 순위는 건너뛰지 않고 순차 번호로 부여 된다.

 

2.3 ROW_NUMBER()

파티션 내에서 1부터 순차적으로 하나씩 증가하는 번호를 생성한다.

 

3. 탐색 함수(그룹 내 행 순서 관련 함수)

3.1 LAG, LEAD

LAG는 이전 행의 필드를 읽고, LEAD는 다음 행의 필드를 읽습니다.

 

3.2 FIRST_VALUE, LAST_VALUE

FIRST_VALUE은 그룹 내의 첫값을 구하고, LAST_VALUE는 마지막 값을 구합니다.

단, LAST_VALUE는 지금까지 읽은 행의 집합을 의미하기 때문에 항상 자기 자신입니다.

전체 그룹에 대한 마지막 값을 구하려면 ROWS 옵션을 주어야 합니다.

 

3.3 NTH_VALUE

현재 윈도우 프레임에 있는 N번째 행의 값을 반환합니다. 이 행이 없으면 NULL을 반환합니다.

 

3.2 그룹 내 비율 관련 함수

3.2.1 PERCENT_RANK()

현재 행의 상대적 순위를 반환한다.

계산에 따라 0과 1사이의 범위에서 행의 백분율 순위를 계산한다.

 

3.2.2 CUME_DIST() - 누적분포

cumulative distribution

  • 0보다 크고 1보다 작거나 같은 값이 나옴.
  • n보다 값이 작은 행의 갯수 / 현재 window 또는 파티션의 row 개수

 

3.2.3 NTILE(n)

레코드의 집합을 n개의 영역으로 구분하고 소속 영역을 구한다. 인수 n은 나눌 영역의 개수를 지정한다.

 
 

4. 집계 분석 함수(그룹 내 집계 관련 함수)

4.1 SUM(value)

파티션별 윈도우 내에서 지정된 value의 합을 계산한다.

4.2 MAX(value)

파티션별 윈도우 내에서 지정한 value의 최대값을 반환한다.

4.3 MIN(value)

파티션별 윈도우 내에서 지정한 value의 최소값을 반환한다.

4.4 AVG(value)

파티션별 윈도우 내에서 지정한 value의 평균값을 계산한다.

4.5 COUNT(value)

파티션별 윈도우 내에서 지정한 value에 대해 존재하는 행을 카운트 한다.

 

 

 

여기에 데이터수정, 실무데이터 분석 등도 수업하셨는데, 그 부분은 내가 듣지 못하여 . ..  들었던 부분으로 WIL을 작성해보았다.

SQL의 WINDOW함수는 지금까지 봐온 SQL문법과 사뭇 다르지만, 또 유용하게 사용할 수 있는 도구 같아 보인다. 

반복 복습해두어야겠다. 이번주 결석이 많았음에 너무 슬프다 ㅠ ㅠ

⭐ 이번주 내가 느낀 것

사실 결혼을 준비하면서 수업 잘 따라갈 수 있을 것이라고 생각했다.

이게 웬걸, 예상과 달리 한 주 전인 요번주동안 평소보다 더 세세하게 신경써야할 부분들이 많았고, 연락도 많았다.

이 준비와 더불어 멋사 수업, 과제를 따라가기가 벅찼다. 스트레스를 많이 받았는지, 병까지 난 것이다 .. . 

그새 난 또 여러 깨달음?을 얻었다.

 

고통스러워하며 병원에 댕겨오며 든 생각은

'아프면 안되는 시기에 아프고 그러냐.. . . 아플 시간도 없는 현대인의 삶인가 . .?'

 

정신 차리고 의자에 앉을 정도가 되어 수요일에 수업을 들으며 든 생각은

'말짱하게 앉아서 아침 9시부터 저녁 6시까지 수업을 들을 수 있는 이런 평범한 일상이 참 감사하다...!'

 

모든 일상이 배움이다.

 

여차저차 계속되고 있는 결혼과 멋사수업의 병행. 이것도 자꾸만 여러가지 생각이 든다. 

더 열심히 프로그램에 참여하지 못하는 이 몇 주간이 많이 아쉽기도 하고

결혼 후에 시작할껄 하는 후회적인 생각이 스치곤 한다. 

주변에 민폐인 느낌도 든다... 

그래도 여기서 배운 마인드를 가지고 돌아보면 하지 않았던 나와 이걸 결혼과 병행해온 나는 많이 달랐다.

오늘, 어제보다 더 성장하고 건강한 하루를 보낼 수 있음에 감사하다.

다녀와서 더 열심히, 정말 열심히 실력을 키워가야할 것이다.

조금 느릴지라도 내 속도대로 꾸준히.

꾸준함이 미덕이다.

 

 

 

 

* 강의 내용은 이범재 강사님 자료 참고하여 작성됨

 

⭐