使用BigQuery计算当日7天活跃用户? [英] Calculating a current day 7 day active user with BigQuery?

问题描述

如果我没有记错的话，计算当前日活跃用户应该很简单.只需将今天和x天退回(7天有效将是6天退回)，然后计算不同的ID.我有一个为期2天的活跃用户的以下查询:

To calculate the current day active user should be simple if I'm not mistaken. Simply take today and x days back (7-day-active would be 6 back) and then count the distinct IDs. I have the following query for a 2 day active user:

WITH allTables AS (
  SELECT 
    CONCAT(user_dim.app_info.app_id, ':', user_dim.app_info.app_platform) AS app,
    event.date,
    user_dim.app_info.app_instance_id as users
  FROM `dataset.app_events_intraday_20170407`
  CROSS JOIN
    UNNEST(event_dim) AS event

  UNION ALL
  SELECT 
    CONCAT(user_dim.app_info.app_id, ':', user_dim.app_info.app_platform) AS app,
    event.date,
    user_dim.app_info.app_instance_id as users
  FROM `dataset.app_events_20170406`
  CROSS JOIN
    UNNEST(event_dim) AS event
) SELECT COUNT(DISTINCT(users)) AS unique,
   COUNT(users) as total
FROM allTables

这是为期2天的活动，但是对于7天或30天，我只需要合并所有这些表即可.这是正确的还是需要修改?

This is for a 2-day active but for a 7day or 30day I would just union all those tables on. Is this correct or would this need modification?

推荐答案

您应该尝试使用 使用通配符表查询多个表

Instead of using UNION ALL you should try to use Querying Multiple Tables Using a Wildcard Table

尝试以下类似的方法

#standardSQL
WITH allTables AS (
  SELECT 
    CONCAT(user_dim.app_info.app_instance_id, ':', user_dim.app_info.app_platform) AS app,
    event.date,
    user_dim.app_info.app_instance_id AS users
  FROM `dataset.app_events_intraday_*`, UNNEST(event_dim) AS event
  WHERE _TABLE_SUFFIX BETWEEN '20170401' AND '20170407' 
  UNION ALL
  SELECT 
    CONCAT(user_dim.app_info.app_instance_id, ':', user_dim.app_info.app_platform) AS app,
    event.date,
    user_dim.app_info.app_instance_id AS users
  FROM `dataset.app_events_*`, UNNEST(event_dim) AS event
  WHERE _TABLE_SUFFIX BETWEEN '20170401' AND '20170407' 
) 
SELECT 
  COUNT(DISTINCT(users)) AS unique,
  COUNT(users) AS total
FROM allTables

您可以在下面的 WHERE 子句中使用它，使其更通用

You can use below for WHERE clause to make it more generic

WHERE _TABLE_SUFFIX 
   BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 6 DAY)) 
   AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())

还请注意:我将 user_dim.app_info.app_id 中的 app_id 更改为 app_instance_id ，因为我认为这是您的错字-但我可能是错的

Also please note: I changed app_id in user_dim.app_info.app_id to app_instance_id as I thought it was typo on your side - but I can be wrong

这篇关于使用BigQuery计算当日7天活跃用户?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！