我有一个表,它记录了每天每个唯一用户的一行,其中包含该用户当天的一些汇总统计信息,并且我需要生成一个报告,告诉我每天的no。包括当天在内的过去30天内的独立用户。
。
- 8月31日,它将统计从8月2日到8月31日的唯一用户
- 8月30日,它将统计从8月1日到8月30日的唯一用户
我看过一些相关的问题,但它们并不是我所需要的——如果一个用户在过去30天内多次登录,他应该只被计算一次,所以我不能只计算过去30天的DAU。
Bigquery SQL for滑动窗口聚合
BigQuery SQL for 28天滑动窗口聚合(不写28行SQL)
目前为止,我的想法是:
- 编写一个简单的脚本,将在每个相关的日子执行一个单独的BigQuery
- 编写一个BigQuery UDF,它将对从另一个查询 中选择的每一天执行基本相同的查询
,但是我没有找到任何关于如何在UDF内执行另一个BigQuery查询的例子,或者如果它是可能的。
我需要制作一份报告,告诉我每天的收入。的包括当天在内的30天内的唯一用户。
下面应该这样做
SELECT
calendar_day,
EXACT_COUNT_DISTINCT(userID) AS unique_users
FROM (
SELECT calendar_day, userID
FROM YourTable
CROSS JOIN (
SELECT DATE(DATE_ADD('2016-08-08', pos - 1, "DAY")) AS calendar_day
FROM (
SELECT ROW_NUMBER() OVER() AS pos, *
FROM (FLATTEN((
SELECT SPLIT(RPAD('', 1 + DATEDIFF('2016-09-08', '2016-08-08'), '.'),'') AS h
FROM (SELECT NULL)),h
)))
) AS calendar
WHERE DATEDIFF(calendar_day, dt) BETWEEN 0 AND 29
)
GROUP BY calendar_day
ORDER BY calendar_day DESC
它假设YourTable有userID和dt字段(如下所示)
dt userID
2016-09-08 1
2016-09-08 2
...
你可以控制:
-分别更改2016-08-08
和2016-09-08
的报告日期范围
-改变BETWEEN 0 AND 29
中29
的聚合大小