在BigQuery上运行过去30天的总和

Question

我正试图在BigQuery中获得谷歌商品商店公共数据集的以下查询：

1. 日期
2. 不同用户数
3. 过去30天内不同用户数的运行总和

例如(为了简单起见，我在示例中使用了3天(：

date        distinct_users     distinct_users_3days
15/07/2018          8                    15
14/07/2018          2                    12
13/07/2018          5                    20
12/07/2018          5                    15
11/07/2018         10                    10
...

这是我当前的SQL代码，它获得前两列，但我不知道如何获得运行的总和：

SELECT
date,
COUNT(DISTINCT(fullVisitorId)) as daily_active_user
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_2017*`
WHERE
_table_suffix BETWEEN "0101"
AND "0715"
GROUP BY
date

感谢您的帮助！：(

Answer 1

我设法找到了问题的答案，所以我想与未来可能遇到这个问题的其他人分享。

SQL代码为：

SELECT
date,
COUNT(DISTINCT(fullVisitorId)) as daily_active_user,
SUM(count(Distinct(fullVisitorId))) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS monthly_active_user
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_2017*`,
unnest(hits) as h
WHERE
_table_suffix BETWEEN "0101" AND "0715"
GROUP BY
date

这提供了一个列，在30天的窗口中对不同的用户进行汇总。

Answer 2

请尝试以下查询3天(SQL server 2014(-：

SELECT date,COUNT(DISTINCT(fullVisitorId)) as daily_active_user,sum(COUNT(DISTINCT(fullVisitorId))) over (PARTITION BY null ORDER BY date desc ROWS 
BETWEEN CURRENT ROW AND 2 FOLLOWING) AS distinct_users_3days FROM YOUR_TABLE_NAME WHERE _table_suffix BETWEEN '0101' AND '715' GROUP BY date

30天-：

SELECT 
date,COUNT(DISTINCT(fullVisitorId)) as daily_active_user,
sum(COUNT(DISTINCT(fullVisitorId))) over (PARTITION BY null ORDER BY date desc ROWS 
BETWEEN CURRENT ROW AND 29 FOLLOWING) AS distinct_users_3days 
FROM YOUR_TABLE_NAME 
WHERE _table_suffix 
BETWEEN '0101' AND '715' 
GROUP BY date