시간대별로 데이터를 고르게 샘플링 하는 방법

 

2340건의 데이터에서 시간 순위를 기준으로 골고루 100개의 데이터를 추출하려면, 데이터를 시간대별로 나눈 후 각 시간대에서 비례적으로 데이터를 샘플링해야 합니다. 이렇게 하면 데이터가 시간대별로 균형 있게 선택됩니다.

 

BigQuery에서는 ROW_NUMBER()와 NTILE() 윈도우 함수를 사용하여 데이터를 시간순으로 나눈 후, 각 그룹에서 데이터를 샘플링할 수 있습니다.

 

 

WITH time_partitioned_data AS (
  SELECT
    *,
    NTILE(100) OVER (ORDER BY transaction_time) AS time_group
  FROM
    `your_project.your_dataset.transactions`
),
sampled_data AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY time_group ORDER BY RAND()) AS random_order
  FROM
    time_partitioned_data
)
SELECT
  *
FROM
  sampled_data
WHERE
  random_order = 1
ORDER BY
  transaction_time;

 

쿼리 설명

 

1. time_partitioned_data CTE:

• NTILE(100) OVER (ORDER BY transaction_time)은 transaction_time을 기준으로 데이터셋을 100개의 그룹으로 나눕니다. NTILE은 데이터를 시간 순으로 동일한 크기의 그룹으로 나눕니다.

• 이 단계에서 각 행은 time_group이라는 그룹 번호를 부여받게 됩니다.

2. sampled_data CTE:

• 각 그룹(time_group) 내에서 RAND() 함수를 사용해 랜덤 순서를 부여합니다.

• ROW_NUMBER() 윈도우 함수를 사용하여 각 그룹 내에서 행 번호(random_order)를 지정합니다.

3. 최종 샘플링:

• 각 그룹(time_group)에서 random_order가 1인 행만 선택합니다. 이는 각 시간 그룹에서 하나의 행을 랜덤하게 선택하는 것입니다.

• 총 100개의 데이터를 샘플링하게 됩니다.

4. 정렬:

• 최종 결과는 시간순으로 정렬됩니다.

 

결과

 

이 쿼리는 시간대별로 골고루 분포된 100개의 데이터를 반환합니다. NTILE(100)을 사용하여 데이터를 100개의 동일한 크기의 시간 그룹으로 나눈 후, 각 그룹에서 하나의 샘플을 무작위로 선택하므로 시간이 고르게 반영된 샘플링을 할 수 있습니다.