이동평균 계산 시 order by 기본값은 뭔가요?

BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)

2-11. 윈도우 함수 연습 문제(2번~6번)

해결된 질문

작성

윈도우 함수 연습문제 6번 질문입니다.

카일스쿨님 쿼리와 다른 건 다 동일하게 작성했었는데, OVER() 안에 order by를 모르고 깜빡했었습니다.
그런데도 결과가 정답(moving_avg2)과 같게 정상 출력이 되었습니다. (QUALIFY로 검증해봤는데 moving_avg와 전부 같다고 나오더라구요)
원래 ORDER BY를 안 써줘도 프레임 설정이 자동으로 가능한 걸까요? ORDER BY에도 기본값 같은 게 있는지 궁금합니다.

-- 윗부분은 생략
SELECT
  date,
  number_of_orders,
  AVG(num2) OVER(ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg,
  AVG(num2) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg2
FROM filled_data
-- QUALIFY moving_avg != moving_avg2
ORDER BY date

sql Google-Analytics firebase google-sheets bigquery 카카오공채-서비스

답변 2

카일스쿨

지식공유자

안녕하세요. 윈도우 함수 내에 ORDER BY는 기본값이 있지 않고, 현재 ORDER BY를 작성하지 않은 경우엔 정렬하지 않은 형태의 데이터에서 프레임을 설정합니다.

지금 문제는데이터가 이미 정렬이 된 상태라서 윈도우 함수 안에 ORDER BY가 없어도 윈도우 함수의 ORDER BY와 같은 결과가 나오게 된 것입니다. 즉, 데이터가 정렬이 되어있기에 이렇게 나온 것입니다(우연히 정렬이 된 데이터였던 것)

확인을 위해 아래 쿼리를 실행해보시면 이해하실 수 있을 것 같아요. 기존 filled_data를 사전에 ORDER BY를 하고 지금 작성하신 쿼리를 비교해보면 결과가 다릅니다.


WITH raw_data AS (
  SELECT DATE '2024-05-01' AS date, 15 AS number_of_orders UNION ALL
  SELECT DATE '2024-05-02', 13 UNION ALL
  SELECT DATE '2024-05-03', NULL UNION ALL
  SELECT DATE '2024-05-04', 16 UNION ALL
  SELECT DATE '2024-05-05', NULL UNION ALL
  SELECT DATE '2024-05-06', 18 UNION ALL
  SELECT DATE '2024-05-07', 20 UNION ALL
  SELECT DATE '2024-05-08', NULL UNION ALL
  SELECT DATE '2024-05-09', 13 UNION ALL
  SELECT DATE '2024-05-10', 14 UNION ALL
  SELECT DATE '2024-05-11', NULL UNION ALL
  SELECT DATE '2024-05-12', NULL
), filled_data AS (
  SELECT
    * EXCEPT(number_of_orders), 
    LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date) AS number_of_orders
  FROM raw_data
  ORDER BY date DESC
  # 여기에 ORDER BY를 추가해서 데이터 순서를 변경함
)

SELECT
  *,
  AVG(number_of_orders) OVER(ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg1, # 위에서 데이터를 정렬한 후 집계한 값
  AVG(number_of_orders) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg2 # 올바른 답
FROM filled_data

질문해주신 ORDER BY와는 살짝 다르지만, 윈도우 함수에서 프레임을 명시하지 않으면 기본 값은 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW가 됩니다. 관련 내용은 인프런 AI 인턴이 링크를 걸어준 글을 보시면 도움이 될 것 같아요

인프런 AI 인턴

안녕하세요, 인프런 AI 인턴입니다. 쿠쿸키님.

주신 질문에 대해 답변을 드리겠습니다. 윈도우 함수에서 ORDER BY 절을 사용하지 않을 경우, 데이터의 처리 순서에 대해 명시적인 기준이 없기 때문에 예상치 않은 결과가 나올 수 있습니다. 그러나, 기본적으로는 명시적인 ORDER BY 없이 윈도우 함수가 사용되면, 테이블의 순서에 따라 동작하게 되며, 이 경우 결과는 의도하지 않은 순서로 나타날 수 있습니다. 따라서, 일반적으로는 항상 명시적으로 ORDER BY 구문을 사용하여 결과의 순서를 보장해야 합니다.

하지만 특정한 인프라나 데이터셋의 크기, 구조에 따라 데이터가 저장된 순서대로 자연스럽게 처리되어 비슷한 결과가 나올 수도 있습니다. 이는 의도적이지 않은 결과로, 재현성을 보장하기 어렵습니다.

추가로, 여기서는 ORDER BY 절이 사용되지 않았음에도 결과가 예상과 같았다면, 주어진 데이터셋이 잘 정렬되었거나 AVG 함수의 특성상 범위 내 모든 데이터에 대해 동일한 계산이 이루어졌을 가능성이 큽니다.

관련된 추가 설명을 통해 이해를 돕기 위해 참고 글을 추천해드립니다:

윈도우 함수 Default 값에 대해 - 윈도우 함수의 기본값과 연관된 질문을 참고하세요. 특히 데이터 중복 처리 및 ORDER BY 프레임 설정에 대한 내용을 확인하실 수 있습니다.

저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다. 현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏 추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.

인프런 커뮤니티 질문&답변

이동평균 계산 시 order by 기본값은 뭔가요?