Labels

Apache Hadoop (3) ASP.NET (2) AWS S3 (2) Batch Script (3) BigQuery (21) BlobStorage (1) C# (3) Cloudera (1) Command (2) Data Model (3) Data Science (1) Django (1) Docker (1) ETL (7) Google Cloud (5) GPG (2) Hadoop (2) Hive (3) Luigi (1) MDX (21) Mongo (3) MYSQL (3) Pandas (1) Pentaho Data Integration (5) PentahoAdmin (13) Polybase (1) Postgres (1) PPS 2007 (2) Python (13) R Program (1) Redshift (3) SQL 2016 (2) SQL Error Fix (18) SQL Performance (1) SQL2012 (7) SQOOP (1) SSAS (20) SSH (1) SSIS (42) SSRS (17) T-SQL (75) Talend (3) Vagrant (1) Virtual Machine (2) WinSCP (1)

Wednesday, June 22, 2016

BigQuery - Calculating Runnig Total


The below bigquery helps user to calculate running total of page visit:

SELECT DATE(a.date_time), a.hour,  a.minute, a.views, SUM(a.views) OVER (ORDER BY  a.hour,  a.minute) AS rt
FROM  [sampledate.clicks] a
JOIN  [sampledate.clicks] b ON  a.date_time = b.date_time
WHERE  b.date_time <= a.date_time 
AND DATE(a.date_time) = DATE(DATE_ADD(CURRENT_DATE(),-1,"DAY"))
ORDER BY  a.HOUR,  a.MINUTE



No comments:

Post a Comment