CODEWITHSUNDEEP
sqlgoogle-bigquery
Raj N
Raj N

Reputation: 13

bigquery for time series data

I have a table like this. The row includes a timestamp and count as measurement of a value at that time. 

Row timestamp count
1 2018-08-20 04:01:39.108497 31
2 2018-08-20 04:01:45.109497 45
3 2018-08-20 04:01:49.109497 44
4 2018-08-20 04:02:39.102497 33
5 2018-08-20 04:02:45.101497 41
6 2018-08-20 04:02:49.103497 22
7 2018-08-20 04:03:39.102497 23
8 2018-08-20 04:03:45.102497 42
9 2018-08-20 04:03:49.103497 41

I would like to aggregate this into this as a minute level aggregation as avg(count)

Row timestamp count
1 2018-08-20 04:01:00 40
2 2018-08-20 04:02:00 32
3 2018-08-20 04:03:00 35

Please help. Thanks in advance

Upvotes: 1

Views: 606

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173028

Below is for BigQuery Standard SQL 

#standardSQL
SELECT TIMESTAMP_TRUNC(ts, MINUTE) dt, CAST(AVG(cnt) AS INT64) viewCount
FROM `project.dataset.table`
GROUP BY dt

If to apply to dummy data in your question as below 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT TIMESTAMP '2018-08-20 04:01:39.108497' ts, 31 cnt UNION ALL
  SELECT '2018-08-20 04:01:45.109497', 45 UNION ALL
  SELECT '2018-08-20 04:01:49.109497', 44 UNION ALL
  SELECT '2018-08-20 04:02:39.102497', 33 UNION ALL
  SELECT '2018-08-20 04:02:45.101497', 41 UNION ALL
  SELECT '2018-08-20 04:02:49.103497', 22 UNION ALL
  SELECT '2018-08-20 04:03:39.102497', 23 UNION ALL
  SELECT '2018-08-20 04:03:45.102497', 42 UNION ALL
  SELECT '2018-08-20 04:03:49.103497', 41 
)
SELECT TIMESTAMP_TRUNC(ts, MINUTE) dt, CAST(AVG(cnt) AS INT64) viewCount
FROM `project.dataset.table`
GROUP BY dt
-- ORDER BY dt

result is 

Row dt                      viewCount
1   2018-08-20 04:01:00 UTC 40   
2   2018-08-20 04:02:00 UTC 32   
3   2018-08-20 04:03:00 UTC 35

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270011

Just use TIMESTAMP_TRUNC():

select timestamp_trunc(minute, timestamp) as timestamp_min,
       sum(count)  -- or whatever aggregation you want
from t
group by timestamp_min;

Your question is unclear on what aggregation you want. For instance, "35" doesn't appear in the data.

Upvotes: 0

Related Questions