Скользящее среднее за последние 30 дней

Я хочу узнать количество уникальных пользователей, активных за последние 30 дней. Я хочу рассчитать это на сегодня, но также и на дни в прошлом. Набор данных содержит идентификаторы пользователей, даты и события, инициированные пользователем, сохраненные в BigQuery. Пользователь становится активным, открывая мобильное приложение, вызывающее событие session_start. Пример невложенного набора данных.

| resettable_device_id |     date    |    event      |
------------------------------------------------------
|         xx           |  2017-06-09 | session_start |
|         yy           |  2017-06-09 | session_start |
|         xx           |  2017-06-11 | session_start |
|         zz           |  2017-06-11 | session_start |

Я нашел решение, соответствующее моей проблеме: BigQuery : как сгруппировать и подсчитать строки в скользящем временном окне?

Мой сценарий BigQuery на данный момент:

#standardSQL
WITH daily_aggregation AS (
  SELECT 
    PARSE_DATE("%Y%m%d", event_dim.date) AS day,
    COUNT(DISTINCT user_dim.device_info.resettable_device_id) AS unique_resettable_device_ids
  FROM `ANDROID.app_events_*`,
    UNNEST(event_dim) AS event_dim
  WHERE event_dim.name = "session_start"
  GROUP BY day
)
SELECT 
  day, 
  unique_resettable_device_ids, 
  SUM(unique_resettable_device_ids) 
  OVER(ORDER BY UNIX_SECONDS(TIMESTAMP(day)) DESC ROWS BETWEEN 2592000 PRECEDING AND CURRENT ROW) AS unique_ids_rolling_30_days
FROM daily_aggregation
ORDER BY day

Результатом этого сценария является следующая таблица:

|      day   | unique_resettable_device_ids | unique_ids_rolling_30_days |
------------------------------------------------------------------------
| 2018-06-05 |            1807              |            2614            |
| 2018-06-06 |             711              |             807            |
| 2018-06-07 |              96              |              96            |

Проблема в том, что столбец unique_ids_rolling_30_days - это просто совокупная сумма столбца unique_resettable_device_ids. Как я могу исправить функцию скользящего окна в моем скрипте?


sql google-bigquery bigquery-standard-sql
person Philip van Kuiken    schedule 07.06.2018    source источник
comment
Вы можете добавить свое решение в качестве ответа и принять его самостоятельно. Это будет яснее для людей, которые зададут этот вопрос.   -  person VictorGGl    schedule 14.06.2018
comment
Удалил решение из исходного вопроса и добавил его в качестве ответа.   -  person Philip van Kuiken    schedule 14.06.2018

Ответы (2)


arrow_upward
2
arrow_downward

«Проблема в том, что столбец unique_ids_rolling_30_days - это просто совокупная сумма столбца unique_resettable_device_ids».

Конечно, потому что это именно то, что код

SUM(unique_resettable_device_ids) OVER(ORDER BY UNIX_SECONDS(TIMESTAMP(day)) DESC ROWS BETWEEN 2592000 PRECEDING AND CURRENT ROW) AS unique_ids_rolling_30_days

просит.

Ознакомьтесь с https://stackoverflow.com/a/49866033/132438, где задается вопрос о конкретном подсчете уникальных посетителей при просмотре. window: Оказывается, это очень медленная операция, учитывая, сколько памяти ей требуется.

Решение этой проблемы, если вам нужен непрерывный счетчик уникальных посетителей: просмотрите приблизительные результаты.

Из связанного ответа:

#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
person Felipe Hoffa    schedule 07.06.2018
comment
Спасибо за ответ. Я преобразовал ваш код в свой вариант использования, но результат сценария не соответствовал сценарию проверки. Мой сценарий проверки - это просто количество активных пользователей в окне с сегодняшнего дня до 30 дней назад. Кто-то помог мне найти другое решение, которое вы можете найти в моем обновленном посте. - person Philip van Kuiken; 08.06.2018
comment
Извините, я добавил решение. Теперь он работает еженедельно для подсчета количества активных пользователей. - person Philip van Kuiken; 08.06.2018

arrow_upward
1
arrow_downward

Рабочее решение для еженедельного подсчета количества активных пользователей за последние 30 дней.

#standardSQL
WITH days AS (
  SELECT day 
  FROM UNNEST(GENERATE_DATE_ARRAY('2018-01-01', CURRENT_DATE(), INTERVAL 1 WEEK)) AS day
), periods AS (
SELECT 
  DATE_SUB(days.day, INTERVAL 30 DAY) AS StartDate,
  days.day AS EndDate FROM days
)
SELECT
  periods.EndDate AS Day,
  COUNT(DISTINCT user_dim.device_info.resettable_device_id) as resettable_device_ids
FROM `ANDROID.app_events_*`,
  UNNEST(event_dim) AS event_dim
CROSS JOIN periods
WHERE
  PARSE_DATE("%Y%m%d", event_dim.date) BETWEEN periods.StartDate AND periods.EndDate
  AND event_dim.name = "session_start"
GROUP BY Day
ORDER BY Day DESC
person Philip van Kuiken    schedule 14.06.2018