Блог

Главная - Вопросы по программированию - BigQuery: НЕУСТАНОВЛЕННЫЙ результат, содержащий несколько строк

BigQuery: НЕУСТАНОВЛЕННЫЙ результат, содержащий несколько строк

# #sql #google-bigquery

Вопрос:

У меня есть UDF, который возвращает несколько строк:

 CREATE OR REPLACE FUNCTION dataset.multi_row() RETURNS ARRAY<STRUCT<r1 FLOAT64, r2 FLOAT64>> LANGUAGE js AS """
  return [{
    "r1": 10 * Math.random(),
    "r2": 10 * Math.random()
  }, {
    "r1": 100 * Math.random(),
    "r2": 100 * Math.random()
  }]
""";

Допустим, этот UDF используется в запросе, который создает несколько строк, например

 SELECT dataset.multi_row() FROM UNNEST([1,2,3])

Row   f0_.r1              f0_.r2
---   -----------------   -----------------
1     9.328970861925416   9.61853335054045
      20.700051452811017  87.67054242459575
2     4.184274373335275   6.136918172331227
      26.790853709322747  35.848885881352
3     0.32172810017527365 7.416528380222973
      70.21210223299556   57.644422256839746

Я хотел бы преобразовать каждую строку и запись массива в отдельную строку, чтобы это выглядело так:

 Row   f0_.r1              f0_.r2
---   -----------------   -----------------
1     9.328970861925416   9.61853335054045
2     20.700051452811017  87.67054242459575
3     4.184274373335275   6.136918172331227
4     26.790853709322747  35.848885881352
5     0.32172810017527365 7.416528380222973
6     70.21210223299556   57.644422256839746

Как выглядит SQL — запрос, чтобы получить этот результат?

Ответ №1:

Вы можете попробовать сохранить данные в временной таблице BQ, затем, следуя коду, это будут unnest данные

 WITH data AS (
SELECT
   [9.328970861925416, 20.700051452811017] as col_1,
   [9.61853335054045, 87.67054242459575] as col_2 union all
SELECT
   [4.184274373335275, 26.790853709322747] as col_1,
   [6.136918172331227, 35.848885881352] as col_2 union all
SELECT
   [0.32172810017527365, 70.21210223299556] as col_1,
   [7.416528380222973, 57.644422256839746] as col_2 
) 

SELECT col_1, col_2[OFFSET(off)] as col_2 FROM data ,unnest(col_1) as col_1 WITH OFFSET off

Результат будет таким :-

введите описание изображения здесь

Комментарии:

1. Мой вопрос был направлен на более общий случай. Например, количество строк, возвращаемых функцией UDF, может превышать 100 (в моем примере я намеренно упростил его, добавив всего 2 строки). Кроме того, количество строк, которые может возвращать SQL-запрос, также является динамическим (возможно, более 1000). Ваше решение слишком разработано специально для моего конкретного примера, я хочу что-то, что работает в общем случае.

2. UNNEST функция невозможна на уровне функции, вам нужно продумать решение по частям, сначала загрузив данные какому-нибудь промежуточному держателю, а затем отправив данные в UNNEST. Мое решение будет потенциальным индикатором в том же направлении. Возможным решением может быть конвейер «Потока данных» или некоторые запланированные запросы BQ на основе количества полей во временной (или промежуточной) таблице для удаления данных.

Метки: ,