Hits Per Day In 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

Solution 1:

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:

selectdate(date_add(day, i, "DAY")) dayfrom  (select'2015-01-01'day) a 
crossjoin
(selectposition(
     split(
       rpad('', datediff('2015-01-15','2015-01-01')*2, 'a,'))) i 
 from (selectNULL)) b;

Then you can join this result with your query table:

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