Is there a way to resample time series data in BigQuery?

Question

pandas DataFrame has resample method like below, what I would like to achieve is equivalent method by querying in BigQuery.

Example method in pandas
Now I have data like this. assuming same data is stored in bigquery.

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

Then resample the data with 5 minutes frequency using DataFrame.

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

This can be done after fetching 1 minute frequency data from bigquery.
But is there a way to QUERY resampling in bigquery?

EDIT

detailed explanation of pandas DataFrame resample.

                        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'

First 5 rows (22:00 to 22:04) in 1min frequency is resampled into 1 row (22:00),
next 5 rows (22:05 to 22:09) into (22:05).
Resampling methods are first, max, min, last and sum respectively.

first computes first value of group (which here means 5 rows)
max computes maximum value,
min computes minimum value,
last computes last value,
sum computes sum of the column in the group

for more detail see pandas Document

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2017-02-17T17:03:50

Try below

#standardSQL
SELECT * EXCEPT(step) 
FROM (
  SELECT *, TIMESTAMP_DIFF(TIMESTAMP(ts), 
              TIMESTAMP(MIN(ts) OVER(ORDER BY ts)), MINUTE) AS step
  FROM yourTable
)
WHERE MOD(step, 5) = 0
-- ORDER BY ts

Sampling interval can be controlled by changing 5 in MOD(step, 5) and MINUTE in TIMESTAMP_DIFF

You can play with this using below dummy data

WITH yourTable AS (
  SELECT '2016-01-03 22:00:00' AS ts, 1.08730 AS Open, 1.08730 AS High, 1.08702 AS Low, 1.08714 AS Close, 8.62 AS Volume UNION ALL
  SELECT '2016-01-03 22:01:00', 1.08718, 1.08718, 1.08713, 1.08713, 3.75 UNION ALL
  SELECT '2016-01-03 22:02:00', 1.08714, 1.08721, 1.08714, 1.08720, 4.60 UNION ALL
  SELECT '2016-01-03 22:03:00', 1.08717, 1.08721, 1.08714, 1.08721, 7.57 UNION ALL
  SELECT '2016-01-03 22:04:00', 1.08718, 1.08718, 1.08711, 1.08711, 5.52 UNION ALL
  SELECT '2016-01-03 22:05:00', 1.08718, 1.08718, 1.08713, 1.08713, 3.75 UNION ALL
  SELECT '2016-01-03 22:06:00', 1.08714, 1.08721, 1.08714, 1.08720, 4.60 UNION ALL
  SELECT '2016-01-03 22:07:00', 1.08717, 1.08721, 1.08714, 1.08721, 7.57 UNION ALL
  SELECT '2016-01-03 22:08:00', 1.08718, 1.08718, 1.08711, 1.08711, 5.52 UNION ALL
  SELECT '2016-01-03 22:09:00', 1.08718, 1.08718, 1.08713, 1.08713, 3.75 UNION ALL
  SELECT '2016-01-03 22:10:00', 1.08714, 1.08721, 1.08714, 1.08720, 4.60 UNION ALL
  SELECT '2016-01-03 22:11:00', 1.08717, 1.08721, 1.08714, 1.08721, 7.57 UNION ALL
  SELECT '2016-01-03 22:12:00', 1.08718, 1.08718, 1.08711, 1.08711, 5.52 
)

Below version implements "panda's resample" (per logic in updated question)

#standardSQL
SELECT 
  MIN(ts) AS ts,
  ARRAY_AGG(Open ORDER BY ts)[OFFSET (0)] AS Open,
  MAX(High) AS High,
  MIN(Low) AS Low,
  ARRAY_AGG(Close ORDER BY ts DESC)[OFFSET (0)] AS Close,
  SUM(Volume) AS Volume
FROM (
  SELECT *, DIV(TIMESTAMP_DIFF(TIMESTAMP(ts), 
              TIMESTAMP(MIN(ts) OVER(ORDER BY ts)), MINUTE), 5) AS grp
  FROM yourTable
)
GROUP BY grp
-- ORDER BY ts

Or further simplified version, with just one GROUP BY and windowing function. Also assuming your data is for later than '2000-01-01 00:00:00' - otherwise you can adjust accordingly

#standardSQL
SELECT 
  MIN(ts) AS ts,
  ARRAY_AGG(Open ORDER BY ts)[OFFSET (0)] AS Open,
  MAX(High) AS High,
  MIN(Low) AS Low,
  ARRAY_AGG(Close ORDER BY ts DESC)[OFFSET (0)] AS Close,
  SUM(Volume) AS Volume
FROM yourTable
GROUP BY DIV(TIMESTAMP_DIFF(TIMESTAMP(ts), 
             TIMESTAMP('2000-01-01 00:00:00'), MINUTE), 5)
-- ORDER BY ts

Is there a way to resample time series data in BigQuery?

EDIT

1 Answers