[인프런 빅쿼리 빠짝스터디 1주차] SQL 스터디 제품현황 분석 : 퍼널분석, PIVOT, ARRAY, STRUCT, UNNEST

인프런에서 빅쿼리 SQL 스터디 1주차는 다양한 SQL 분석 기법을 활용해 데이터를 심도있게 분석하는 것을 목표로 합니다. 특히, 퍼널분석과 데이터 구조의 변환에 초점을 맞추어 ARRAY(배열), STRUCT(구조체), pivot, UNNEST와 같은 빅쿼리의 주요 기능들을 학습합니다. 이러한 기능들을 통해 데이터의 구조를 효율적으로 다루고 분석할 수 있습니다.

 

 

🔑 중요 키워드: 퍼널분석, pivot, ARRAY, STRUCT, UNNEST



📄 1. 퍼널분석 개념 정의 

퍼널 분석이란?

퍼널 분석은 사용자가 서비스 내에서 진행하는 주요 단계(이벤트)를 분석하는 기법으로, 각 단계에서 사용자가 얼마나 이탈하는지 확인할 수 있습니다. 퍼널 분석을 통해 개선해야 할 부분을 파악하고, 서비스의 전환율을 높일 수 있습니다.

이미지 출처 : https://webengage.com/blog/funnel-analysis-for-payment-companies/

 

 

사용한 데이터:

ecommerce_logs 전자상거래 데이터 
(*다음 SQL 쿼리 예시는 강의내용 데이터가 아닌 임의로 변형해서 작성했습니다.) 

퍼널 단계:
homepage, category_view, product_view, add_to_cart, checkout, purchase
이벤트명과 화면(firebase_screen)을 연결하여 퍼널의 각 단계를 분석합니다.

기간: 2023-01-01 ~ 2023-01-10

 

 

1. 퍼널 단계:
   • homepage: 사용자가 메인 페이지에 도착한 이벤트
   • category_view: 특정 카테고리 페이지를 본 이벤트
   • product_view: 제품 상세 페이지를 본 이벤트
   • add_to_cart: 장바구니에 상품을 담은 이벤트
   • checkout: 결제 화면으로 이동한 이벤트
   • purchase: 실제 구매가 완료된 이벤트
2. 데이터 필터링:
   • event_name과 firebase_screen 값을 결합하여 퍼널 단계를 추적합니다.
   • 기간은 2023년 1월 1일부터 1월 10일까지로 설정했습니다.
3. 결과:
   • 각 날짜별로 퍼널 단계에서 몇 명의 사용자가 해당 이벤트를 수행했는지 집계합니다.
   • step_number는 퍼널의 각 단계를 나타내며, 해당 단계에서 사용자의 이탈률을 계산할 수 있습니다.

예시 데이터 

event_date	event_timestamp	event_name	user_id	user_pseudo_id	event_params
1/1/23	1.6725E+15	screen_view	1	ABC123	[{"key": "firebase_screen", "value": {"string_value": "homepage"}}]
1/1/23	1.6725E+15	screen_view	1	ABC123	[{"key": "firebase_screen", "value": {"string_value": "category_view"}}]
1/1/23	1.6725E+15	screen_view	1	ABC123	[{"key": "firebase_screen", "value": {"string_value": "product_view"}}, {"key": "product_id", "value": {"int_value": 101}}]

 

퍼널 분석 결과 데이터 예시 

event_date	event_name_firebase_screen	step_number	cnt
1/1/23	screen_view-homepage	1	5
1/1/23	screen_view-category_view	2	4
1/1/23	screen_view-product_view	3	4
1/1/23	screen_view-add_to_cart	4	3
1/1/23	click_checkout	5	2
1/1/23	purchase	6	2

 

 

 

📄 2. PIVOT 개념 정의 및 SQL 코드

 

개념 정의 : PIVOT은 데이터를 행에서 열로 변환하는 방법입니다.특히 많은 행을 열로 변환하여 데이터의 가독성을 높이고, 성능을 향상시킬 수 있습니다. 
즉, long format의 데이터 행에서 wide format의 데이터 열로 변환하는 것입니다. 

<pivot 전 - long format 데이터> 

student	subject	score
a	수학	100
a	영어	90
a	국어	80
b	수학	82
b	영어	93
b	국어	100

 

 

<pivot 중>

student	subject	score	수학	영어	국어
a	수학	80	100
a	영어	90		90
a	국어	100			80
80b	수학	82	82
b	영어	93		93
b	국어	100			100

 

<pivot 후 -  wide format의 데이터 > 

student	수학	영어	국어
a	100	90	80
b	82	93	100

 

 

PIVOT 피봇 SQL 예시 코드 :

SELECT student,
       MAX(IF(subject = '수학', score, NULL)) AS math,
       MAX(IF(subject = '영어', score, NULL)) AS english,
       MAX(IF(subject = '과학', score, NULL)) AS science
FROM scores
GROUP BY student;

 

📊  PIVOT SQL 적용한 쿼리테스트 문제풀이 보러가기 

 

 

📄 3. ARRAY 개념 정의 및 SQL 코드

 

ARRAY는 Python의 list와 유사한 구조로, 동일한 타입의 여러개 값을 하나의 컬럼에 저장할 수 있는 자료형입니다.
다양한 빅쿼리 함수와 함께 사용되며, 배열을 다루는 데 유용합니다.

 

사용법: 대괄호([])를 사용하여 배열을 정의하고, ARRAY<자료형> 혹은 배열 생성 함수를 통해 배열을 생성할 수 있습니다.

 

product_id	product_name	prices
1	스마트폰	[1000, 1200, 1300]
2	노트북	[1500, 1800, 2000]
3	태블릿	[600, 650, 700]

 

코드 예시:

SELECT [1000, 1200, 1300] AS smartphone_prices,
       [1500, 1800, 2000] AS laptop_prices,
       [600, 650, 700] AS tablet_prices;

 

배열 생성 함수 사용 예시:

SELECT GENERATE_DATE_ARRAY('2024-01-01', '2024-02-01', INTERVAL 1 WEEK) AS week_dates;

 

 

ARRAY의 데이터 접근:
배열 요소에 접근할 때는 OFFSET 또는 ORDINAL을 사용합니다.

SELECT product_name,
       prices[SAFE_OFFSET(0)] AS first_price
FROM products;

 

 

코드 결과 : 

 

product_name	first_price
스마트폰	1000
노트북	1500
태블릿	600

 

 

📄 4. STRUCT 개념 정의 및 SQL 코드

 

STRUCT는 서로 다른 타입의 여러 값을 하나의 컬럼에 저장할 수 있는 자료형으로, Python의 dict와 비슷합니다.
여러 필드를 그룹화하여 구조체로 관리할 수 있습니다.

사용법: 소괄호 ()를 사용하거나, STRUCT<자료형>(데이터) 형태로 STRUCT를 정의합니다.

 

customer_id	customer_details
101	STRUCT('홍길동', '서울', '010-1234-5678')
102	STRUCT('김영희', '부산', '010-9876-5432')
103	STRUCT('이철수', '대구', '010-1111-2222')

 

코드 예시:

SELECT STRUCT<STRING, STRING, STRING>('홍길동', '서울', '010-1234-5678') AS customer_info;

customer_info

{홍길동, 서울, 010-1234-5678}

 

STRUCT의 데이터 접근:

SELECT customer_info.name
FROM (SELECT STRUCT('홍길동', '서울', '010-1234-5678') AS customer_info);

name

홍길동

 

 

🧐 QUIZ 질문 : ARRAY와 STRUCT의 차이는? 

정답 : ARRAY는 동일한 타입의 여러개 값을 하나의 컬럼에 저장,
STRUCT는 서로 다른 타입의 여러 값을 하나의 컬럼에 저장 

 

📄 5. UNNEST개념 정의 및 SQL 코드

UNNEST는 ARRAY나 STRUCT와 같은 중첩된 데이터 구조를 평면화(Flatten)하는 데 사용됩니다.
특히 ARRAY 내의 요소들을 각기 다른 행으로 변환하는 데 유용합니다.

사용법: CROSS JOIN UNNEST() 혹은
                                 ,  UNNEST() 를 사용하여 배열을 행으로 변환합니다.

예시 테이블: movie_cast

movie_id	movie_name	actors
1	베놈	[STRUCT('톰 하디', '에디 브록'), STRUCT('우디 해럴슨', '클레투스 캐서디')]
2	스파이더맨	[STRUCT('톰 홀랜드', '피터 파커'), STRUCT('젠데이아', 'MJ')]

SELECT movie_name,
       actor_info.name AS actor_name,
       actor_info.character AS character_name
FROM movie_cast,
     UNNEST(actors) AS actor_info;

 

결과 : 

movie_name	actor_name	character_name
베놈	톰 하디	에디 브록
베놈	우디 해럴슨	클레투스 캐서디
스파이더맨	톰 홀랜드	피터 파커
스파이더맨	젠데이아	MJ

 

 

 

이 예시를 통해 ARRAY, STRUCT, UNNEST의 개념과 활용법을 쉽게 이해할 수 있습니다. 다양한 데이터 분석과 변형에 이러한 구조를 응용하면 데이터를 더 유연하게 다룰 수 있습니다.

 

🙌 더 자세한 내용은 카일스쿨님 블로그 및 인프런 Bigquery(SQL) 활용편 강의에서 만나볼 수 있습니다. 

>> 카일스쿨 블로그 보러가기