Home > Net >  How do I split a duration of time into hourly intervals in BigQuery?
How do I split a duration of time into hourly intervals in BigQuery?

Time:08-29

This is my table.

Location Date Employee start end
A 2021-01-01 A1 10 15
A 2021-01-01 A1 15 16
B 2021-01-01 B1 16 21
C 2021-01-01 C1 11 15

Here is the expected output:

Location Date Employee start end
A 2021-01-01 A1 10 15
A 2021-01-01 A1 11 15
A 2021-01-01 A1 12 15
A 2021-01-01 A1 13 15
A 2021-01-01 A1 14 15
A 2021-01-01 A1 15 15
A 2021-01-01 A1 15 16
A 2021-01-01 A1 16 16
B 2021-01-01 B1 16 21
B 2021-01-01 B1 17 21
B 2021-01-01 B1 18 21
B 2021-01-01 B1 19 21
B 2021-01-01 B1 20 21
B 2021-01-01 B1 21 21
C 2021-01-01 C1 11 15
C 2021-01-01 C1 12 15
C 2021-01-01 C1 13 15
C 2021-01-01 C1 14 15
C 2021-01-01 C1 15 15

Please help me how to split like this in BigQuery.

CodePudding user response:

SELECT * EXCEPT(t) REPLACE(t AS start)
  FROM my_table, UNNEST(GENERATE_ARRAY(start, `end`)) t
 ORDER BY Location, start;
Query results:
 ---------- ------------ ---------- ------- ----- 
| Location |    Date    | Employee | start | end |
 ---------- ------------ ---------- ------- ----- 
| A        | 2021-01-01 | A1       |    10 |  15 |
| A        | 2021-01-01 | A1       |    11 |  15 |
| A        | 2021-01-01 | A1       |    12 |  15 |
| A        | 2021-01-01 | A1       |    13 |  15 |
| A        | 2021-01-01 | A1       |    14 |  15 |
| A        | 2021-01-01 | A1       |    15 |  15 |
| A        | 2021-01-01 | A1       |    15 |  16 |
| A        | 2021-01-01 | A1       |    16 |  16 |
| B        | 2021-01-01 | B1       |    16 |  21 |
| B        | 2021-01-01 | B1       |    17 |  21 |
| B        | 2021-01-01 | B1       |    18 |  21 |
| B        | 2021-01-01 | B1       |    19 |  21 |
| B        | 2021-01-01 | B1       |    20 |  21 |
| B        | 2021-01-01 | B1       |    21 |  21 |
| C        | 2021-01-01 | C1       |    11 |  15 |
| C        | 2021-01-01 | C1       |    12 |  15 |
| C        | 2021-01-01 | C1       |    13 |  15 |
| C        | 2021-01-01 | C1       |    14 |  15 |
| C        | 2021-01-01 | C1       |    15 |  15 |
 ---------- ------------ ---------- ------- ----- 
  • Related