89708

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 BY Value ORDER BY Value DESC LIMIT 1000;

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.

Tested getting error Field 'day' not found.

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;

Answer1:

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;

Recommend

  • Regex: Ignore a part of the string
  • Filter log files(_success and _log) in FileSystem.liststatus
  • @RunWith causes Maven to ignore my Test running with my own runner
  • Need advice in designing tables in SQL-Server
  • CakePHP Subquery from SQL
  • How to subscribe to an event without preventing garbage collection?
  • Get textarea in codebehind
  • JSR-330 support in Picocontainer : @Inject … @Named(\"xxx)
  • Limiting recursion to certain level - Duplicate rows
  • Row Count Is Returning the incorrect number using RaptureXML
  • Meteor helpers not available in Angular template
  • Linq Objects Group By & Sum
  • Using $this when not in object context
  • Retrieving value from sql ExecuteScalar()
  • Illegal mix of collations for operation for date/time comparison
  • Jenkins: How To Build multiple projects from a TFS repository?
  • Sony Xperia Z Tablet not found by adb
  • How to recover from a Spring Social ExpiredAuthorizationException
  • Arrow is showed instead of the material design version hamburger icon. Why doesn't syncState in
  • How to limit post in wp_query
  • Why is the timeout on a windows udp receive socket always 500ms longer than set by SO_RCVTIMEO?
  • Release, debug version and Authorization Google?
  • Adding custom controls to a full screen movie
  • How can I estimate amount of memory left with calling System.gc()?
  • Delete MySQLi record without showing the id in the URL
  • Is there a mandatory requirement to switch app.yaml?
  • Comma separated Values
  • Matrix multiplication with MKL
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • Buffer size for converting unsigned long to string
  • KeystoneJS: Relationships in Admin UI not updating
  • Error creating VM instance in Google Compute Engine
  • how does django model after text[] in postgresql [duplicate]
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • File not found error Google Drive API
  • How to get Windows thread pool to call class member function?
  • Linking SubReports Without LinkChild/LinkMaster
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass
  • Checking variable from a different class in C#
  • Converting MP3 duration time