📌 Array

• 여러개의 동일한 타입을 하나의 칼럼에 저장하는 데이터 타입

Array 생성하는 방법

1. 대괄호([])를 사용

• 간단한 배열 생성시 사용
• Bigquery, PostgreSQL에서 사용가능한 방법!

SELECT 
  [1, 2, 3, 4] AS numbers,
  ['apple', 'banana', 'cherry'] AS fruits;

2. ARRAY 함수 사용

• 대괄호보다 더 다양한 상황의 배열생성 (서브쿼리 결과)

• Bigquery, PostgreSQL에서 사용가능한 방법!

• MySQL에서는 JSON_ARRAY를 사용하며, Oracle에서는 PL/SQL에서 컬랙션 타입을 사용.

• ARRAY[value,...]

  SELECT 
    ARRAY[1, 2, 3, 4] AS numbers,
  		ARRAY['apple', 'banana', 'cherry'] AS fruits;
  		```
  

3. 배열 생성 함수 GENERATE_DATE_ARRAY 사용

• Bigquery에서 사용가능한 방법!
• 주어진 시작 및 끝 날짜 조건에 맞는 배열을 생성하는 함수
• 날짜 범위 생성에 사용
• GENERATE_DATE_ARRAY(start_date, end_date[, step])

  --- 2일 간격으로 20240101부터 20240110까지의 날짜 배열 생성
  -- 결과는 ["2024-01-01", "2024-01-03", "2024-01-05", "2024-01-07", "2024-01-09"] 배열 생성


    SELECT GENERATE_DATE_ARRAY('2024-01-01', '2024-01-10', INTERVAL 2 DAY) AS date_array;

4. ARRAY_AGG

• 그룹화된 결과를 배열로 집계하는데 사용하는 함수

• 여러 행의 값을 배열로 집계하여 데이터분석 및 보고서 작성에 유용

• Bigquery, PostgreSQL에서 사용가능한 방법!

• SQL server에서도 동일한 함수를 사용하나, 문자열 형태로 반환! XML, JSON으로 배열과 비슷한 형태로 반환하게 끔은 할 수 있다고 함.

  SELECT user_id, ARRAY_AGG(order_id) AS orders
  FROM orders
  GROUP BY user_id;
  

ARRAY 값에 접근하기

Bigquery에서 배열의 element에 접근하는데 OFFSET, ORDINAL, SAFE가 사용된다.
(Bigquery에서만 사용하는 기능!)

1. OFFSET

• 0부터 시작하는 인덱스로 접근

• 즉, 1번째 element는 offset(0)을 사용.

  SELECT my_array[OFFSET(0)] AS first_element 
  FROM (SELECT [10, 20, 30] AS my_array);
  

2. ORDINAL

• 1부터 시작하는 인덱스를 사용

• 1번째 element 접근시, ORDINAL(1)사용

  SELECT my_array[ORDINAL(1)] AS first_element  -- 첫 번째 요소
  FROM (SELECT [10, 20, 30] AS my_array);
  

3. SAFE

• 배열 인덱스에 접근시, 유효하지 않은 인덱스 오류 방지

• SAFE_OFFSET,SAFE_ORDINAL사용

• 존재하지 않는 인덱스 접근시, NULL 반환

  SELECT my_array[SAFE_OFFSET(3)] AS fourth_element  -- 배열의 길이가 3일 경우 NULL 반환
  FROM (SELECT [10, 20, 30] AS my_array);
  
  SELECT my_array[SAFE_ORDINAL(4)] AS fourth_element  -- 배열의 길이가 3일 경우 NULL 반환
  FROM (SELECT [10, 20, 30] AS my_array);
  

⭐ UNNEST

• 배열의 element를 table로 변환
• 중첩된 셀에서 하나의 데이터 셀로 변환
• 평면화 Flatten

  	```
  SELECT 
  	*
  FROM table_A
  ,UNNEST(col) AS name
  ```

  또는

  	SELECT 
  		*
  	FROM table_A
  	CROSS JOIN UNNEST(col) AS name
  	```

---

📌 STRUCT

• ARRAY와 달리 자료형이 다른 데이터들의 집합

STRUCT 생성

1. ()를 사용하여 생성
2. STRUCT(val,...)

---

📌 PIVOT

• 엑셀의 피봇과 동일한 개념
• PIVOT : 회전하다. 축을 돌리다 -> 행과 열을 변환
  -> ROW 수를 줄여 효율적으로 쿼리작동
  
  피봇 후
  

1. ⭐ MAX, IF, GROUP BY를 사용하기

• BIGQUEY는 PIVOT()을 지원하지 않음.
• PIVOT시 모든 값이 같을 경우 MAX 혹은 ANY_VALUE를 사용
• 집계함수를 사용하기 때문에 GROUP BY 사용해주기

SELECT 
    MONTH, 
    MAX(IF(PRODUCT = 'A', SALESAMOUNT, NULL)) AS A,
    MAX(IF(PRODUCT = 'B', SALESAMOUNT, NULL)) AS B
FROM SALES
GROUP BY MONTH;

2. PIVOT()을 사용하기

• BigQuery에서도 지원!! 👉BigQuery PIVOT 사용법(Pivot Operator):어쩐지 오늘은(카일스쿨)

• MYSQL, ORACLE에서 사용. POSTGRESQL은 CROSSTAB 사용

  SELECT *
  FROM (
    SELECT Month, Product, SalesAmount
    FROM Sales
  ) AS SourceTable
  PIVOT (
    SUM(SalesAmount)
    FOR Product IN ([A], [B])
  ) AS PivotTable;
  

📌 퍼널 분석(FUNNEL)

• 퍼널 : 사용자의 행동 흐름을 시각적으로 표현할 때 사용되는 용어. (예: 방문, 회원가입, 장바구니, 결제..)
• 세션, 대표적으로 유저, 페이지뷰 단위로 퍼널 분석
• 이탈, 전환률을 분석하여 전략을 수립할 수 있음

퍼널 종류

1. OPEN 퍼널 : 순서없이 진행됨
2. CLOSED 퍼널 : 순서대로 퍼널이 진행되어야 함

👉 함께 읽으면 좋은 글

• 퍼널 최적화 전략

• 퍼널은 줄일수록 좋은걸까?

  ---

⭐ 중요 개념 및 함수

• UNNEST
• PIVOT (MAX, IF, GROUP BY)
• 퍼널 분석

⭐ 습관들이기

1. 쿼리작성시 예약어 대문자, \n 해주는 습관들이기
2. UNNEST시에 AS 까먹지 말기
3. PIVOT시에 집계함수를 사용하니 꼭 GROUP BY 해주기!!!
4. 퍼널 분석 익숙해지기!

---

참고

• BigQuery PIVOT 사용법(Pivot Operator):어쩐지 오늘은(카일스쿨)
• BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석):인프런(카일스쿨)
• ChatGPT: PIVOT 그림