title: Is there a way to resample time series data in BigQuery?
body: <p>pandas DataFrame has <code>resample</code> method like below, what I would like to achieve is equivalent method by querying in BigQuery. </p>
  
  <p><strong>Example method in pandas</strong><br>
  Now I have data like this. assuming same data is stored in bigquery.</p>
  
  <pre><code>In [2]: df.head()
  Out[2]: 
                          Open     High      Low    Close  Volume
  Gmt time                                                       
  2016-01-03 22:00:00  1.08730  1.08730  1.08702  1.08714    8.62
  2016-01-03 22:01:00  1.08718  1.08718  1.08713  1.08713    3.75
  2016-01-03 22:02:00  1.08714  1.08721  1.08714  1.08720    4.60
  2016-01-03 22:03:00  1.08717  1.08721  1.08714  1.08721    7.57
  2016-01-03 22:04:00  1.08718  1.08718  1.08711  1.08711    5.52
  </code></pre>
  
  <p>Then resample the data with 5 minutes frequency using DataFrame.</p>
  
  <pre><code>In [3]: ohlcv = {
        :         'Open':'first',
        :         'High':'max',
        :         'Low':'min',
        :         'Close':'last',
        :         'Volume':'sum'
        :         }
        : df = df.resample('5T').apply(ohlcv)  # 5 minutes frequency
        : df = df[['Open', 'High', 'Low', 'Close', 'Volume']]  # reorder columns
        : df.head()
        : 
        : 
  Out[3]: 
                          Open     High      Low    Close  Volume
  Gmt time                                                       
  2016-01-03 22:00:00  1.08730  1.08730  1.08702  1.08711   30.06
  2016-01-03 22:05:00  1.08711  1.08727  1.08709  1.08709  190.63
  2016-01-03 22:10:00  1.08708  1.08709  1.08662  1.08666  168.79
  2016-01-03 22:15:00  1.08666  1.08674  1.08666  1.08667  223.83
  2016-01-03 22:20:00  1.08667  1.08713  1.08666  1.08667  170.17
  </code></pre>
  
  <p>This can be done after fetching 1 minute frequency data from bigquery.<br>
  But is there a way to <code>QUERY</code> resampling in bigquery?</p>
  
  <h2>EDIT</h2>
  
  <p>detailed explanation of pandas DataFrame resample.</p>
  
  <pre><code>                        Open     High      Low    Close  Volume
  Gmt time                                                       
  # 1 minute frequency data stored in bigquery
  2016-01-03 22:00:00  1.08730  1.08730  1.08702  1.08714    8.62
  2016-01-03 22:01:00  1.08718  1.08718  1.08713  1.08713    3.75
  2016-01-03 22:02:00  1.08714  1.08721  1.08714  1.08720    4.60
  2016-01-03 22:03:00  1.08717  1.08721  1.08714  1.08721    7.57
  2016-01-03 22:04:00  1.08718  1.08718  1.08711  1.08711    5.52
  
  2016-01-03 22:05:00  1.08711  1.08714  1.08711  1.08711   27.47
  2016-01-03 22:06:00  1.08717  1.08720  1.08711  1.08711   21.58
  2016-01-03 22:07:00  1.08713  1.08718  1.08712  1.08715   28.12
  2016-01-03 22:08:00  1.08714  1.08723  1.08712  1.08718   49.74
  2016-01-03 22:09:00  1.08722  1.08727  1.08709  1.08709   63.72
  
  # expected query result
  # above will be resampled into below..
  2016-01-03 22:00:00  1.08730  1.08730  1.08702  1.08711   30.06
  2016-01-03 22:05:00  1.08711  1.08727  1.08709  1.08709  190.63
  # method to resample 'first'  'max'    'min'    'last'    'sum'
  </code></pre>
  
  <p>First 5 rows (22:00 to 22:04) in 1min frequency is resampled into 1 row (22:00),<br>
  next 5 rows (22:05 to 22:09) into (22:05).<br>
  Resampling methods are <code>first</code>, <code>max</code>, <code>min</code>, <code>last</code> and <code>sum</code> respectively.  </p>
  
  <p><code>first</code> computes first value of group (which here means 5 rows)<br>
  <code>max</code> computes maximum value,<br>
  <code>min</code> computes minimum value,<br>
  <code>last</code> computes last value,<br>
  <code>sum</code>  computes sum of the column in the group</p>
  
  <p>for more detail see pandas <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.resample.html" rel="nofollow noreferrer">Document</a></p>