如何自动更新最近12周的数据? [英] How to automatically update data from the 12 most recent weeks?

问题描述

我想创建两个动态每周 BigQuery Firebase报告，以反映以下12个星期的最新数据:

I would like to create two dynamic weekly BigQuery Firebase Reports, reflecting the most recent 12 Weeks of data for:

1. 每周发生的事件
2. 每周触发事件发生次数的DISTINCT用户

这些报告的灵感来自 Tableau报告我在网上看到了:

The inspiration for these reports came from a Tableau-report I saw online:

我正在尝试创建动态时间戳以提取 1.event.name 的计数和 2.distinct user_dim.app_info.app_instance_id 的值.

I am trying to create dynamic timestamps to pull the values of 1.count of event.name and 2.distinct user_dim.app_info.app_instance_id.

我的第一次迭代:

SELECT  event.name as event_name, 
COUNT(CASE WHEN _TABLE_SUFFIX >= '20170724' AND _TABLE_SUFFIX < '20170731' THEN event.name END) AS W1,  
COUNT(CASE WHEN _TABLE_SUFFIX >= '20170731' AND _TABLE_SUFFIX < '20170807' THEN event.name END) AS W2,  
COUNT(CASE WHEN _TABLE_SUFFIX >= '20170807' AND _TABLE_SUFFIX < '20170814' THEN event.name END) AS W3,  
COUNT(CASE WHEN _TABLE_SUFFIX >= '20170814' AND _TABLE_SUFFIX < '20170821' THEN event.name END) AS W4,  
COUNT(CASE WHEN _TABLE_SUFFIX >= '20170821' AND _TABLE_SUFFIX < '20170828' THEN event.name END) AS W5,  
COUNT(CASE WHEN _TABLE_SUFFIX >= '20170828' AND _TABLE_SUFFIX < '20170904' THEN event.name END) AS W6,  
COUNT(CASE WHEN _TABLE_SUFFIX >= '20170904' AND _TABLE_SUFFIX < '20170911' THEN event.name END) AS W7,  
COUNT(CASE WHEN _TABLE_SUFFIX >= '20170911' AND _TABLE_SUFFIX < '20170918' THEN event.name END) AS W8,  
COUNT(CASE WHEN _TABLE_SUFFIX >= '20170918' AND _TABLE_SUFFIX < '20170925' THEN event.name END) AS W9,  
COUNT(CASE WHEN _TABLE_SUFFIX >= '20170925' AND _TABLE_SUFFIX < '20171002' THEN event.name END) AS W10,  
COUNT(CASE WHEN _TABLE_SUFFIX >= '20171002' AND _TABLE_SUFFIX < '20171009' THEN event.name END) AS W11,  
COUNT(CASE WHEN _TABLE_SUFFIX >= '20171009' AND _TABLE_SUFFIX < '20171016' THEN event.name END) AS W12  
FROM `<<project-id>>.app_events_*`, UNNEST(event_dim) AS event
WHERE  _TABLE_SUFFIX >= '20170724' AND _TABLE_SUFFIX < '20171016'
GROUP BY event_name
ORDER BY event_name DESC;

我还在下面玩sudo代码:

I also played around with sudo code below:

# Enable StandardSQL
SELECT week, event.name as event_name, COUNT(event.name) as event_count
FROM 
(TABLE_DATE_RANGE([firebase-analytics-sample-data:android_dataset.app_events_],DATE_ADD(CURRENT_TIMESTAMP(), -1, 'WEEK'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'WEEK'))), ....
....
....
(TABLE_DATE_RANGE([firebase-analytics-sample-data:android_dataset.app_events_],DATE_ADD(CURRENT_TIMESTAMP(), -12, 'WEEK'), DATE_ADD(CURRENT_TIMESTAMP(), -12, 'WEEK'))),
UNNEST(event_dim) as event
GROUP BY event_name
ORDER BY week DESC
LIMIT 12;

注意:通配符表不支持视图.查询返回错误 如果通配符表与数据集中的任何视图匹配.目前， 针对多个表的查询不支持缓存的结果 使用通配符功能(即使使用缓存结果"选项为 选中).如果您多次运行相同的通配符查询，则您将 为每个查询收费.通配符表支持本机BigQuery存储 只要.查询外部表或表时，不能使用通配符 视图(源).

Note: wildcard table does not support views. The query returns an error if the wildcard table matches any views in the dataset. Currently, cached results are not supported for queries against multiple tables using a wildcard function (even if the Use Cached Results option is checked). If you run the same wildcard query multiple times, you are billed for each query. Wildcard tables support native BigQuery storage only. You cannot use wildcards when querying an external table or a view (source).

总结一下:

• 如果我不必每周手动输入日期字段，将会更快很多，但是脚本知道"当前的我们所在的一周的索引号，并更新最近12周的数据.

• It would be a lot faster if I did not have to manually input the date fields every week, but the script "knows" the current week's index number we are in, and updates the 12 most recent weeks' data.

附录

• 每周2017年7月24日2017年7月30日20170724 20170730
• 2017年7月31日当周2017年8月6日20170731 20170806
• 第32周2017年8月7日2017年8月13日20170807 20170813
• 第33周2017年8月14日2017年8月20日20170814 20170820
• 第34周2017年8月21日2017年8月27日20170821 20170827
• 第35周2017年8月28日2017年9月3日20170828 20170903
• 第36周2017年9月4日2017年9月10日20170904 20170910
• 第37周2017年9月11日2017年9月17日20170911 20170917
• 第38周2017年9月18日2017年9月24日20170918 20170924
• 第39周2017年9月25日2017年10月1日20170925 20171001
• 第40周2017年10月2日2017年10月8日20171002 20171008
• 第41周2017年10月9日2017年10月15日20171009 20171015

• Week 30 July 24, 2017 July 30, 2017 20170724 20170730
• Week 31 July 31, 2017 August 6, 2017 20170731 20170806
• Week 32 August 7, 2017 August 13, 2017 20170807 20170813
• Week 33 August 14, 2017 August 20, 2017 20170814 20170820
• Week 34 August 21, 2017 August 27, 2017 20170821 20170827
• Week 35 August 28, 2017 September 3, 2017 20170828 20170903
• Week 36 September 4, 2017 September 10, 2017 20170904 20170910
• Week 37 September 11, 2017 September 17, 2017 20170911 20170917
• Week 38 September 18, 2017 September 24, 2017 20170918 20170924
• Week 39 September 25, 2017 October 1, 2017 20170925 20171001
• Week 40 October 2, 2017 October 8, 2017 20171002 20171008
• Week 41 October 9, 2017 October 15, 2017 20171009 20171015

推荐答案

如果我不必每周手动输入日期字段，将会更快很多，但是脚本知道"我们所在的星期数，并根据给定的一周更新了最近12周的数据我们在.

It would be a lot faster if i did not have to manually input the date fields every week), but the script "knows" the week-number we are in, and updates the 12 most recent weeks' data given the week we are in.

以下是用于BigQuery标准SQL

Below is for BigQuery Standard SQL

WHERE _TABLE_SUFFIX 
  BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 12 * 7 + EXTRACT(DAYOFWEEK FROM CURRENT_DATE()) - 2 DAY)) 
  AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL EXTRACT(DAYOFWEEK FROM CURRENT_DATE()) - 1 DAY))

下面仅显示输出

#standardSQL
SELECT 
  FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 2 * 7 + EXTRACT(DAYOFWEEK FROM CURRENT_DATE()) - 2 DAY)) first_day,
  FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL EXTRACT(DAYOFWEEK FROM CURRENT_DATE()) - 1 DAY)) last_day  

-

first_day   last_day     
20171002    20171015       

只要您在脚本上运行，它就会为您返回最近12周的开始和结束时间

Whenever you run above script - it will return you the start and end of most recent 12 weeks period

更新为:

我在上面发布了我的第一个迭代答案

I posted my first iteration answer above in what i would want

#standardSQL
SELECT 
  CONCAT(
    FORMAT_DATE('Week %W %d %B %Y, ', first_day),
    FORMAT_DATE('%d %B %Y, ', last_day), 
    FORMAT_DATE('%Y%m%d ', first_day),
    FORMAT_DATE('%Y%m%d', last_day)
  ) wk
FROM (
  SELECT 
    DATE_SUB(CURRENT_DATE(), INTERVAL 1 * 7 + EXTRACT(DAYOFWEEK FROM CURRENT_DATE()) - 2 DAY) first_day,
    DATE_SUB(CURRENT_DATE(), INTERVAL EXTRACT(DAYOFWEEK FROM CURRENT_DATE()) - 1 DAY) last_day 
)

与输出

wk   
Week 41 09 October 2017, 15 October 2017, 20171002 20171015

这篇关于如何自动更新最近12周的数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！