移动N天活动用户（BigQuery） [英] Moving N-day Active Users (BigQuery)

问题描述

我有一个由2列组成的事件表：

I have a table "events" consisting of 2 columns:

userId | eventDate
-------+-------------------
s234124| 2015-01-01
a2s3166| 2015-01-02
c216782| 2015-01-03
z312235| 2015-01-04

userId是用户ID。 eventDate表示该用户发生事件的日期。

userId is the user id. eventDate represents the date upon which an event happened for that user.

我想每天计算30个（或7个）活动唯一用户的数量，或60等），在该日期结束的一天。活动唯一用户定义为在给定窗口中至少有一个事件的userId。

I want to calculate, on a daily basis, the number of active unique users for the 30 (or 7, or 60, etc.) day period ending on that date. An active unique user is defined as a userId that has at least one event during a given window.

我读过这篇文章描述了一个类似的问题，但是在适应我的使用案例时遇到问题。

I read this article, which describes a similar problem but am having trouble adapting it to my use case.

推荐答案

假设表中有两个 userid 和日期 - 数据集。 your_table $ b

Asuming there are two fileds userid and date in your table - dataset.your_table

SELECT 
  date,
  SUM(CASE WHEN period = 7  THEN users END) as days_07,
  SUM(CASE WHEN period = 14 THEN users END) as days_14,
  SUM(CASE WHEN period = 30 THEN users END) as days_30
FROM (
  SELECT
    dates.date as date,  
    periods.period as period,
    EXACT_COUNT_DISTINCT(activity.userid) as users
  FROM dataset.your_table as activity
  CROSS JOIN (SELECT date FROM dataset.your_table GROUP BY date) as dates
  CROSS JOIN (SELECT period FROM (SELECT 7 as period), 
                (SELECT 14 as period), (SELECT 30 as period)) as periods
  WHERE dates.date >= activity.date 
  AND INTEGER(FLOOR(DATEDIFF(dates.date, activity.date)/periods.period)) = 0
  GROUP BY 1,2
)
GROUP BY date
ORDER BY date DESC

结果如下所示

date           days_07     days_14     days_30
8/29/2015    2,468,649   3,597,684   7,180,175 
8/28/2015    2,472,342   3,592,680   6,969,581 
8/27/2015    2,486,979   3,595,822   6,745,625 
8/26/2015    2,507,572   3,576,816   6,494,710 
8/25/2015    2,508,036   3,553,386   6,264,950 
8/24/2015    2,511,946   3,521,184   6,024,151 
8/23/2015    2,488,485   3,482,163   5,774,763 
8/22/2015    2,474,526   3,450,719   5,547,318 
8/21/2015    2,463,568   3,422,003   5,327,760 

这篇关于移动N天活动用户（BigQuery）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！