Group rows to one

Question

Suppose a source table like this:

Year        Long       Lat        Values    
========================================
2015         8          1           12     
2015         8          1           43
2015         8          1           11
...

The destiny table should be like this:

Year        Long       Lat        Field1          Field2        Field3  
========================================================================
2015         8          1           12             43             11
...

¿How can I do something like this in a single query (to use in Google BigQuery) ?

These post could be an approach of what I want: post1 post2

The "problem" is I will use this query in a Python Google Dataflow pipeline, so due to the restriction of read and write using apache beam I would need to do it in a single select (you can obviate the insert statement because I will use an apache beam function for that).

EXTRA

Using this select

select Fecha, Longitud, Latitud,
       ARRAY_AGG(Valor) as vector     
from `poc-cloud-209212.PruebasIoT.CanonicalFrames`
group by Fecha, Longitud, Latitud

If I try to use:

select Fecha, Longitud, Latitud,
       ARRAY_AGG(Valor)[offset(0)] as field1,    
       ARRAY_AGG(Valor)[offset(1)] as field2
from `poc-cloud-209212.PruebasIoT.CanonicalFrames`
group by Fecha, Longitud, Latitud

I have the error:

Array index 1 is out of bounds (overflow)

The solution

This is what worked for my use case:

Simple query to understand easier the way you could do it base on the table of this post (which could have more rows, with different values than shows in the schema):

select year, long, lat,
       array_agg(values)[SAFE_OFFSET(0)] as field1,
       array_agg(values)[SAFE_OFFSET(1)] as field2,
       array_agg(values)[SAFE_OFFSET(2)] as field3
from t
group by year, long, lat;

I think you want to use PIVOT. Cf. https://stackoverflow.com/a/10429399/6996150 PS: I love the ¿ character! — johey, Dec 04 '18 at 11:08
Like "DBMS" i'm using Google BigQuery, which use standard SQL. The logic is to read from a table which received "raw" data and they have to be stored in a "canonical" way — IoT user, Dec 04 '18 at 11:17
Btw, no - you will not be able to do this in a single select - two selects will work as it is described in posts you referenced in your question - consider voting them up if not yet :o) — Mikhail Berlyant, Dec 04 '18 at 16:04
@MikhailBerlyant I will try. For now I think I can do it with `ARRAY_AGG (Valor ORDER BY ID)[SAFE_OFFSET(0)] AS Field1, ARRAY_AGG (Valor ORDER BY ID)[SAFE_OFFSET(1)] AS Field2,<...>` in the select like @GordonLinoff said. The result is a table in which the columns _FieldX_ contais the values of the source table where the row where splited — IoT user, Dec 04 '18 at 16:21
yes - if you know in advance max number of columns in final output - this will work — Mikhail Berlyant, Dec 04 '18 at 16:38

score 1 · Accepted Answer · answered Dec 04 '18 at 11:42

1

You can use conditional aggregation. But I think the more typical method would be to put the fields in an array:

select year, long, lat,
       array_agg(values)
from t
group by year, long, lat;

Note that the order of the results is not guaranteed. SQL tables represent unordered sets, and your data has no column specifying the ordering.

You can easily expand this to fields:

select year, long, lat,
       array_agg(values)[offset(0)] as field1,
       array_agg(values)[offset(1)] as field2,
       array_agg(values)[offset(2)] as field3
from t
group by year, long, lat;

answered Dec 04 '18 at 11:42

Gordon Linoff

1,242,037
58
646
786

Thank you, Gordon. I added some information to the main post with the result of your firsts option. The second option I have an error – IoT user Dec 04 '18 at 12:16
2

use `SAFE_OFFSET()` instead of `OFFSET()` – Mikhail Berlyant Dec 04 '18 at 15:55
Cool the use of SAFE_OFFSET. Thanks – IoT user Dec 04 '18 at 16:11

Group rows to one

The solution

1 Answers1