Блог

В BigQquery как я могу агрегировать массивы по элементам?

Например, если у меня есть эта таблица

Я хочу суммировать все векторные элементы и выводить [1 4 7, 2 5 8, 3 6 9] = [12, 15, 18]

Я могу СУММИРОВАТЬ поля с плавающей SELECT SUM(float_field) FROM table запятой с помощью, но когда я пытаюсь применить СУММУ к массиву, я получаю

Нет совпадающей подписи для агрегатной функции SUM для типов аргументов: ARRAY.
Поддерживаемые подписи: SUM(INT64); SUM(FLOAT64); SUM(ЧИСЛОВОЙ); SUM(BIGNUMERIC) в [1:8]

Я нашел ARRAY_AGG в документе, но это не то, что я хочу: он просто создает массив из значений.

Я думаю, вы хотите:

 select array_agg(sum_val order by id) as res
from (
    select idx, sum(val) as sum_val
    from mytable t
    cross join unnest(t.array_value) as val with offset as idx
    group by idx
) t
 

Я думаю, вы хотите:

 select array_agg(sum_val)
from (select (select sum(val)
              from unnest(t.array_value) val
             ) as sum_val
      from t
     ) x
 

Я думаю, технически вы просто ссылаетесь на отдельные значения в массивах, используя offset() or safe_offset() в случае, если могут отсутствовать значения

 -- example data
with temp as (
  select * from unnest([
    struct(1 as id, [1, 2, 3] as array_value),
    (2, [4,5,6]),
    (3, [7,8])
  ])
)

-- actual query
select
  [
    SUM( array_value[safe_offset(0)] ),
    SUM( array_value[safe_offset(1)] ),
    SUM( array_value[safe_offset(2)] )
  ] as result_array
from temp
 

Я помещаю их в результирующий массив, но вам не обязательно это делать. У меня в последнем массиве отсутствовало одно значение, чтобы показать, что запрос не прерывается. Если вы хотите, чтобы он сломался, вы должны использовать offset() без ‘safe_’

Ниже приведен стандартный SQL для BigQuery

 select array_agg(val order by offset) 
from (
  select offset, sum(val) as val 
  from `project.dataset.table` t, 
  unnest(array_value) as val with offset 
  group by offset
)