CODEWITHSUNDEEP
sqlgoogle-bigquery
user1979481
user1979481

Reputation: 59

How to add rows in BigQuery

I have data as in this BigQuery table 

+-------+---------+------+------+
| chrom |  start  | stop | cons |
+-------+---------+------+------+
| chr1  | 1       | 5    | 0.3  |
| chr1  | 5       | 7    | 0.7  |
| chr2  | 1       | 3    | 0.2  |
+-------+---------+------+------+

where the value in "cons" is true for every number from start to stop (though the number in stop itself is excluded). Now I'd like to have the data in an non aggregated way, so the table would look like this

+-------+---------+------+------+
| chrom |  start  | stop | cons |
+-------+---------+------+------+
| chr1  | 1       | 2    | 0.3  |
| chr1  | 2       | 3    | 0.3  |
| chr1  | 3       | 4    | 0.3  |
| chr1  | 4       | 5    | 0.3  |
| chr1  | 5       | 6    | 0.7  |
| chr1  | 6       | 7    | 0.7  |
| chr2  | 1       | 2    | 0.2  |
| chr2  | 2       | 3    | 0.2  |
+-------+---------+------+------+

The 

INSERT INTO

command is not supported by BigQuery. Any suggestions for a workaround?

Upvotes: 0

Views: 7485

Answers (1)

N.N.
N.N.

Reputation: 3172

  1. Create an aid table called "Numbers" and contains integer numbers from 0 - to largest reasonable value
  2. Cross Join your table with the Numbers table
  3. in the where close enforce Numbers.Number>=Start and Number.Number

It should look something like:

Select chrome, Numbers.Number as Start, Numbers.Number+1 as stop, cons
from yourtable
cross join Numbers
where Numbers.Number>=Start and Number.Number<stop
  1. Save your results to a new table or override the existing one.

Hope this helps

Upvotes: 2

Related Questions