목차

  • Window Function 개요
  • 집계 함수
  • Window Function 구문

 

1. Window Function 개요

  행과 행간의 관계를 쉽게 정의하기 위해 만든 함수를 뜻함. 집계함수를 사용한다는 점에서 groupby 집계와 비슷하지만 윈도우 함수와 큰 차이점이 존재한다. 바로 출력되는 데이터 형태의 '집약' 여부다. 직관적인 차이는 아래 예시와 같다.

-- GROUP BY절을 이용한 집계
SELECT
  category
  ,COUNT(name) as category_cnt

FROM tutorial.animal_crossing_construction
GROUP BY category

[그림 1] group by 집계 결과

 

-- Window 함수를 이용한 집계
SELECT
  category
  ,COUNT(name) OVER (PARTITION BY category) as category_cnt

FROM tutorial.animal_crossing_construction

[그림2] window 함수 집계 결과

 

 위의 예시는 Mode SQL 사이트에서 animal_crossing_construction 이라는 튜토리얼 테이블로 실행한 결과다. 두 쿼리 결과를 비교해 보았을 때,

  • groupby 절은 집계하고자 하는 속성의 고유 개수만큼 결과가 집약되어 출력
  • window 함수는 집계하고자 하는 속성의 고유 개수에 관계 없이 모든 행에 집계 결과를 출력

와 같은 차이점을 보이고 있다.

 

2. 집계 함수

2.1) 순위 함수

  • RANK: 특정 컬럼의 순위 도출하는 함수. 동일한 값에 대해서는 같은 순위를 부여하며 중간 순위는 비움.
-- RANK 함수를 이용한 집계
SELECT
  name
  ,category
  ,buy
  ,RANK() OVER (PARTITION BY category ORDER BY buy DESC) as buy_rank

FROM tutorial.animal_crossing_construction

[그림 3] RANK 함수 쿼리 결과

 

  • DENSE_RANK: 동일한 값에 대해서 같은 순위를 부여. RANK함수와 달리 중간 순위를 비우지 않음
-- DENSE_RANK 함수를 이용한 집계
SELECT
  name
  ,category
  ,buy
  ,DENSE_RANK() OVER (PARTITION BY category ORDER BY buy DESC) as buy_rank

FROM tutorial.animal_crossing_construction

[그림 4] DENSE_RANK 함수 집계 결과

 

  • ROW_NUMBER: RANK, DENSE_RANK 함수와 달리 동일한 값에도 고유 순위를 부여함.
-- ROW_NUMBER 함수를 이용한 집계
SELECT
  name
  ,category
  ,buy
  ,ROW_NUMBER() OVER (PARTITION BY category ORDER BY buy DESC) as buy_rank

FROM tutorial.animal_crossing_construction

[그림 5] ROW_NUMBER 함수 집계 결과

2.2) 일반 집계 함수

  • SUM: 합계
  • MAX: 최대값
  • MIN: 최소값
  • AVG: 평균값
  • COUNT: 개수
-- MAX 함수를 이용한 집계
SELECT
  name
  ,category
  ,buy
  ,MAX(buy) OVER (PARTITION BY category ORDER BY buy DESC) as max_buy

FROM tutorial.animal_crossing_construction

[그림 6] MAX 함수 집계 결과

 

일반 집계 함수는 순위 함수와 달리 () 안에 집계하고자 하는 대상의 컬럼명을 기입해야 함.

2.3) 그룹 내 행 순서 함수

  • FIRST_VALUE: over 절 내 partition 별 윈도우에서 가장 먼저 나온 값 도출
-- FIRST_VALUE 함수를 이용한 집계
SELECT
  name
  ,category
  ,buy
  ,FIRST_VALUE(buy) OVER (PARTITION BY category) as first_buy

FROM tutorial.animal_crossing_construction

[그림 7] FIRST_VALUE 함수 집계 결과

  • LAST_VALUE: over 절 내 partition 별 윈도우에서 가장 나중에 나온 값 도출
-- LAST_VALUE 함수를 이용한 집계
SELECT
  name
  ,category
  ,buy
  ,LAST_VALUE(buy) OVER (PARTITION BY category) as last_buy

FROM tutorial.animal_crossing_construction

[그림 8] LAST_VALUE 함수 집계 결과

  • LAG: 이전 n번째 행의 값 가져옴. 최대 3개의 인자를 가지며
    • 첫 번째 인자: 집계할 컬럼명
    • 두 번째 인자: 몇 번째 앞의 행을 가져올지 결정 (default 값은 1)
    • 세 번째 인자: 가져올 행이 없을 경우 채우고자 하는 값
-- LAG 함수를 이용한 집계
SELECT
  name
  ,category
  ,buy
  ,LAG(buy, 2) OVER (PARTITION BY category) as lag_buy

FROM tutorial.animal_crossing_construction

[그림 9] LAG 함수 이전 2번째 값 집계 결과

  • LEAD: 이후 n번째 행의 값 가져옴. 인자 개수는 LAG 함수와 동일
-- LEAD 함수를 이용한 집계
SELECT
  name
  ,category
  ,buy
  ,LEAD(buy, 2) OVER (PARTITION BY category) as lead_buy

FROM tutorial.animal_crossing_construction

[그림 10] LEAD 함수 이후 2번째 값 집계 결과

2.4) 그룹 내 비율 함수

  • PERCENT_RANK: partition 별로 가장 먼저 나오는 값을 0, 마지막에 나오는 값을 1로 해서 순서별 백분율 산출
-- PERCENT_RANK 함수를 이용한 집계
SELECT
  name
  ,category
  ,buy
  ,PERCENT_RANK() OVER (PARTITION BY category ORDER BY buy) as buy_percent_rank

FROM tutorial.animal_crossing_construction

[그림 11] PERCENT_RANK 함수 집계 결과

  • CUME_DIST: partition 별 누적백분율 산출
-- CUME_DIST 함수를 이용한 집계
SELECT
  name
  ,category
  ,buy
  ,CUME_DIST() OVER (PARTITION BY category ORDER BY buy) as buy_percent_rank

FROM tutorial.animal_crossing_construction

[그림 12] CUME_DIST 함수 집계 결과

  • NTILE: partition 별 집계 결과를 n등분한 결과 산출. 이 때, 해당 함수의 인자로 몇 등분할 것인지 숫자를 지정해야 함.
-- NTILE 함수를 이용한 집계
SELECT
  name
  ,category
  ,buy
  ,NTILE(4) OVER (PARTITION BY category ORDER BY buy) as buy_percent_rank

FROM tutorial.animal_crossing_construction

[그림 13] NTILE 함수 4등분 집계 결과

 

3. Window Function 구문

[그림 14] Window Function 문법 (참고링크: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls)

 

상단의 이미지는 Window 함수 문법구조를 나타내고 있다. Google Cloud 내 Bigquery 가이드 문서에서 참고한 내용이다.

Window 함수의 대략적인 구조는 다음과 같다.

  • function_name: 윈도우 절을 동작시키는 집계함수를 뜻함. 앞서 소개한 집계함수들이 모두 이에 해당됨. 여기서 argument_list는 집계함수의 특성에 따라 들어갈 수도 있고 안들어갈 수도 있음.
  • OVER: 윈도우 함수를 사용함을 나타내는 키워드로 반드시 기입해야 함.
  • over_clause: function_name (집계함수)을 어떻게 주어진 행에 적용시킬지 나타내는 구문. () 안에 조건을 기입함.
    • PARTITION BY: 윈도우 함수를 어떤 컬럼 카테고리를 기준으로 나눌지 설정하는 구문. group by 와 비슷한 역할을 한다고 생각하면 됨.
    • ORDER BY: 윈도우 함수를 어떤 컬럼을 기준으로 정렬할지 설정하는 구문. 오름차순이면 ASC 또는 빈 구문, 내림차순이면 DESC를 기입
    • window_frame_clause: 윈도우 함수에 적용되는 행을 어떤 범위로 구간을 나눌지 적용시키는 구문
      • rows_range
        • ROWS: 물리적 행 단위. 모든 행을 1개의 행으로 독립적으로 인식
        • RANGE: 논리적 행 집합. ORDER BY절에 명시된 컬럼으로 논리적인 행 집합 구성. 집합으로 묶인 그룹이 1개의 행으로 인식됨.
      • frame_start | frame_between
        • CURRENT ROW: 현재 행
        • UNBOUNDED:가장 맨 앞 또는 맨 뒤의 행
        • (UNBOUNDED OR N) PRECEDING: 이전 N번째 행
        • (UNBOUNDED OR N) FOLLOWING: 다음 N번째 행

rows_range와 frame_start | frame_between 구문의 종류 별 예시를 통해 차이를 알아보자.

 

1. ROWS와 RANGE의 차이

SELECT
  name
  ,category
  ,buy
  ,SUM(buy) OVER (PARTITION BY category ORDER BY buy ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as window_buy_row_sum
  ,SUM(buy) OVER (PARTITION BY category ORDER BY buy RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as window_buy_range_sum

FROM tutorial.animal_crossing_construction

[그림 15] ROWS와 RANGE의 차이

 

sum() 집계 함수를 사용하여 category 컬럼의 속성 별로 나누어 (partition by) buy 컬럼을 기준으로 오름차순 (order by) 할 때, 첫 번째 행부터 헌재 행 (BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)까지 집계하는 window 함수를 명시한 결과다.

  • ROWS: 각 행을 독립적인 행으로 간주. 현재 행으로부터 이전 행의 모든 buy컬럼 값을 합산하여 누적 합을 계산
  • RANGE: order by 절에 명시했던 buy컬럼의 값을 기준으로 행을 묶어서 집계. 즉, buy 컬럼의 속성 값이 동일할 경우, 동일한 행으로 간주하고 합계를 계산

2. PRECEDING & FOLLOWING

SELECT
  name
  ,category
  ,buy
  ,SUM(buy) OVER (PARTITION BY category ORDER BY buy ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) as window_buy_row_sum

FROM tutorial.animal_crossing_construction

[그림 16] PRECEDING & FOLLOWING 예시

 

category 컬럼의 각 카테고리 속성 내에서 현재 행 기준으로 이전 2개, 이후 2개에 해당되는 buy값을 합계한 결과다.

 

3. UNBOUNDED

SELECT
  name
  ,category
  ,buy
  ,SUM(buy) OVER (PARTITION BY category ORDER BY buy ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as window_buy_row_sum

FROM tutorial.animal_crossing_construction

[그림 17] UNBOUNDED 예시

 

category 컬럼의 각 카테고리 속성 내에서 현재 행 기준으로 이전 모든 행, 이후 모든 행에 해당되는 buy값을 합계한 결과다.

1. Requirements.txt 생성

%%writefile requirements.txt
pandas
fsspec
numpy
matplotlib
scikit-learn
statsmodels
google-api-core
google-api-python-client
google-auth>=2.15.0
google-auth-httplib2
google-auth-oauthlib
google-cloud
google-cloud-aiplatform
google-cloud-bigquery
google-cloud-core
google-cloud-datastore
google-cloud-pipeline-components
google-cloud-storage
google-crc32c
google-resumable-media
googleapis-common-protos
kfp>=2.*
kfp-pipeline-spec
kfp-server-api
kubernetes
db-dtypes

 

2. Docker 파일 생성

%%writefile Dockerfile

FROM python:3.10
WORKDIR /
COPY requirements.txt /requirements.txt

RUN pip install --upgrade pip
RUN pip install -r requirements.txt
ENTRYPOINT [ "bash" ]
  • FROM: 파이썬 기본 버전이 기재된 base 이미지
  • WORKDIR: Container에서 작업 경로 설정
  • COPY: 도커실행폴더 기준으로 해당 파일 또는 폴더를 지정한 컨테이너 내부 위치로 복사
  • RUN: COMMEND 창에서 실행할 명령어 수행
  • ENTRYPOINT: 생성 시 실행되는 명령어

3. Docker Build Shell 스크립트 생성

%%writefile docker_build.sh
#!/bin/bash     
PROJECT_ID="프로젝트명"
REGION="리전명"
REPOSITORY="Artifact 레포지토리 이름"
IMAGE_TAG='이미지명'

docker build -t $REGION-docker.pkg.dev/$PROJECT_ID/$REPOSITORY/$IMAGE_TAG .
  • GCP Artifact Repository 내 지정된 Repository 장소에 IMAGE_TAG 이름으로 이미지 build 수행
  • 로컬에 이미지 생성

4. Docker Push Shell 스크립트 생성

%%writefile docker_push.sh
#!/bin/bash     
PROJECT_ID="프로젝트명"
REGION="리전명"
REPOSITORY="Artifact 레포지토리 이름"
IMAGE_TAG='이미지명'

####### artifact registry repository 생성 안했을 때, 진행하는 부분
# Create repository in the artifact registry
# gcloud beta artifacts repositories create $REPOSITORY \
#   --repository-format=docker \
#   --location=$REGION

# Configure Docker
gcloud auth configure-docker $REGION-docker.pkg.dev

# Push
docker push $REGION-docker.pkg.dev/$PROJECT_ID/$REPOSITORY/$IMAGE_TAG
  • 로컬에 생성된 이미지를 Artifact Registry에 저장

1. Cloud function

  1.  기본사항
    1. 환경 : 1세대
    2. 함수 이름 설정 (~-batch-function)
    3. 리젼 : asia-northeast3
  2. 트리거
    1. HTTPS 필요 체크 해제
  3. 런타임, 빌드, 연결, 보안 설정
    1. cloud function 권한이 있는 서비스 계정 설정
  4. 소스
    1. 런타임 : Python 3.9, 진입점 : process_request
    2. main.py
import json
from google.cloud import aiplatform

PROJECT_ID = 프로젝트명
REGION = 리전명
PIPELINE_ROOT = 파이프라인 GCS 경로

def process_request(request):

   aiplatform.init(
       project=PROJECT_ID,
       location=REGION,
   )

   job = aiplatform.PipelineJob(

       display_name=파이프라인 이름,
       template_path=파이프라인 json 파일 GCS 경로,
       pipeline_root=PIPELINE_ROOT,
       location=REGION,
       enable_caching=False

   )

   job.submit(service_account=서비스 계정명)
   return "Job submitted"
  • requirements.txt
google-api-python-client>=1.7.8,<2
google-cloud-aiplatform
pyyaml

 

2. Cloud Scheduler

  1. 일정정의
    • 스케줄러 이름 설정
    • 리젼 : asia-northeast3 (서울)
    • 빈도
      • 매일 10시 45분 : 45 10 * * *
      • 매월 10시45분 : 45 10 1 * *
    • 시간대 : 한국 표준시(KST)
  2. 실행 구성
    • 대상 유형 : HTTP
    • URL
      • 위의 Cloud Function 생성 → 트리거 → 트리거 URL 복사
    • 인증헤더 : OIDC 토큰 추가
    • 스케줄러 권한이 있는 서비스 계정 설정

+ Recent posts