限制在两个日期分区之间进行选择的视图

Question

我希望为Google Analytics着陆页定义一个视图。我试图通过保存以下查询作为视图设置此：

SELECT 
    date, 
    fullVisitorId AS fv, 
    visitID AS v, 
    h.page.pagePath AS landing_page 
FROM 
    `project-id.dataset.ga_sessions_*`, UNNEST(hits) AS h 
WHERE 
    hitNumber = 1 

在这种加入到这一观点的疑问我打算将它们限制在两者之间的日期分区，就像这样：

SELECT 
    sessions.date, 
    fullVisitorId AS fv, 
    visitId AS v, 
    landing_page 
FROM `project-id.dataset.ga_sessions_*` AS sessions, UNNEST(hits) AS h 
JOIN `project-id.dataset.landing_pages` AS landing_pages 
ON landing_pages.fv = sessions.fullVisitorId 
AND landing_pages.date = sessions.date 
AND landing_pages.v = sessions.visitId 
WHERE 
    _TABLE_SUFFIX BETWEEN '20170108' AND '20170108' 

这似乎仍然选择大容量的数据~5GB，而不是预计将有一天的〜60MB。

如何重新编写视图，以便它只选择由消费查询定义的相关日期分区？

Answer 1

确保在视图定义中包含_TABLE_SUFFIX，以便您可以在视图上的查询中引用它。下面是转换_TABLE_SUFFIX一个日期的例子：

SELECT 
    date, 
    fullVisitorId AS fv, 
    visitID AS v, 
    h.page.pagePath AS landing_page, 
    PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) AS sessions_date 
FROM 
    `project-id.dataset.ga_sessions_*`, UNNEST(hits) AS h 
WHERE 
    hitNumber = 1; 

现在尝试查询过的观点：

SELECT 
    COUNT(DISTINCT fullVisitorId) AS total_visitors 
FROM `dataset.view_name` 
WHERE sessions_date = '2017-01-08';