google-analytics - 限制 View 以在两个日期分区之间进行选择

Question

我希望为 Google Analytics 登录页面定义一个 View 。我试图通过将以下查询保存为 View 来进行设置:

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

在加入此 View 的查询中，我计划将它们限制在两个日期分区之间，如下所示:

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。

如何重新编写 View ，使其仅选择消费查询定义的相关日期分区？

Answer 1

确保包含 _TABLE_SUFFIX在 View 定义中，以便您可以在对 View 的查询中引用它。这是一个转换 _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;

现在尝试对 View 进行查询:

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