Bigquery Sql For Sliding Window Aggregate

Hi I have a table that looks like this Date Customer Pageviews 2014/03/01 abc 5 2014/03/02 xyz 8 2014/03/03 abc 6 I want to get page vie

Solution 1:

How about this:

SELECT changes + changes1 + changes2 + changes3 changes28days, login, USEC_TO_TIMESTAMP(week)
FROM (
  SELECT changes,
         LAG(changes, 1) OVER (PARTITIONBY login ORDERBY week) changes1,
         LAG(changes, 2) OVER (PARTITIONBY login ORDERBY week) changes2,
         LAG(changes, 3) OVER (PARTITIONBY login ORDERBY week) changes3,
         login,
         week
  FROM (
    SELECTSUM(payload_pull_request_changed_files) changes, 
           UTC_USEC_TO_WEEK(created_at, 1) week,
           actor_attributes_login login,
    FROM [publicdata:samples.github_timeline]
    WHERE payload_pull_request_changed_files >0GROUPBY week, login
))
HAVING changes28days >0

For each user it counts how many changes they have submitted per week. Then with LAG() we can peek into the next row, how many changes they submitted the -1, -2, and -3 week. Then we just add those 4 weeks to see how many changes were submitted on the last 28 days.

Now you can wrap everything in a new query to filter users with changes>X, and count them.

Solution 2:

I have created the following "Times" table:

Table Details: Dim_Periods
Schema
DateTIMESTAMPYearINTEGERMonthINTEGERdayINTEGER         
QUARTER INTEGER     
DAYOFWEEK   INTEGER     
MonthStart  TIMESTAMP   
MonthEnd    TIMESTAMP   
WeekStart   TIMESTAMP   
WeekEnd TIMESTAMP   
Back30Days  TIMESTAMP-- the date 30 days before "Date"
Back7Days   TIMESTAMP-- the date 7 days before "Date"

and I use such query to handle "running sums"

SELECTDate,Count(*) as MovingCNT
FROM

(SELECTDate,
                Back7Days 
                    FROM DWH.Dim_Periods  
                 whereDate<timestamp(current_date()) ANDDate>= (DATE_ADD (CURRENT_TIMESTAMP(), -5, 'month'))
                )P
                CROSSJOINEACH
    (SELECT repository_url,repository_created_at
    FROM publicdata:samples.github_timeline
                ) L
        WHEREtimestamp(repository_created_at)>= Back7Days 
              ANDtimestamp(repository_created_at)<=DateGROUPEACHBYDate

Note that it can be used for "Month to date", Week to Date" "30 days back" etc. aggregations as well. However, performance is not the best and the query can take a while on larger data sets due to the Cartesian join. Hope this helps