• 2021년 5월에 추가된 BigQuery QUALIFY 함수에 대해 정리한 글입니다
  • BigQuery QUALIFY, BigQuery window function condition, bigquery window function filter, bigquery qualify 사용법, bigquery analyitics function filter, bigquery analytics function condition

QUALIFY

• QUALIFY 함수는 윈도우 함수(또는 분석 함수)에 조건을 설정할 때 활용할 수 있는 함수
  • 윈도우 함수의 결과를 바로 필터링할 수 있음

query_statement:
    query_expr

query_expr:
    [ WITH with_clause ]
    { select | ( query_expr ) | query_expr set_op query_expr }
    [ ORDER BY expression [{ ASC | DESC }] [, ...] ]
    [ LIMIT count [ OFFSET skip_rows ] ]

select:
    SELECT [ AS { STRUCT | VALUE } ] [{ ALL | DISTINCT }]
        { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ]
            [ REPLACE ( expression [ AS ] column_name [, ...] ) ]
        | expression [ [ AS ] alias ] } [, ...]
    [ FROM from_clause[, ...] ]
    [ WHERE bool_expression ]
    [ GROUP BY { expression [, ...] | ROLLUP ( expression [, ...] ) } ]
    [ HAVING bool_expression ]
    [ QUALIFY bool_expression ]
    [ WINDOW window_clause ]

set_op:
    UNION { ALL | DISTINCT } | INTERSECT DISTINCT | EXCEPT DISTINCT

• BigQuery SQL 구문을 보면 HAVING 밑에 위치함을 알 수 있음

QUALIFY가 없던 시절에 사용했던 쿼리

• 분석 함수의 결과를 조건으로 설정하고 싶은 경우, 아래 쿼리처럼 서브쿼리에 기존 쿼리를 감싸서 작성함
• 어렵진 않지만, 기본적으로 쿼리가 3~4줄이 길어지는 번거로움이 존재

SELECT *
FROM (
  SELECT
    item,
    RANK() OVER (PARTITION BY category ORDER BY purchases DESC) as rank
  FROM Produce
  WHERE Produce.category = 'vegetable'
) 
WHERE 
  rank <= 3

QUALIFY 사용법

• HAVING 아래(HAVING이 없다면 보통 WHERE)에 QUALIFY bool_expression
  • 1) SELECT 절에 분석 함수를 사용한 컬럼을 명시하는 방법
  • 2) QUALIFY 절에 분석 함수를 사용하는 방법
• 단, QUALIFY 절은 WHERE, GROUP BY, HAVING 중 하나를 같이 사용해야 함
  • 만약 조건이 떠오르지 않으면 WHERE 1=1를 추가하면 됨(약간 이상한 해결책이긴 합니다..)

• 참고로 QUALIFY는 분석 함수가 실행된 후에, 실행되는 함수

• 1) SELECT 절에 분석 함수를 사용한 컬럼을 명시하는 방법

    SELECT
      item,
      RANK() OVER (PARTITION BY category ORDER BY purchases DESC) as rank
    FROM Produce
    WHERE Produce.category = 'vegetable'
    QUALIFY rank <= 3
  

• 2) QUALIFY 절에 분석 함수를 사용하는 방법

    SELECT 
      item
    FROM Produce
    WHERE Produce.category = 'vegetable'
    QUALIFY RANK() OVER (PARTITION BY category ORDER BY purchases DESC) <= 3
  

Reference

• BigQuery QUALIFY Document

카일스쿨 유튜브 채널을 만들었습니다. 데이터 사이언스, 성장, 리더십, BigQuery 등을 이야기할 예정이니, 관심 있으시면 구독 부탁드립니다 :)

PM을 위한 데이터 리터러시 강의를 만들었습니다. 문제 정의, 지표, 실험 설계, 문화 만들기, 로그 설계, 회고 등을 담은 강의입니다

이 글이 도움이 되셨거나 다양한 의견이 있다면 댓글 부탁드립니다 :)

Buy me a coffee