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

Question

我正在尝试使用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

Answer 1

获得更好的性能

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
)
select views, date from (
select views, date from first
union all
select sum(views) as views, NULL as date from first
) order by date asc

Answer 2

您可以使用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]'