时间戳

首页 > 解决方案 > 如何在 BigQuery 中返回混合/多个数据？

sql - 如何在 BigQuery 中返回混合/多个数据？

问题描述

我正在尝试使用 BigQuery（昨天才开始）从分析数据中获取页面的页面浏览量。两者都单独工作。

如果我运行第一个查询，

SELECT count(1) AS views, event_date AS date from `project-id.analytics_number.events_*`, UNNEST(event_params) as param1 where event_name = 'page_view' AND param1.value.string_value='/[page]' AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
  AND
  FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) GROUP BY event_date ORDER BY event_date ASC

它给了我如下数据：

[
  {
    "views": "6",
    "date": "20210101"
  },
  {
    "views": "55",
    "date": "20210102"
  },
  {
    "views": "3",
    "date": "20210103"
  },
  {
    "views": "12",
    "date": "20210104"
  },
  {
    "views": "5",
    "date": "20210105"
  }
]

然后，如果我尝试使用以下方法获取所有视图的总数：

 SELECT count(*) as count from `project-id.analytics_number.events_*`, UNNEST(event_params) 
   as param1 where event_name = 'page_view' AND param1.value.string_value='/[page]'

这给了我：

[
  {
    "count": "81"
  }
]

这按我的意愿工作。现在我正在尝试同时获取数据。在一个查询中，这可能吗？

我正在尝试这个，但没有工作。

with first as (
  SELECT count(1) AS views, event_date AS date from `project-id.analytics_number.events_*`, UNNEST(event_params) as param1 where event_name = 'page_view' AND param1.value.string_value='/[page]' AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
  AND
  FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) GROUP BY event_date ORDER BY event_date ASC
),
second as (
    SELECT count(*) as count from `project-id.analytics_number.events_*`, UNNEST(event_params) 
   as param1 where event_name = 'page_view' AND param1.value.string_value='/[page]'
)
select * from second

标签： sqlgoogle-bigquery

解决方案

您可以使用UNION ALL它们来组合它们：

SELECT count(*) AS views, event_date AS date 
FROM `project-id.analytics_number.events_*` CROSS JOIN
     UNNEST(event_params) as param1 
WHERE event_name = 'page_view' AND 
      param1.value.string_value='/[page]' AND
      _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)) AND
                            FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY event_date 
UNION ALL
SELECT count(*) as count, NULL 
FROM `project-id.analytics_number.events_*` CROSS JOIN
     UNNEST(event_params) as param1 
WHERE event_name = 'page_view' AND 
      param1.value.string_value='/[page]'

推荐阅读