CODEWITHSUNDEEP
sqlmatrixgoogle-bigquery
Ilja
Ilja

Reputation: 1053

Bigquery - create a table with repeated integer sequence

I need to find a simple way to turn an integer sequence in Biquery into a matrix with zeros on empty spaces

Example:

v1   v2   v3
5    2    1

needs to become

v1 v2 v3 v4 v5
5  2  1  0  0
0  5  2  1  0
0  0  5  2  1

etc.

I looked in all the possible places around but could not find how to do it in BQ or SQL.

Thanks

Upvotes: 1

Views: 682

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

I would consider below solution to be generic enough 

#standardSQL
WITH sequence AS (
  SELECT ARRAY[5,2,1] AS num
),
numbers AS (
  SELECT ARRAY_LENGTH(num) AS len, 
    ARRAY_CONCAT(num, ARRAY( SELECT 0 FROM sequence, UNNEST(num) AS x)) AS num
  FROM sequence
)
SELECT 
  num[OFFSET(CASE WHEN 1 - n < 0 THEN 2 * len - n ELSE 1 - n END)],
  num[OFFSET(CASE WHEN 2 - n < 0 THEN 2 * len - n ELSE 2 - n END)],
  num[OFFSET(CASE WHEN 3 - n < 0 THEN 2 * len - n ELSE 3 - n END)],
  num[OFFSET(CASE WHEN 4 - n < 0 THEN 2 * len - n ELSE 4 - n END)],
  num[OFFSET(CASE WHEN 5 - n < 0 THEN 2 * len - n ELSE 5 - n END)]
FROM numbers, UNNEST(GENERATE_ARRAY(1, len)) AS n
ORDER BY n

You can easily adjust it to any sequence. For this you need to make changes in two places only:

  1. adjust ARRAY values in sequence subquery (line 2)

  2. add more rows like below into SELECT list

Row: 

num[OFFSET(CASE WHEN Z - n < 0 THEN 2 * len - n ELSE Z - n END)]

you need to add as meny of such so that total number of the row will be 2xZ-1 and each next row has Z incremented by 1
This can easily be script - so the whole query (or just SELECT list) will be generated for you then to run

If you are running this in client of your choice (python, go, etc.) - this can be part of your code so the whole stuff is automated

Upvotes: 2

JohnHC
JohnHC

Reputation: 11205

In its simplest form:

select v1, v2, v3, 0 as v4, 0 as v5
from MyTable
where (...)
union all
select 0, v1, v2, v3, 0
from MyTable
where (...)
union all
select 0,0,v1, v2, v3
from MyTable
where (...)
union all
select 0,0,0,v1, v2
from MyTable
where (...)
union all
select 0,0,0, 0,v1
from MyTable
where (...)

Upvotes: 0

Related Questions