时间戳

首页 > 解决方案 > 7 天用户计数：Big-Query 自加入以获取日期范围和计数？

sql - 7 天用户计数：Big-Query 自加入以获取日期范围和计数？

问题描述

我的 Google Firebase 事件数据已集成到 BigQuery，我试图从这里获取 Firebase 自动为我提供的信息之一：1 天、7 天、28 天的用户数。

1 天计数非常简单

SELECT
  "1-day" as period,
  events.event_date,
  count(distinct events.user_pseudo_id) as uid
FROM
  `your_path.events_*` as events
WHERE events.event_name = "session_start"
group by events.event_date

有一个整洁的结果，比如

period   event_date  uid
1-day    20190609    5
1-day    20190610    7
1-day    20190611    5
1-day    20190612    7
1-day    20190613    37
1-day    20190614    73
1-day    20190615    52
1-day    20190616    36

但是对我来说，当我尝试每天计算前 7 天有多少唯一用户时，它会变得复杂 从上面的查询中，我知道我的第 20190616 天的目标值将是 142，通过过滤 7 天并删除组按条件。

我尝试的解决方案是直接自加入（以及没有改变结果的变体）

SELECT
  "7-day" as period,
  events.event_date,
  count(distinct user_events.user_pseudo_id) as uid
FROM
  `your_path.events_*` as events,
  `your_path.events_*` as user_events
WHERE user_events.event_name = "session_start"
  and PARSE_DATE("%Y%m%d", events.event_date) between DATE_SUB(PARSE_DATE("%Y%m%d", user_events.event_date), INTERVAL 7 DAY) and PARSE_DATE("%Y%m%d", user_events.event_date) #one day in the first table should correspond to 7 days worth of events in the second
  and events.event_date = "20190616" #fixed date to check
group by events.event_date

现在，我知道我几乎没有设置任何连接条件，但如果有的话，我希望产生交叉连接和巨大的结果。相反，这种方式的计数是 70，这比预期的要低很多。此外，我可以设置 INTERVAL 2 DAY 并且结果不会改变。

很明显我这里做的很不对，但是我也觉得我做的方式很简陋，一定有更聪明的方法来完成。

我已经检查了使用 BigQuery 计算当前 7 天的活跃用户？但是这里的显式交叉连接是 event_dim 我不确定哪个定义

检查在 BigQuery 中滚动 90 天活跃用户提供的解决方案，按照评论的建议提高性能（DAU/MAU/WAU）。该解决方案起初看起来很合理，但最近出现了一些问题。这是我适应我的情况的使用 COUNT(DISTINCT) 的查询

SELECT DATE_SUB(event_date, INTERVAL i DAY) date_grp
 , COUNT(DISTINCT user_pseudo_id) unique_90_day_users
 , COUNT(DISTINCT IF(i<29,user_pseudo_id,null)) unique_28_day_users
 , COUNT(DISTINCT IF(i<8,user_pseudo_id,null)) unique_7_day_users
 , COUNT(DISTINCT IF(i<2,user_pseudo_id,null)) unique_1_day_users
FROM (
  SELECT PARSE_DATE("%Y%m%d",event_date) as event_date, user_pseudo_id
  FROM `your_path_here.events_*`
  WHERE EXTRACT(YEAR FROM PARSE_DATE("%Y%m%d",event_date))=2019
  GROUP BY 1, 2
), UNNEST(GENERATE_ARRAY(1, 90)) i
GROUP BY 1
ORDER BY date_grp

这是最近几天的结果（考虑数据从 5 月 23 日开始），您可以从中看出结果是错误的

row_num   date_grp     90-day  28-day  7-day   1-day
114       2019-06-16   273     273     273     210
115       2019-06-17   78      78      78      78

因此，在最后一天，90 天、28 天、7 天的计数仅考虑同一天，而不是之前的所有天。如果 6 月 16 日的 1 天更高，则 6 月 17 日的 90 天计数不可能为 78。

标签： sqlgoogle-bigquery

解决方案

这是对我同样问题的答案。我的方法很简陋，因为我对 BQ 快捷键和一些高级功能不是很熟悉，但结果仍然是正确的。我希望其他人能够与更好的查询集成。

#standardSQL
WITH dates AS (
  SELECT i as event_date
  FROM UNNEST(GENERATE_DATE_ARRAY('2019-05-24', CURRENT_DATE(), INTERVAL 1 DAY)) i
)
, ptd_dates as (
  SELECT DISTINCT "90-day" as day_category, FORMAT_DATE("%Y%m%d",event_date) AS event_date, FORMAT_DATE("%Y%m%d",DATE_SUB(event_date, INTERVAL i-1 DAY)) as ptd_date
  FROM dates,
    UNNEST(GENERATE_ARRAY(1, 90)) i
  UNION ALL
  SELECT distinct "28-day" as day_category, FORMAT_DATE("%Y%m%d",event_date) AS event_date, FORMAT_DATE("%Y%m%d",DATE_SUB(event_date, INTERVAL i-1 DAY)) as ptd_date
  FROM dates,
    UNNEST(GENERATE_ARRAY(1, 29)) i
  UNION ALL
  SELECT distinct "7-day" as day_category, FORMAT_DATE("%Y%m%d",event_date) AS event_date, FORMAT_DATE("%Y%m%d",DATE_SUB(event_date, INTERVAL i-1 DAY)) as ptd_date
  FROM dates,
    UNNEST(GENERATE_ARRAY(1, 7)) i
  UNION ALL
  SELECT distinct "1-day" as day_category, FORMAT_DATE("%Y%m%d",event_date) AS event_date, FORMAT_DATE("%Y%m%d",event_date) as ptd_date
  FROM dates
)


SELECT event_date,
  sum(IF(day_category="90-day",unique_ptd_users,null)) as count_90_day ,
  sum(IF(day_category="28-day",unique_ptd_users,null)) as count_28_day,
  sum(IF(day_category="7-day",unique_ptd_users,null)) as count_7_day,
  sum(IF(day_category="1-day",unique_ptd_users,null)) as count_1_day
from (
SELECT ptd_dates.day_category
  , ptd_dates.event_date
  , COUNT(DISTINCT user_pseudo_id) unique_ptd_users
FROM ptd_dates,
  `your_path_here.events_*` events,
  unnest(events.event_params) e_params
WHERE ptd_dates.ptd_date = events.event_date
GROUP BY ptd_dates.day_category
  , ptd_dates.event_date)
group by event_date
order by 1,2,3

根据 ECris 的建议，我首先定义了一个要使用的日历表：它包含 4 类 PTD（期间至今）。每个都是从基本元素生成的：这应该线性扩展，因为它不查询事件数据集，因此没有间隙。

然后使用事件进行连接，其中连接条件显示每个日期我如何计算该期间所有相关日期的不同用户。

结果是正确的。

推荐阅读