해결된 질문
24.07.22 01:19 작성
·
71
0
안녕하세요. 강의를 잘 듣고 있습니다.
다름이 아니라, 연습문제를 모두 풀고 나서 윈도우함수 강의를 듣는 과정에서, default에 대해 궁금한 점이 생겼습니다.
정확히, 계산을 해야할 경우, 중복된 데이터가 있는 경우를 고려해서, ROWS BETWEEN UNBOUNDED PRECEDINIG AND CURRENT ROW
라고 프레임의 범위를 정확하게 명시를 하는 것이 좋을 것 같다. 라는 생각이 들었습니다.
그 이유는 다음과 같습니다.
강의에서 말씀해주신대로, 구글 빅쿼리는 default 값이 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
라고 되어있는데요,
-- 4)
-- 누적 쿼리: 과거의 시간(UNBOUNDED PRECEDING)부터 current row까지
SELECT
*,
SUM(query_cnt) OVER(PARTITION BY user ORDER BY query_date) AS cumulative_sum
SUM(query_cnt) OVER(PARTITION BY user ORDER BY query_date ROWS BETWEEN
UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum2
FROM (
SELECT
query_date,
team,
user,
COUNT(user) AS query_cnt
FROM advanced.query_logs
GROUP BY ALL
)
-- QUALIFY cumulative_sum != cumulative_sum2
ORDER BY user, query_date
문득, default값이 RANGE BETWEEN UNBOUND PRECEDING AND CURRENT ROW
라면, 왜 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
와 같은 값을 가질까? 다른 값을 가지는 경우도 있지 않을까? 하는 의문이 들었습니다.
예제에서는, GROUP BY ALL을 했기 때문에, 중복 데이터가 있어도, query_cnt로 집계가 된 상태에서, 윈도우 함수를 실행하다보니, QUALIFY로 조건절 검증을 수행해도, !=
를 만족하는 데이터가 없었던 것 같았습니다.
그래서, 1번 예제와 4번 예제를 결합해서, 다음과 같이 검증을 수행해보았습니다.
-- INSERT INTO my_sess.query_logs (user, team, query_date)
-- VALUES ('샘', 'Data Science', '2024-04-24');
SELECT
*,
-- 시간의 흐름에 따라, 일자별 유저가 실행한 누적 총 쿼리 수
SUM(total_query_cnt) OVER(PARTITION BY user ORDER BY query_date) AS cumulative_total_sum,
SUM(total_query_cnt) OVER(PARTITION BY user ORDER BY query_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_total_sum2
FROM (
SELECT
*,
-- 사용자별 시간의 흐름에 따라 쿼리를 실행한 총 횟수: total_query_cnt
COUNT(query_date) OVER(PARTITION BY user) AS total_query_cnt
FROM
my_sess.query_logs
)
-- QUALIFY cumulative_total_sum != cumulative_total_sum2
ORDER BY user, query_date
결과는 다음과 같습니다.
중복데이터가 있을 경우, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW는 다 합쳐서 하나로 집계해서 결과값을 내놓는 것으로 보입니다.
QUALIFY 함수를 적용했을 때
cumulative_total_sum = cumulative_total_sum2 인 경우
데이터 중복이 없을 경우에는, 같은 값이 나옵니다.
답변 1
1
2024. 07. 22. 02:11
minsubrother님 안녕하세요! 열심히 학습하고 계시네요. default에 생각하신 부분이나 논리적으로 전개하신 부분 인상 깊게 봤습니다! 말씀해주신 부분 모두 맞는 말이에요
제 생각을 공유드리면
말씀하신 것처럼 명시적으로 하기 위해 ROWS BETWEEN UNBOUNDED PRECEDINIG AND CURRENT ROW을 사용하는 것도 가능합니다. 여기서 중요한 주제는 "데이터의 중복" 여부입니다. 저는 이런 경우엔 중복을 미리 제거하는 방식을 선호합니다(윈도우 함수에서 신경쓰지 않도록)
중복을 제거하면 ROWS나 RANGE나 동일한 결과가 가능성이 높아집니다. ORDER BY에서 사용하는 컬럼이 고유해지기 때문이에요
가독성 관점
윈도우 함수의 Frame이 쿼리가 복잡하게 만드는 이유가 되곤 합니다. 그래서 저는 Default를 써도 괜찮은 경우라면 Default를 쓰곤 하고, 이동 평균 같은 값을 구할 때 주로 Frame을 명시하곤 합니다
Frame 개념이 익숙해지면 괜찮기도 하지만, 회사에선 일을 동료들과 같이 진행합니다. 그래서 팀마다 쿼리를 어떻게 작성할지에 대한 규칙을 정하곤 합니다. 이 부분은 초보자를 위한 BigQuery(SQL 입문)에서 스타일 가이드라는 것을 알려드릴 때 공유드립니다(강의 넘버는 6-2)
윈도우 함수의 스타일 가이드는 많이 존재하진 않는데, 윈도우 함수나 중복 데이터를 처리하기 위한 규칙을 종종 만들었습니다. 쿼리 중간에 DISTINCT나 GROUP BY ALL을 사용해서 중복이 제거되었음을 명시적으로 보장하고, 그 후에 윈도우 함수를 사용하는 것. 이럴 땐 중복을 제거했기에 Default를 쓰는 것도 괜찮았습니다.
사용한다고 하면 저도 RANGE보다 ROWS를 선호합니다.
날짜 데이터에서 데이터가 누락이 되는 경우가 존재할 때 데이터를 어떻게 채울 것인가? 관점에서 고민하게 됩니다.
실제 존재하는 행만을 기준으로 계산해야 할수도 있고, 값이 없으면 0으로 처리해야 하는 경우도 존재합니다.
이럴 때 ROWS를 사용하는 것이 더 안전합니다. 누적 계산, 이동 평균을 할 때도 더 명확하지요.
말씀해주신 것처럼 이해하시면 될 것 같고, 본질적인 이해까지 잘 하셨네요!
중복 처리 관련은 데이터를 잘못 추출하는 것을 방지하기 위해 중간 중간 중복을 제거하고 명시적인 중복 제거를 표현하려는 제 습관이 있다고 봐주시면 좋을 것 같네요.
더불어 윈도우 함수에서 ROWS/RANGE 선택을 하는 것보다 그 앞단에서 중복 제거하는 것이 더 안전하고 여러 곳에서 활용할 수 있지요(윈도우 함수에서만 데이터가 사용되는 것은 아니기에)
정리하면
본질적으론 이 문제는 데이터 중복을 어떻게 처리할 것인가에 대한 문제. 데이터 중복 여부에 따라 달라짐
윈도우 함수쪽에서 데이터를 처리하는 것보다 근본적인 데이터를 처리하는 것이 더 좋을 수 있음
말씀하신 것처럼 명시적으로 ROWS를 사용하는 것도 좋음. 회사의 스타일 가이드에 따라 다를 것
별개로 초보자를 위한 BigQuery(SQL) 입문 강의에서 섹션 3, 6은 꼭 들어보셔도 좋을 것 같단 생각이 드네요. 어떻게 쿼리를 작성하고 검증할까에 대한 내용인데 minsubrother님의 논리적인 생각 과정과 어떻게 다른지 비교하면서 보셔도 좋을 것 같네요
열심히 학습하시는 모습을 보니 저도 영감을 받네요. 계속 질문 올려주셔요! 🙂
2024. 07. 22. 14:51
좋은 질문을 해주셔서 저도 여러 생각을 공유하게 되었네요. 저를 이렇게 답변하게 만들어주셔서 감사합니다!!
BigQuery 입문편이랑 활용편이 모두 연결되는 내용이라 입문편 보시면 새로 알게 되는 내용도 있을거에요. JOIN 같은 것은 이미 아실테니 2배속으로 보시고 제가 어떤 관점으로 일하는지, 어떤 생각을 가지는지, 실무에서 어떤 경험을 했는지 등을 보시면 도움이 될 것 같아요 🙂
2024. 07. 22. 13:13
하나의 질문에 대해 정성스러운 답변을 작성해주셔서 감사합니다. 어제 새벽에 확인했었는데, 감사합니다. 라는 짧은 인사는 좀 아닌 것 같아서 지금 와서야 답글을 달게 되었습니다.
실무에서는 SQL 쿼리 작성 규칙을 정의한다는 점이 흥미롭네요! 프로그래밍 언어와 다르게 유지보수가 힘들텐데 어떻게 관리를 할까... 라는 생각이 들었었는데, 체계적인 방식을 통해 이루어진다는 점이 와 닿았습니다.
그리고 윈도우 함수에서 프레임을 정의할 때마다, 쿼리가 복잡해진다고 느꼈었는데, 카일님 말씀대로, 중복을 명시적으로 제거한 뒤, 윈도우 함수를 사용하면 default로 가능한 경우에는 쿼리가 보기 편해질 것 같다는 생각이 듭니다.
그리고, 추가로 SQL 입문 강의도 꼭 들어봐야겠다는 생각이 들었습니다.
다시 한 번 좋은 답변을 달아주셔서 감사합니다.