인프런 커뮤니티 질문&답변
작성한 질문수
[빠짝스터디 2주차 과제] 윈도우 함수 연습문제
작성
·
38
·
수정됨
0
이번주는 평일 주말에 일이 많아서 따라가기 힘들었네요 ㅎㅎ
그치만 마무리해서 올립니다!!
#유저들의 다음 접속 월, 다다음 접속 월
SELECT user_id,visit_month,
LEAD(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month),
LEAD(visit_month, 2) OVER(PARTITION BY user_id ORDER BY visit_month)
FROM advanced.analytics_function_01
#유저들의 다음 접속월, 다다음 접속 월, 이전 접속 월
SELECT user_id,visit_month,
LEAD(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month),
LEAD(visit_month, 2) OVER(PARTITION BY user_id ORDER BY visit_month),
LAG(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month)
FROM advanced.analytics_function_01
ORDER BY user_id
#diff 구하기
SELECT user_id,visit_month,
LEAD(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month),
LEAD(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) - visit_month
FROM advanced.analytics_function_01
ORDER BY user_id
SELECT after_visit_month - visit_month
FROM(
SELECT user_id,visit_month,
LEAD(visit_month) OVER(PARTITION BY user_id ORDER BY visit_month) AS after_visit_month
FROM advanced.analytics_function_01
ORDER BY user_id
)
#윈도우함수
SELECT *,
SUM(amount) OVER () AS amount_total,
SUM(amount) OVER (ORDER BY order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum,
SUM(amount) OVER (PARTITION BY user_id ORDER BY order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum_user,
AVG(amount) OVER (ORDER BY order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS last_5_avg
FROM advanced.orders
ORDER BY order_id
#윈도우함수 연습문제 1
SELECT *, COUNT(user) OVER(PARTITION BY user)
FROM advanced.query_logs
#윈도우함수 연습문제 2
WITH nums AS (
SELECT EXTRACT(week FROM query_date) AS week_number,
team,
user
FROM advanced.query_logs
)
, numss AS (
SELECT week_number, team, user, COUNT(user) AS query_cnt
FROM nums
GROUP BY week_number, team, user
)
, rnks AS (
SELECT *, RANK() OVER (PARTITION BY week_number, team ORDER BY query_cnt DESC) AS rk
FROM numss
ORDER BY week_number, team, user
)
SELECT *
FROM rnks
WHERE rk = 1
#윈도우함수 연습문제 3
WITH nums AS (
SELECT EXTRACT(week FROM query_date) AS week_number,
team,
user
FROM advanced.query_logs
)
, numss AS (
SELECT week_number, team, user, COUNT(user) AS query_cnt
FROM nums
GROUP BY week_number, team, user
)
SELECT user, team, week_number, query_cnt, LAG(query_cnt) OVER (PARTITION BY user ORDER BY week_number)
FROM numss
ORDER BY user, team
#윈도우함수 연습문제 4
WITH cntcnt AS (
SELECT user, team, query_date, COUNT(user) AS query_count
FROM advanced.query_logs
GROUP BY user, team, query_date
)
SELECT user, team, query_date, query_count, SUM(query_count) OVER (PARTITION BY user ORDER BY query_date) AS cnt
FROM cntcnt
#윈도우함수 연습문제 5
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
)
, raws AS (
SELECT date, number_of_orders, LAG(number_of_orders) OVER(ORDER BY date) AS befores
FROM raw_data
)
SELECT date, COALESCE(number_of_orders, befores)
FROM raws
#윈도우함수 연습문제 6
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
)
, raws AS (
SELECT date, number_of_orders, LAG(number_of_orders) OVER(ORDER BY date) AS befores
FROM raw_data
)
, dates AS (
SELECT date, COALESCE(number_of_orders, befores) AS orders
FROM raws
)
SELECT date, orders, AVG(orders) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
FROM dates
#윈도우함수 연습문제7
마지막 문제는 스스로 풀진 못했고 강의의 힘을 빌려 마무리했습니다! ㅎㅎ
답변 1
0
안녕하세요, 인프런 AI 인턴입니다. Min Choi님.
BigQuery의 윈도우 함수를 활용하여 다양한 쿼리를 작성하신 것을 보니, 열심히 공부하고 계신 모습이 인상 깊습니다. 윈도우 함수는 데이터 분석에서 매우 강력한 도구이며, 사용법을 익혀두시면 큰 도움이 될 것입니다. 작성하신 쿼리를 바탕으로 몇 가지 사항을 추가 설명드리겠습니다.
LEAD 및 LAG 함수: LEAD는 다음 행의 값을 가져오고, LAG는 이전 행의 값을 가져오는 데 사용됩니다. 이 함수들을 활용하면 유저들의 접속 패턴을 분석할 수 있으며, 특이한 행동을 감지하는 데 유용합니다.
윈도우 함수의 파티셔닝:
PARTITION BY를 사용하여 유저를 기준으로 각자의 데이터를 분리한 후 순서를 지정하여 계산합니다. 이렇게 하면 각 유저의 행동 패턴을 독립적으로 분석할 수 있습니다.COALESCE 함수: NULL 값을 처리할 때 유용합니다. 이번 과제에서는 COALESCE를 사용하여 이전 값으로 NULL을 대체하는 로직을 구현하셨습니다.
선형 회귀와 윈도우 함수: 일부 연습문제에서는 선형 회귀처럼 데이터를 구성하여 평균이나 누적 합계를 산출하셨습니다. 이런 방식은 시계열 데이터에서 패턴을 찾거나 이상치를 탐지할 때 유용합니다.
아래의 링크는 윈도우 함수와 관련된 유사한 게시글입니다. 추가적인 참고가 필요하실 경우 도움이 될 수 있습니다.
저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다. 현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏 추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.