[SQL] BigQuery에서 구십일에게 활성 사용자 롤링,의 preformance을 개선 (DAU / MAU / WAU)

BigQuery에서 구십일에게 활성 사용자 롤링,의 preformance을 개선 (DAU / MAU / WAU)

나는 90/30/7 일 롤백, 특정 날짜에 독특한 이벤트의 수를 얻기 위해 노력하고있어. 나는 쿼리 울부 짖는 소리와 행 제한된 수의에서이 작업을 가지고 있지만, 대규모 데이터 세트에 대한 I는 대규모가되는 집계 문자열에서 메모리 오류를 얻을.

나는 같은 결과를 달성하는 더 효과적인 방법을 찾고 있어요.

표는 다음과 같은 :

+---+------------+-------------+
|   |     date   |     userid  |
+---+------------+-------------+
| 1 | 2013-05-14 | xxxxx       |
| 2 | 2017-03-14 | xxxxx       |
| 3 | 2018-01-24 | xxxxx       |
| 4 | 2013-03-21 | xxxxx       |
| 5 | 2014-03-19 | xxxxx       |
| 6 | 2015-09-03 | xxxxx       |
| 7 | 2014-02-06 | xxxxx       |
| 8 | 2014-10-30 | xxxxx       |
| ..| ...        | ...         |
+---+------------+-------------+

원하는 결과의 형식 :

+---+------------+---------------------------------------------+
|   |     date   | active_users_7_days | active_users_90_days  |
+---+------------+---------------------------------------------+
| 1 | 2013-05-14 | 1240                | 34339                 |
| 2 | 2017-03-14 | 4334                | 54343                 |
| 3 | 2018-01-24 | .....               | .....                 |
| 4 | 2013-03-21 | .....               | .....                 |
| 5 | 2014-03-19 | .....               | .....                 |
| 6 | 2015-09-03 | .....               | .....                 |
| 7 | 2014-02-06 | .....               | .....                 |
| 8 | 2014-10-30 | .....               | .....                 |
| ..| ...        | .....               | .....                 |
+---+------------+---------------------------------------------+

내 쿼리는 다음과 같습니다 :

#standardSQL
    WITH
      T1 AS(
      SELECT
        date,
        STRING_AGG(DISTINCT userid) AS IDs
      FROM
        `consumer.events`
      GROUP BY
        date ),
      T2 AS(
      SELECT
        date,
        STRING_AGG(IDs) OVER(ORDER BY UNIX_DATE(date) RANGE BETWEEN 90 PRECEDING
          AND CURRENT ROW) AS IDs
      FROM
        T1 )
    SELECT
      date,
      (
      SELECT
        COUNT(DISTINCT (userid))
      FROM
        UNNEST(SPLIT(IDs)) AS userid) AS NinetyDays
    FROM
      T2

해결법

1. ==============================

   1.고유 한 사용자를 계산하면 롤 창을 통해 결과를 원하는 더 많은 경우에 많은 자원을 필요로한다. 확장 가능한 솔루션의 경우, ++ HLL 같은 대략적인 알고리즘에보기 :

   고유 한 사용자를 계산하면 롤 창을 통해 결과를 원하는 더 많은 경우에 많은 자원을 필요로한다. 확장 가능한 솔루션의 경우, ++ HLL 같은 대략적인 알고리즘에보기 :

   정확한 카운트를 들어,이 작동 (그러나 윈도우가 커질수록 속도가 느린 도착) 것입니다 :

   #standardSQL
   SELECT DATE_SUB(date, INTERVAL i DAY) date_grp
    , COUNT(DISTINCT owner_user_id) unique_90_day_users
    , COUNT(DISTINCT IF(i<31,owner_user_id,null)) unique_30_day_users
    , COUNT(DISTINCT IF(i<8,owner_user_id,null)) unique_7_day_users
   FROM (
     SELECT DATE(creation_date) date, owner_user_id
     FROM `bigquery-public-data.stackoverflow.posts_questions` 
     WHERE EXTRACT(YEAR FROM creation_date)=2017
     GROUP BY 1, 2
   ), UNNEST(GENERATE_ARRAY(1, 90)) i
   GROUP BY 1
   ORDER BY date_grp
   

   근사해 빠른 결과를 생성 방법 (14S 366s VS, 그러나 그 결과는 대략)

   #standardSQL
   SELECT DATE_SUB(date, INTERVAL i DAY) date_grp
    , HLL_COUNT.MERGE(sketch) unique_90_day_users
    , HLL_COUNT.MERGE(DISTINCT IF(i<31,sketch,null)) unique_30_day_users
    , HLL_COUNT.MERGE(DISTINCT IF(i<8,sketch,null)) unique_7_day_users
   FROM (
     SELECT DATE(creation_date) date, HLL_COUNT.INIT(owner_user_id) sketch
     FROM `bigquery-public-data.stackoverflow.posts_questions` 
     WHERE EXTRACT(YEAR FROM creation_date)=2017
     GROUP BY 1
   ), UNNEST(GENERATE_ARRAY(1, 90)) i
   GROUP BY 1
   ORDER BY date_grp
   

   정확한 결과를 제공 업데이트 쿼리 - 90 일 이내 (어떤 날짜가 누락되지 않을 때 작동)로 행을 제거 :

   #standardSQL
   SELECT DATE_SUB(date, INTERVAL i DAY) date_grp
    , HLL_COUNT.MERGE(sketch) unique_90_day_users
    , HLL_COUNT.MERGE(DISTINCT IF(i<31,sketch,null)) unique_30_day_users
    , HLL_COUNT.MERGE(DISTINCT IF(i<8,sketch,null)) unique_7_day_users
    , COUNT(*) window_days
   FROM (
     SELECT DATE(creation_date) date, HLL_COUNT.INIT(owner_user_id) sketch
     FROM `bigquery-public-data.stackoverflow.posts_questions` 
     WHERE EXTRACT(YEAR FROM creation_date)=2017
     GROUP BY 1
   ), UNNEST(GENERATE_ARRAY(1, 90)) i
   GROUP BY 1
   HAVING window_days=90
   ORDER BY date_grp
   

2. ==============================

   2.당신은 날짜를 집계하고 합계를 할 수 있습니다. 집계는 무엇입니까? 가장 최근의 날짜를 가지고 :

   당신은 날짜를 집계하고 합계를 할 수 있습니다. 집계는 무엇입니까? 가장 최근의 날짜를 가지고 :

   select count(*) as num_users,
          sum(case when date > datediff(current_date, interval -30 day) then 1 else 0 end) as num_users_30days,
          sum(case when date > datediff(current_date, interval -60 day) then 1 else 0 end) as num_users_60days,
          sum(case when date > datediff(current_date, interval -90 day) then 1 else 0 end) as num_users_90days
   from (select user_id, max(date) as max(date)
         from `consumer.events` e
         group by user_id
        ) e;
   

   사용자에 대한 가장 최근의 날짜가 기간의 경우, 사용자는 계산해야한다.

   당신은 하위 쿼리에 WHERE 절을 사용하여이 "-의 등"특정 날짜를 얻을 수 있습니다.

from https://stackoverflow.com/questions/49852357/rolling-90-days-active-users-in-bigquery-improving-preformance-dau-mau-wau by cc-by-sa and MIT license