[SQL] 유저 데이터 분석에서 유용한 ROW_NUMBER, LAG 함수

오늘은 다음과 같은 유저 데이터 분석에서의 특정한 상황을 가정해 봅시다.

아래 질문을 해결해나가며, ROW_NUMBER와 LAG 함수의 활용에 대해 알아봅시다.

유저가 방문한 사이트를 구분해서 유저의 행동 패턴을 집계하고 싶어.
특정 이벤트 이전에 어떤 이벤트가 일어났는지 궁금해
하지만, 1번째 방문한 경우와 2번째 방문한 경우를 다르게 구분 짓고 싶어.
그러니까 내 말은.. 단순히 GROUP BY SITE 구문으로는 불가능하단 말이야.

어떻게 할 수 있을까?

 

본 글은 기본적인 SQL 구문을 알고 있다고 가정합니다.
그리고 윈도우 함수(LAG, ROW_NUMBER 등)의 정의를 들어본 적은 있어야 합니다.
매번 함수의 정의를 다시 찾아보고 실제 데이터 정제나 분석 업무에서 활용하기 어려운 사람을 대상으로 설명합니다.
작성된 예제 쿼리는 모두 빅쿼리를 사용했습니다.

 

 

샘플 데이터 소개

다음과 같은 유저 데이터가 있다고 가정합니다.

유저는 사이트를 이동하면서 탐색할 수 있습니다. 우리의 목표는 사이트별로 유저의 탐색 패턴을 집계하는 것입니다.

다만, 처음 1번 사이트를 방문한 경우와 2번 사이트를 탐색한 후에 다시 1번 사이트를 방문한 경우는 서로 다른 패턴으로 집계합니다.

행	user_id	site_id	event_type	timestamp
1	1	1	click	2023-01-01 10:00:00 UTC
2	1	1	add_to_cart	2023-01-01 10:10:00 UTC
3	1	1	click	2023-01-01 12:00:00 UTC
4	1	1	click	2023-01-01 12:06:00 UTC
5	1	2	add_to_cart	2023-01-01 12:01:00 UTC
6	1	1	add_to_cart	2023-01-01 12:10:00 UTC
7	1	2	search	2023-01-01 13:00:00 UTC
8	1	2	add_to_cart	2023-01-01 13:01:00 UTC
9	2	1	search	2023-01-01 11:28:00 UTC
10	2	1	add_to_cart	2023-01-01 11:30:00 UTC
11	2	1	add_to_cart	2023-01-01 11:32:00 UTC
12	2	2	click	2023-01-01 12:30:00 UTC
13	2	2	click	2023-01-01 12:31:00 UTC
14	2	2	search	2023-01-01 12:32:00 UTC
15	2	1	search	2023-01-01 14:28:00 UTC
16	2	1	click	2023-01-01 15:30:00 UTC
17	2	1	add_to_cart	2023-01-01 15:32:00 UTC

 

아래는 위 샘플 데이터 생성 코드입니다.

 

CREATE TABLE sample_data.user_clicks (
  user_id INT,
  site_id INT,
  event_type STRING,
  timestamp TIMESTAMP
); 

INSERT INTO sample_data.user_clicks VALUES
(1, 1, 'click', '2023-01-01 10:00:00'),
(1, 1, 'add_to_cart', '2023-01-01 10:10:00'),
(1, 1, 'click', '2023-01-01 12:00:00'),
(1, 2, 'add_to_cart', '2023-01-01 12:01:00'),
(1, 1, 'click', '2023-01-01 12:06:00'),
(1, 1, 'add_to_cart', '2023-01-01 12:10:00'),
(1, 2, 'search', '2023-01-01 13:00:00'),
(1, 2, 'add_to_cart', '2023-01-01 13:01:00'),
(2, 1, 'search', '2023-01-01 11:28:00'),
(2, 1, 'add_to_cart', '2023-01-01 11:30:00'),
(2, 1, 'add_to_cart', '2023-01-01 11:32:00'),
(2, 2, 'click', '2023-01-01 12:30:00'),
(2, 2, 'click', '2023-01-01 12:31:00'),
(2, 2, 'search', '2023-01-01 12:32:00'),
(2, 1, 'search', '2023-01-01 14:28:00'),
(2, 1, 'click', '2023-01-01 15:30:00'),
(2, 1, 'add_to_cart', '2023-01-01 15:32:00');

 

 

ROW_NUMBER() 함수

유저가 방문한 사이트가 같더라도
방문할 때마다 다른 ID로 구분하고 싶다면 어떻게 쿼리를 작성해야 할까요?

 

우선 ROW_NUMBER() 함수를 사용하여 timestamp 컬럼을 기준으로 순서를 지정합니다.

이때, ROW_NUMBER() 함수는 구간 내에서(파티션이 있다면 파티션 내에서, 없다면 전체에서) 쿼리 결과의 현재 행 번호를 반환합니다. 

우리는 ORDER BY timestamp로 순서를 지정했으므로, timestamp 기준으로 오름차순 정렬한 뒤 행 번호를 매겼다고 생각하면 됩니다.

SELECT *, ROW_NUMBER() OVER (ORDER BY timestamp) AS time_order
FROM `sample_data.user_clicks`
ORDER BY time_order

 

추가로 ROW_NUMBER() 함수를 지정합니다. 이번에는 site_id로 파티션을 지정하고, site_order로 이름을 정합니다.

SELECT 
  *, 
  ROW_NUMBER() OVER (ORDER BY timestamp) AS time_order, 
  ROW_NUMBER() OVER (PARTITION BY site_id ORDER BY timestamp) AS site_order,
FROM `sample_data.user_clicks`
ORDER BY user_id, timestamp

쿼리의 결과를 보면 사이트 구분을 어떻게 할 수 있을지 감이 안 옵니다.

 

이젠 정말 구분할 수 있도록 time_order 컬럼에서 site_order 컬럼의 값을 빼고 session_id라고 지정해 보겠습니다.

SELECT 
  *, 
  ROW_NUMBER() OVER (ORDER BY timestamp) AS time_order, 
  ROW_NUMBER() OVER (PARTITION BY site_id ORDER BY timestamp) AS site_order,
  (
    ROW_NUMBER() OVER (ORDER BY timestamp) 
    - ROW_NUMBER() OVER (PARTITION BY site_id ORDER BY timestamp)
  ) AS session_id
FROM `sample_data.user_clicks`
ORDER BY user_id, timestamp

위 결과에서 1번 유저가 첫 번째로 1번 사이트를 방문한 경우는 session_id 가 0으로,

두 번째로 1번 사이트를 방문한 경우는 1로 session_id 가 구분되어 있는 것을 볼 수 있습니다.

 

LAG 함수

세션별 유저의 행동 이벤트의 패턴을 비교하려면 어떻게 해야 할까요? 

 

위에서 같은 사이트여도, 방문마다 다른 세션으로 구분을 해두었습니다.

이제 세션별로 유저의 행동 이벤트를 나열해서 패턴을 살펴봅시다.

 

우선 특정 이벤트 이전에 어떤 이벤트가 발생했는지 살펴봅시다.

LAG 함수는 현재 행에서 이전 행의 값을 가져올 때 사용되는 윈도우 함수입니다. 

특별히 offset을 지정하지 않으면 구간 내 1개 행 이전의 값을 가져옵니다.

SELECT
  *,
  LAG(event_type) OVER (
    PARTITION BY session_id, user_id ORDER BY timestamp
  ) AS prev_event_type,
FROM (
  SELECT 
    *, 
    ROW_NUMBER() OVER (ORDER BY timestamp) - ROW_NUMBER() OVER (PARTITION BY site_id ORDER BY timestamp) AS session_id
  FROM `sample_data.user_clicks`
)
ORDER BY user_id, timestamp

 

위 데이터의 통계를 살펴볼까요?

WITH data AS (
  SELECT
    *,
    LAG(event_type) OVER (
      PARTITION BY session_id, user_id ORDER BY timestamp
    ) AS prev_event_type,
  FROM (
    SELECT 
      *, 
      ROW_NUMBER() OVER (ORDER BY timestamp) - ROW_NUMBER() OVER (PARTITION BY site_id ORDER BY timestamp) AS session_id
    FROM `sample_data.user_clicks`
  )
)
SELECT event_type, prev_event_type, COUNT(*) AS count
FROM data
GROUP BY event_type, prev_event_type
ORDER BY event_type, prev_event_type

장바구니 담기(add_to_cart) 이전에 클릭(click) 이벤트가 발생한 경우가 3건인 것을 확인할 수 있습니다.

 

이번에는 이벤트의 세션별 이벤트 발생 순서대로 패턴을 확인해 봅시다.

빅쿼리에서 제공하는 STRING_AGG 함수를 사용하여 유저, 사이트, 세션으로 구분하여 이벤트를 묶어서 볼 수 있습니다.

SELECT
  user_id, 
  site_id,
  session_id,
  STRING_AGG(event_type, " | " ORDER BY timestamp) AS event_type_pattern
FROM (
  SELECT 
    *, 
    ROW_NUMBER() OVER (ORDER BY timestamp) - ROW_NUMBER() OVER (PARTITION BY site_id ORDER BY timestamp) AS session_id
  FROM `sample_data.user_clicks`
)
GROUP BY user_id, site_id, session_id
ORDER BY user_id, site_id, session_id

 

클릭(click), 검색(search) 이벤트 뒤에 장바구니 담기(add_to_cart) 이벤트가 발생한 경우가 많고,

장바구니 담기 이벤트를 마지막으로 패턴이 끝나는 경우가 많은 것을 확인할 수 있겠습니다.

 

마무리

오늘 알아본 LAG 함수는 이전 행의 값을 가져와 현재 행에서 사용하며,

ROW_NUMBER 함수는 각 행에 일련번호를 부여하는 유용한 윈도우 함수입니다.

이러한 함수들을 활용하면 데이터 분석이나 비교적 복잡한 쿼리 작성에 효과적으로 활용할 수 있습니다.

이번 글을 통해 업무에 자유롭게 사용할 수 있고 복잡한 데이터 정제 요구 사항을 수행할 수 있게 되기를 응원합니다!

 

 

참고 문서

https://cloud.google.com/bigquery/docs/reference/standard-sql/numbering_functions#row_number

Numbering functions  |  BigQuery  |  Google Cloud

https://cloud.google.com/bigquery/docs/reference/standard-sql/navigation_functions#lag

Navigation functions  |  BigQuery  |  Google Cloud

https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions#string_agg

Aggregate functions  |  BigQuery  |  Google Cloud