윈도우 함수 문제 풀이

S.T.A track/BigQuery

윈도우 함수 문제 풀이

Eprld 2025. 5. 13. 10:59

1) 사용자별 쿼리를 실행한 총 횟수를 구하는 쿼리를 작성해주세요. 단, GROUP BY를 사용해서 집계하는 것이 아닌 query_logs의 데이터의 우측에 새로운 컬럼을 만들어주세요.

--1번 의도 : COUNT를 윈도우 함수로 쓸 수 있는가?

--로그 : 통나무에서 왔음, 히스토리, 기록
--로그성 데이터 : 기록이 한 줄에 하나씩(Row). Raw. 이벤트 데이터 > 어떤 유저가 무엇을 했다
  --고객에게(유저에게) 노출을 하지 않음
  --회사마다 다름. AWS 라는 클라우드의 저장소 -> 데이터 웨어하우스, 데이터베이스
  --Google Analytic 4, Firebase 플랫폼을 사용해서 데이터 저장하면 생기는 형태
  --앱 로그, 웹 로그
  --형태가 개발자가 어떻게 개발하냐에 따라서 그냥 못생긴 형태도 있음
  --데이터 엔지니어가 Table 형태로 가공해서 줌
--데이터베이스 데이터 : 거래와 관련된 데이터. 배민. 주문 데이터. 주문 목록
--MySQL, PostgreSQL, Oracle

SELECT

--사용자별! => user

COUNT(*) OVER(PARTITION BY user) AS cnt

FROM `advanced.query_logs`

ORDER BY user, query_date

2) 주차별로 팀 내에 서 쿼리를 많이 실행한 수를 구한 후, 실행한 수를 활용해 랭킹을 구해주세요. 단, 랭킹이 1등인 사람만 결과가 보이도록 해주세요

--우리가 만들어야 하는 것 : WEEK | user | team | query_date | cnt

--rk 가 1인 것만

--COUNT 그냥 집계랑, 윈도우 COUNT를 언제 해야할까? 기준이 있을까?

--데이터 형태에 맞게 적절하게 수행을 하는데, 최종적으로 보여줄 형태의 데이터에서

--하나의 Row 우측에 컬럼을 추가할 것인가? => 윈도우 함수

--한 번 집계를 하고 그 결과에 우측에 컬럼을 붙여야 하는가? => 그 전에는 집계 함수, 그 후에 윈도우 함수

--문제

--주차별로 뽑자 => EXTRACT(WEEK )

--랭킹을 구하자

--랭킹이 1인 것만 남기자

--1) 서브 쿼리 방식

SELECT

FROM(

SELECT

EXTRACT(WEEK FROM query_date) AS week,

team,

user,

COUNT(*) AS cnt

FROM advanced.query_logs

GROUP BY ALL

)

--1) WITH 방식

WITH base AS(

SELECT

EXTRACT(WEEK FROM query_date) AS week,

team,

user,

COUNT(*) AS cnt

FROM advanced.query_logs

GROUP BY ALL

)

SELECT

RANK() OVER(PARTITION BY week, team ORDER BY cnt DESC) AS rk,

DENSE_RANK() OVER(PARTITION BY week, team ORDER BY cnt DESC) AS dense_rk

--데이터의 결과는 같지만 데이터 형태에 따라 다를 수가 있다.

FROM base

--1) QULIFY 사용법

WITH base AS(

SELECT

EXTRACT(WEEK FROM query_date) AS week,

team,

user,

COUNT(*) AS cnt

FROM advanced.query_logs

GROUP BY ALL

)

SELECT

RANK() OVER(PARTITION BY week, team ORDER BY cnt DESC) AS rk,

DENSE_RANK() OVER(PARTITION BY week, team ORDER BY cnt DESC) AS dense_rk

FROM base

QUALIFY rk = 1

--SELECT 절 뒤에 실행이 되어서 WHERE대신 사용한다

3) (2번 문제에서 사용한 주차별 쿼리 사용) 쿼리를 실행한 시점 기준 1주 전에 쿼리 실행 수를 별도의 컬럼으로 확인할 수 있는 쿼리를 작성해주세요

WITH base AS(

SELECT

EXTRACT(WEEK FROM query_date) AS week,

team,

user,

COUNT(*) AS cnt

FROM advanced.query_logs

GROUP BY ALL

)

SELECT

LAG(cnt) OVER(PARTITION BY user ORDER BY week) AS lag_cnt

--LAG 안에 있는 컬럼 : 내가 그 컬럼을 보고 싶다

--ORDER BY는 파티션 내의 정렬이 목적

--파티션 바이 / 오더 바이 => LAG를 하겠구나

FROM base

--3번 의도 : LAG 의도

4) 시간의 흐름에 따라, 일자별로 유저가 실행한 누적 쿼리 수를 작성해주세요

--4번 의도 : SUM 집계 부석 함수를 쓸 수 있을까?

--COUNT 집계 함수(GROUP BY), 집계 분석 함수(PARTITION BY)

WITH base AS(

SELECT

query_date,

team,

user,

COUNT(*) AS cnt

FROM advanced.query_logs

GROUP BY ALL

)

SELECT

SUM(cnt) OVER(PARTITION BY user ORDER BY query_date) AS cummurative_cnt

--COUNT 함수 대신 SUM

--COUNT 집계 함수로 GROUP BY를 한 후에, 그 값을 SUM하면

FROM base

ORDER BY user, query_date

5) 다음 데이터는 주문 횟수를 나타낸 데이터입니다. 만약 주문 횟수가 없으면 NULL로 기록됩니다. 이런 데이터에서 NULL 값이라고 되어있는 부분을 바로 이전 날짜의 값으로 채워주는 쿼리를 작성해주세요

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

)

SELECT

IFNULL(number_of_orders, LAST_VALUE(number_of_orders IGNORE NULLS) OVER(ORDER BY date)) AS lead_orders

--OVER절 안에 PARTITION BY가 없어도 되냐? => 데이터의 형태에 따라서 그럴 수 있다.

--이미 집계가 된 데이터면 PARTITION BY 쓰는 경우도 있을 것

FROM raw_data

--주문 횟수 데이터

--주문 횟수가 없으면 NULL

--NULL이라고 되어 있으면, 이전 날짜의 값으로 채워라!

--연속 두 번 NULL인 케이스를 확인해라

6) 5번 문제에서 NULL을 채운 후, 2일 전 ~ 현재 데이터의 평균을 구하는 쿼리를 작성해주세요(이동 평균)

--평균 : AVG

--윈도우 함수에서 배운 Frame이란 개념을 쓰면 된다.

SELECT

ROUND(AVG(number_of_orders) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg

FROM base2