How to groupby user_id and time using SQL Bigquery-CodePudding

I have a table that contains user_id, time (six hours interval), and average margin. I wanted to group by user_id and time (time in ascending order).

The table looks like this as shown below:

user_id	time	average_margin
5696	2020-10-12 00:00:00	0.29
5426	2020-10-08 12:00:00	0.38
5696	2020-10-12 06:00:00	0.47
5512	2020-10-08 12:00:00	0.06
5238	2020-10-08 12:00:00	0.80
5696	2020-10-12 00:00:00	0.72
5698	2020-10-08 12:00:00	0.64
5732	2020-10-12 06:00:00	0.27
5696	2020-10-08 12:00:00	0.75
5238	2020-10-08 12:00:00	0.32

I want the result to be something like this as shown below:

user_id	time	average_margin
5696	2020-10-08 00:00:00	0.29
5696	2020-10-12 06:00:00	0.75
5696	2020-10-12 12:00:00	0.47
5696	2020-10-13 18:00:00	0.75
5238	2020-10-08 12:00:00	0.80
5238	2020-10-09 06:00:00	0.72
5238	2020-10-11 12:00:00	0.64
5732	2020-10-12 06:00:00	0.27
5512	2020-10-08 12:00:00	0.06
5426	2020-10-08 12:00:00	0.32

The first is to be grouped by user_id and then grouped by time in ascending order.

Below is my code:

SELECT user_id,
       Time,
       AVG(margin) AS average_margin
FROM
  (SELECT user_id,
        TIMESTAMP_SECONDS(360*60 * DIV(UNIX_SECONDS(ordered_time), 360*60)) AS Time, # six hours interval
        SAFE_DIVIDE(SUM(gross_revenue), SUM(turnover)) AS margin
  FROM `table1`
  GROUP BY user_id, Time) a
GROUP BY user_id, Time

please ignore some of the values in the average_margin column for the second table, some values are not in the right position because I manually type the table.

Also, another question is for my code I am using SAFE_DIVIDE because I have an error of division by zero: -0.2 / 0. If anyone knows how to fix that because I don't want null values but calculated numbers.

Thank you very much for the help, please let me know if I need to further clarify anything.

CodePudding user response：

I took your original table and executed the query

SELECT * FROM `table` order by `user_id`, `time`

over it. My result was

I think I'm missing something from your question? If you can explain in context of this answer, it would help. I'll delete this answer if it isn't close to your question.