Google Big Query中的每日点击次数 [英] Hits per day in Google Big Query

问题描述

我使用Google Big Query来查找每天点击量。这是我的查询，

I am using Google Big Query to find hits per day. Here is my query,

SELECT COUNT(*) AS Key, 
       DATE(EventDateUtc) AS Value 
FROM    [myDataSet.myTable] 
WHERE    ..... 
GROUP BY Value 
ORDER BY Value DESC 
LIMIT 1000;

这个工作正常，但忽略了0个点击的日期。我想包括这个。我无法在Google Big Query中创建临时表。如何解决这个问题。

This is working fine but it ignores the date with 0 hits. I wanna include this. I cannot create temp table in Google Big Query. How to fix this.

经过测试，发现错误字段'天'未找到。

SELECT COUNT(*) AS Key, 
       DATE(t.day) AS Value  from (
       select date(date_add(day, i, "DAY")) day
from  (select '2015-05-01 00:00' day) a 
cross join
(select 
   position(
     split(
       rpad('', datediff(CURRENT_TIMESTAMP(),'2015-05-01 00:00')*2, 'a,'))) i 
 from (select NULL)) b

       ) d
left join [sample_data.requests] t on d.day = t.day
GROUP BY Value 
ORDER BY Value DESC 
LIMIT 1000;

推荐答案

您可以查询表中存在的数据查询无法猜测表中缺少哪些日期。这个问题你需要用你的编程语言来处理，或者你可以

You can query data that exists in your tables, the query cannot guess which dates are missing from your table. This problem you need to handle either in your programming language, or you could join with a numbers table and generates the dates on the fly.

日期：

If you know the date range you have in your query, you can generate the days:

select date(date_add(day, i, "DAY")) day
from  (select '2015-01-01' day) a 
cross join
(select 
   position(
     split(
       rpad('', datediff('2015-01-15','2015-01-01')*2, 'a,'))) i 
 from (select NULL)) b;

然后你可以将这个结果加入你的查询表：

Then you can join this result with your query table:

SELECT COUNT(*) AS Key, 
       DATE(t.day) AS Value  from (...the.above.query.pasted.here...) d
left join [myDataSet.myTable] t on d.day = t.day
WHERE    ..... 
GROUP BY Value 
ORDER BY Value DESC 
LIMIT 1000;

这篇关于Google Big Query中的每日点击次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！