Search code examples
sqlgroup-bygoogle-bigquery

Grouping by rows based on time interval in bigquery

I have a table as below with per minute ('time' column) records for multiple user and for different dates. I have created another column (time_3min) for 3 minute interval. Now I want to group by rows based on time_3min column and sum up the 'value' column.

userId  date            time            time_3min   value
abc 2023-04-10  01:00:00    01:00:00    2
abc 2023-04-10  01:01:00    01:00:00    5
abc 2023-04-10  01:02:00    01:00:00    3
abc 2023-04-10  01:03:00    01:03:00    6
abc 2023-04-10  01:04:00    01:03:00    7
abc 2023-04-10  01:05:00    01:03:00    1
abc 2023-04-11  01:00:00    01:00:00    10
abc 2023-04-11  01:01:00    01:00:00    5
abc 2023-04-11  01:02:00    01:00:00    3
abc 2023-04-11  01:03:00    01:03:00    7
abc 2023-04-11  01:04:00    01:03:00    4
abc 2023-04-11  01:05:00    01:03:00    3
xyz 2023-04-10  01:00:00    01:00:00    11
xyz 2023-04-10  01:01:00    01:00:00    8
xyz 2023-04-10  01:02:00    01:00:00    6
xyz 2023-04-10  01:03:00    01:03:00    4
xyz 2023-04-10  01:04:00    01:03:00    6
xyz 2023-04-10  01:05:00    01:03:00    2
xyz 2023-04-11  01:00:00    01:00:00    11
xyz 2023-04-11  01:01:00    01:00:00    8
xyz 2023-04-11  01:02:00    01:00:00    7
xyz 2023-04-11  01:03:00    01:03:00    4
xyz 2023-04-11  01:04:00    01:03:00    6
xyz 2023-04-11  01:05:00    01:03:00    6

I want to display the data as below

userId  date            time_3min   sum(value)
abc 2023-04-10  01:00:00    10
abc 2023-04-10  01:03:00    14
abc 2023-04-11  01:00:00    18
abc 2023-04-11  01:03:00    14
xyz 2023-04-10  01:00:00    25
xyz 2023-04-10  01:03:00    12
xyz 2023-04-11  01:00:00    26
xyz 2023-04-11  01:03:00    16

Could you please suggest how to use right group by clause to calculate sum with order by based on date and time_3min

group by and order by in bigquery

Solution

  • It should be just:

     WITH data AS (
  SELECT 'abc' as userId, '2023-04-10' as date, '01:00:00' as time, '01:00:00' as time_3min, 2 as value UNION ALL
  SELECT 'abc', '2023-04-10', '01:01:00', '01:00:00', 5 UNION ALL
  SELECT 'abc', '2023-04-10', '01:02:00', '01:00:00', 3 UNION ALL
  SELECT 'abc', '2023-04-10', '01:03:00', '01:03:00', 6 UNION ALL
  SELECT 'abc', '2023-04-10', '01:04:00', '01:03:00', 7 UNION ALL
  SELECT 'abc', '2023-04-10', '01:05:00', '01:03:00', 1 UNION ALL
  SELECT 'abc', '2023-04-11', '01:00:00', '01:00:00', 10 UNION ALL
  SELECT 'abc', '2023-04-11', '01:01:00', '01:00:00', 5 UNION ALL
  SELECT 'abc', '2023-04-11', '01:02:00', '01:00:00', 3 UNION ALL
  SELECT 'abc', '2023-04-11', '01:03:00', '01:03:00', 7 UNION ALL
  SELECT 'abc', '2023-04-11', '01:04:00', '01:03:00', 4 UNION ALL
  SELECT 'abc', '2023-04-11', '01:05:00', '01:03:00', 3 UNION ALL
  SELECT 'xyz', '2023-04-10', '01:00:00', '01:00:00', 11 UNION ALL
  SELECT 'xyz', '2023-04-10', '01:01:00', '01:00:00', 8 UNION ALL
  SELECT 'xyz', '2023-04-10', '01:02:00', '01:00:00', 6 UNION ALL
  SELECT 'xyz', '2023-04-10', '01:03:00', '01:03:00', 4 UNION ALL
  SELECT 'xyz', '2023-04-10', '01:04:00', '01:03:00', 6 UNION ALL
  SELECT 'xyz', '2023-04-10', '01:05:00', '01:03:00', 2 UNION ALL
  SELECT 'xyz', '2023-04-11', '01:00:00', '01:00:00', 11 UNION ALL
  SELECT 'xyz', '2023-04-11', '01:01:00', '01:00:00', 8 UNION ALL
  SELECT 'xyz', '2023-04-11', '01:02:00', '01:00:00', 7 UNION ALL
  SELECT 'xyz', '2023-04-11', '01:03:00', '01:03:00', 4 UNION ALL
  SELECT 'xyz', '2023-04-11', '01:04:00', '01:03:00', 6 UNION ALL
  SELECT 'xyz', '2023-04-11', '01:05:00', '01:03:00', 6 
)
SELECT userID
       ,date
      ,time_3min
      ,SUM(value)
FROM data
GROUP BY userID
        ,date
        ,time_3min

    enter image description here