-1

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.

Data Beginner
  • 61
  • 1
  • 1
  • 6
  • *`2020-10-12 00:60:00`* 60 minutes ??? – Akina Oct 18 '22 at 12:24
  • @Akina Sorry, mistake about that. I manually typed the table and got the number in the wrong position, adjusted it now. Thanks for pointing that out. – Data Beginner Oct 18 '22 at 12:40
  • Howdy, you have described the problem well and even shown your code. Is the code not working? Are you looking for better code? – Kolban Oct 18 '22 at 15:34
  • Hey @Kolban, the code is not getting what I want by not grouping user_id and time together. I am hoping to achieve what the second table is showing. – Data Beginner Oct 18 '22 at 17:08

1 Answers1

0

I took your original table and executed the query

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

over it. My result was

enter image description here

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.

Kolban
  • 13,794
  • 3
  • 38
  • 60
  • Hi @Kolban, thanks for answering the question. In fact, I think you just answered the question with a simple `order by` to help with grouping user_id and time together. I been trying to use different functions including `partition by` but it seems sometimes simple syntax does the work. – Data Beginner Oct 18 '22 at 20:27
  • Could I ask another question related to the time function I am using? I saw a couple of other threads asking about calculating time intervals using functions like this: `TIMESTAMP_SECONDS(360*60 * DIV(UNIX_SECONDS(ordered_time), 360*60))` with google bigquery. Despite the function's works, I am not quite understanding why to use ordered_time divide 6 hours in seconds (360*60) and multiply 6 hours in seconds (360*60) again. Thanks – Data Beginner Oct 18 '22 at 20:32
  • The idea of "Constant * DIV(variable, Constant)" is that DIV is integer division ... i.e. round down to the nearest integer. Consider: 3 * (1 DIV 3) = 0 3 * (2 DIV 3) = 0 3 * (3 DIV 3) = 3 3 * (4 DIV 3) = 3 3 * (5 DIV 3) = 3 3 * (6 DIV 6) = 4 etc By DIV a seconds time by 60 seconds * 60 minutes * 6 hours will result in a single value for a 6 hour window. – Kolban Oct 18 '22 at 22:00
  • Hi @Kolban, sorry for my late reply. I am not quite following the 60 seconds * 60 minutes * 6 hours and the above formulas. I know what the DIV function does but I am stuck on finding the logic behind the calculations. Thanks for your help. – Data Beginner Oct 23 '22 at 21:39
  • How many seconds in 6 hours = 6 * 60 (minutes) * 60 (seconds) = 360 * 6 = 2160. Now to group an instant of time into a "6 hour bucket" ... take that instant of time (in seconds) ... divide it by 2160 ... throw away the remainder (DIV) and multiply it back by 2160. Any instant of time within that 6 hour period ... no matter what its value ... if you divide by 2160, throw away remainder, multiply by 2160 you'll have a constant value. – Kolban Oct 24 '22 at 04:06