如果我没记错的话,计算当前日活跃用户应该很简单。只需将今天和 x 天带回(7 天活跃将是 6 天(,然后计算不同的 ID。我对 2 天活跃用户有以下查询:
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 天,我只会将所有这些表合并在一起。这是正确的还是需要修改?
与其使用
UNION ALL,不如尝试使用 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
您可以在下面使用 for WHERE
子句以使其更通用
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
,因为我认为这是您的错字 - 但我可能是错的