从大查询滚动 30 天的数据

Question

>假设我有这个查询：

SELECT ga_channelGrouping, ga_sourceMedium,ga_campaign, SUM(ga_sessions) as sessions,
SUM(ga_sessionDuration)/SUM(ga_sessions) as avg_sessionDuration, 
SUM(ga_users)as Users, SUM(ga_newUsers)as New_Users, SUM(ga_bounces)/SUM(ga_sessions) 
AS ga_bounceRate, SUM(ga_pageviews)/SUM(ga_sessions)as pageViews_per_sessions, 
SUM( ga_transactions)/SUM(ga_sessions) AS ga_conversionRate 

FROM db.table 
group by ga_channelGrouping, ga_sourceMedium,ga_campaign

如何从大查询中找到滚动的 30 天数据。我的DATE列值的格式如下：2018-06-19 11:00:00 UTC

Answer 1

您可以使用DATE_ADD或DATE_SUB函数来移动日期值和TIMESTAMP_ADD，TIMESTAMP_SUB来移动时间戳值。

所以你可以试试：

SELECT ga_channelGrouping, ga_sourceMedium,ga_campaign, SUM(ga_sessions) as sessions,
SUM(ga_sessionDuration)/SUM(ga_sessions) as avg_sessionDuration, 
SUM(ga_users)as Users, SUM(ga_newUsers)as New_Users, SUM(ga_bounces)/SUM(ga_sessions) 
AS ga_bounceRate, SUM(ga_pageviews)/SUM(ga_sessions)as pageViews_per_sessions, 
SUM( ga_transactions)/SUM(ga_sessions) AS ga_conversionRate 

FROM db.table 
WHERE your_date_column >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24*30 HOUR)
group by ga_channelGrouping, ga_sourceMedium,ga_campaign

TIMESTAMP_SUB不以DAY为间隔，因此我们在这里做了24*30个小时来回溯 30 天。

---

编辑：如果要回滚30天，而不管一天中的时间如何，您可以执行以下操作：

WHERE your_date_column >= TIMESTAMP_TRUNC(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24*30 HOUR), DAY)

或

WHERE CAST(your_date_column AS DATE) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY))

Answer 2

如何从大查询中找到滚动的 30 天数据。我的 DATE 列值的格式如下：2018-06-19 11：00：00 UTC

首先，我想指出aggregating last 30 days与rolling 30 days有很大不同- 所以下面的答案实际上侧重于rolling 30 days与。just last 30 days

下面是 BigQuery 标准 SQL，假设您的日期列名为your_date_column且

数据类型为 TIMESTAMP

#standardSQL
SELECT 
your_date_column, -- data type of TIMESTAMP with value like 2018-06-19 11:00:00 UTC
ga_channelGrouping, 
ga_sourceMedium,
ga_campaign, 
SUM(ga_sessions) OVER(win) AS sessions,
(SUM(ga_sessionDuration) OVER(win))/(SUM(ga_sessions) OVER(win)) AS avg_sessionDuration, 
SUM(ga_users) OVER(win) AS Users, 
SUM(ga_newUsers) OVER(win) AS New_Users, 
(SUM(ga_bounces) OVER(win))/(SUM(ga_sessions) OVER(win)) AS ga_bounceRate, 
(SUM(ga_pageviews) OVER(win))/(SUM(ga_sessions) OVER(win)) AS pageViews_per_sessions, 
(SUM(ga_transactions) OVER(win))/(SUM(ga_sessions) OVER(win)) AS ga_conversionRate 
FROM `project.dataset.table`
WINDOW win AS (
PARTITION BY ga_channelGrouping, ga_sourceMedium, ga_campaign
ORDER BY UNIX_DATE(DATE(your_date_column)) 
RANGE BETWEEN 29 PRECEDING AND CURRENT ROW
)    

为了让您了解它是如何工作的 - 尝试使用下面的虚拟示例(为简单起见，它会滚动3天(

#standardSQL
WITH `project.dataset.table` AS (
SELECT 1 value, TIMESTAMP '2018-06-19 11:00:00 UTC' your_date_column UNION ALL
SELECT 2, '2018-06-20 11:00:00 UTC' UNION ALL
SELECT 3, '2018-06-21 11:00:00 UTC' UNION ALL
SELECT 4, '2018-06-22 11:00:00 UTC' UNION ALL
SELECT 5, '2018-06-23 11:00:00 UTC' UNION ALL
SELECT 6, '2018-06-24 11:00:00 UTC' UNION ALL
SELECT 7, '2018-06-25 11:00:00 UTC' UNION ALL
SELECT 8, '2018-06-26 11:00:00 UTC' UNION ALL
SELECT 9, '2018-06-27 11:00:00 UTC' UNION ALL
SELECT 10, '2018-06-28 11:00:00 UTC' 
)
SELECT 
your_date_column, 
value, 
SUM(value) OVER(win) rolling_value
FROM `project.dataset.table`
WINDOW win AS (ORDER BY UNIX_DATE(DATE(your_date_column)) RANGE BETWEEN 2 PRECEDING AND CURRENT ROW)
ORDER BY your_date_column   

其中结果是

Row your_date_column        value   rolling_value    
1   2018-06-19 11:00:00 UTC 1       1    
2   2018-06-20 11:00:00 UTC 2       3    
3   2018-06-21 11:00:00 UTC 3       6    
4   2018-06-22 11:00:00 UTC 4       9    
5   2018-06-23 11:00:00 UTC 5       12   
6   2018-06-24 11:00:00 UTC 6       15   
7   2018-06-25 11:00:00 UTC 7       18   
8   2018-06-26 11:00:00 UTC 8       21   
9   2018-06-27 11:00:00 UTC 9       24   
10  2018-06-28 11:00:00 UTC 10      27