Data/SQL - Bigquery

[SQL] Bigquery : 여러 조건으로 그룹화 하는 Grouping Sets

Derek Grey 2025. 1. 5. 22:21
반응형

 

 

 

1. Grouping Sets

 

GROUPING SETS 함수는 복잡한 집계 쿼리를 단순화하고 여러 그룹화 수준에서 집계를 한 번에 계산할 때 유용한 함수입니다. 일반적으로 GROUP BY 를 사용하여 해결할 수 있지만, GROUP BY 를 사용하다가 더 세분화해서 사용할 수 있는 쿼리는 없을까? 라고 생각하면 GROUPING sets를 사용하면 됩니다.

 

예시로, GROUPING SETS와 일반 GROUP BY의 차이점은 여러 그룹화 수준을 한 번에 처리할 수 있는 능력입니다.

 

이와 같은 테이블이 존재한다고 가정해보자. 그리고, GROUP BY 를 활용하여 국가의 월별 매출을 조회해보고 싶다고 하자. 

 

** 아래 데이터는 샘플** 실제로 빅쿼리상에서는 해당과 같이 날렸지만 STRUCT 에러로 인해서 조회가 안됨.

** CHATGPT가 샘플을 줬으나.. 에러가 나네요 ㅠ_ㅠ**

 

WITH sales AS (
  SELECT '한국' AS 국가, '1월' AS 월, 10000 AS 매출 UNION ALL
  SELECT '한국', '2월', 12000 UNION ALL
  SELECT '미국', '1월', 15000 UNION ALL
  SELECT '미국', '2월', 13000
)

SELECT 
  국가,
  월,
  SUM(매출) AS 총매출
FROM 
  sales
GROUP BY 
  1,2

 

GROUP BY 를 활용한다면 아래와 같은 결과를 얻을 수 있을 것 입니다.

 

그렇지면, 여기서 한가지 더 세분화하게 GROUPING을 할 순 없을가요? 예를 들어서 한 쿼리 안에 전체 국가의 total_sales 나 month 별로 total_sales를 보고 싶을 수 있을 것 같습니다. 이게 바로 세분화를 한다는 내용이고 여기서 Grouping sets 와 Group by의 차이점이 발생합니다.

 

WITH sales AS (
  SELECT '한국' AS 국가, '1월' AS 월, 10000 AS 매출 UNION ALL
  SELECT '한국', '2월', 12000 UNION ALL
  SELECT '미국', '1월', 15000 UNION ALL
  SELECT '미국', '2월', 13000
)

SELECT 
  국가,
  월,
  SUM(매출) AS 총매출
FROM 
  sales
GROUP BY 
  GROUPING SETS (
    (국가, 월),  -- 국가별, 월별 그룹화
    (국가),      -- 국가별 그룹화
    (월),        -- 월별 그룹화
    ()           -- 전체 그룹 (총합)
  )
ORDER BY 
  국가, 월;

 

하지만, 이와 같이 Grouping Sets를 사용한다면 아래와 같이 그룹을 세분화하여 total_sales을 조회할 수 있습니다. 

2. 응용

위와 같은 간단한 예시에서 Grouping sets 와 Group by 는 큰 차이를 보이지 않을 수 있습니다. 하지만, 만약 보다 더 세분화 해서 group 에 따른 결과를 조회하고 싶다면요? 특히, 대용량의 데이터셋. 즉 LOG 데이터나 무한대로 GROUP 화를 해야 하는 상황을 만들어 보겠습니다.

WITH user_sales AS (
  SELECT 'user_1' AS member_id, 'region_1' AS region_id, 
         DATETIME('2024-12-01 08:00:00') AS start_at_kst, 
         DATETIME('2024-12-01 10:00:00') AS end_at_kst, 
         1500 AS sales
  UNION ALL
  SELECT 'user_1', 'region_1', 
         DATETIME('2024-12-02 09:00:00'), 
         DATETIME('2024-12-02 11:00:00'), 
         1200
  UNION ALL
  SELECT 'user_2', 'region_2', 
         DATETIME('2024-12-01 14:00:00'), 
         DATETIME('2024-12-01 16:00:00'), 
         2000
  UNION ALL
  SELECT 'user_2', 'region_2', 
         DATETIME('2024-12-02 15:00:00'), 
         DATETIME('2024-12-02 17:00:00'), 
         1800
)

 

*위 결과는 하나의 예시일뿐입니다.

 

이와 같이 member(유저) , region(지역), start_at_kst(시작시간), end_at_kst(종료시간) 그 시간 내 sales(매출)을 세분화하게 group 화 해서 total_sales를 데이터가 있습니다.

 

1. 간단하게는 GROUP BY 를 member_id 별로, region_id 별로 total_sales 를 구하겠습니다.

2. 추가적으로, 각 유저의 (start_at_kst ~ end_at_kst) 내 발생한 sales는 얼마인가를 구하겠습니다. 만약 해당 데이터가 LOG 데이터와 같이 무한대라면 특정 시간대는 계속 그룹화하여 발생할 것 입니다.

3. 뿐만 아니라, GROUPING 이라는 함수를 사용하여 예외상황(start_at_kst 와 end_at_kst)가 LOG 상에서 빠진 데이터나 그룹을 한가지 더 조건으로 주어 구별해보겠습니다. 

 

SELECT 
  member_id,
  region_id,
  CONCAT(CAST(start_at_kst AS STRING), '~', CAST(end_at_kst AS STRING)) AS core_time,
  SUM(sales) AS total_sales,
  CASE 
    WHEN GROUPING(region_id) = 1 AND GROUPING(CONCAT(start_at_kst, '~', end_at_kst)) = 0 THEN 'only_core_time_info'
    WHEN GROUPING(CONCAT(start_at_kst, '~', end_at_kst)) = 1 AND GROUPING(region_id) = 0 THEN 'only_region_info'
    ELSE 'region_and_core_time_info'
  END AS label
FROM user_sales
GROUP BY 
  GROUPING SETS (
    (member_id, region_id), 
    (member_id, CONCAT(start_at_kst, '~', end_at_kst)), 
    (member_id, region_id, CONCAT(start_at_kst, '~', end_at_kst))
  )
ORDER BY member_id, label;

 

위와 같이 세분화하여 그룹화를 지어 결과를 다양하게 조회해볼 수 있습니다.

기존의 GROUP BY 보다 자유롭고, 세분화하여 결과를 조회할 수 있는 방법입니다. 세부 설명은 아래와 같습니다.

 

  1. GROUPING 함수:
    • region_id와 core_time(start_at_kst ~ end_at_kst)의 그룹화 여부를 확인하여 레이블을 지정.
  2. GROUPING SETS:
    • 다차원 집계를 수행하여 region_id 또는 core_time 단위별, 혹은 두 항목을 모두 포함한 결과를 생성.
  3. 라벨링(label):
    • 어떤 집계 수준에서 데이터를 계산했는지를 명확히 구분.

 

 

3. 결론

GROUPING SETS의 장점은 무엇보다 GROUP 을 다양하게 주어 쿼리의 간결성과 세분화된 데이터 분석을 하는데 용이하다는 점 입니다. 개인적으론 LOG 데이터에서 정말 다양한 GROUP 화를 지어야 할 때, CROSS JOIN 으로 UNNEST 한 날짜 데이터를 조인하여 세분화 하게 데이터 분석을 사용할 때 용이하다고 생각됩니다.

 기억해뒀다가, 다차원 데이터 분석을 할 때 사용하기에 좋은 함수라 정리하고 갑니다! 

반응형