Блог

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

У меня было много проблем с тем, чтобы массивы случайных чисел не повторялись в каждой отдельной строке. Я нашел обходной путь, но является ли это ожидаемым поведением? Ниже я опубликую два «метода» (один с желаемыми результатами, другой с плохими результатами). Обратите внимание, что оба метода отлично работают, если вы не используете массив, а просто генерируете одно случайное число.

Метод 1 (ПЛОХИЕ результаты):

 SELECT
    (
        SELECT
            ARRAY(
                SELECT AS STRUCT
                    RAND() AS random
                FROM UNNEST(GENERATE_ARRAY(0, 10, 1)) AS _time
            ) AS random_for_times
    )
FROM UNNEST(GENERATE_ARRAY(0, 10, 1))
 

Метод 2 (ХОРОШИЕ результаты):

 SELECT
    (
        SELECT
            ARRAY(
                SELECT AS STRUCT
                    RAND() AS random
                FROM UNNEST(GENERATE_ARRAY(0, 10, 1)) AS _time
            ) AS random_for_times
        FROM (SELECT NULL FROM UNNEST([0]))
    )
FROM UNNEST(GENERATE_ARRAY(0, 10, 1))
 

Пример результатов — Метод 1 (НЕВЕРНЫЙ):

 Row 1
0.5431173080158003
0.5585452983410205
...
Row 2   
0.5431173080158003
0.5585452983410205
...
 

Пример результатов — Метод 2 (ХОРОШИЙ):

 Row 1
0.49639706531271377
0.1604380522058521
...
Row 2   
0.7971869432989377
0.9815667330115473
...
 

РЕДАКТИРОВАТЬ: смотрите Ниже некоторые альтернативные примеры, которые похожи, после теории Юнь Чжана о подзапросах. Ваше решение было полезно для проблемы, которую я опубликовал, но обратите внимание, что все еще есть некоторые случаи, которые я нахожу непонятными. Кроме того, хотя я согласен, что вы, вероятно, правы в отношении того, что подзапросы привязаны к проблеме: не должен ли подзапрос (особенно без предложения FROM) с меньшей вероятностью повторно использовать его результаты, чем выбор «нормального» значения? Иногда люди говорят о проблемах с производительностью подзапросов, потому что они предположительно вычисляются один раз для каждой строки, даже если результаты могут быть одинаковыми.

Согласны ли вы с тем, что это может быть ошибкой?

Приведенные ниже примеры показывают, что проблема не обязательно даже в создании массива случайных чисел — даже выполнение подвыборки, в которой просто случайно есть несвязанный массив, может вызвать проблемы с RAND() . Проблема устраняется путем устранения подвыбора, путем выбора только случайного значения из подвыбора или путем включения значения внутри массива, которое варьируется в зависимости от строки. Странно !!!

ПЛОХО

 SELECT
    (SELECT AS STRUCT RAND() AS r, ARRAY(SELECT 1) AS a)
FROM UNNEST(GENERATE_ARRAY(0, 5, 1)) AS u
 

ИСПРАВЛЕНИЕ # 1 — нет подвыбора

 SELECT
    STRUCT(RAND() AS r, ARRAY(SELECT 1) AS a)
FROM UNNEST(GENERATE_ARRAY(0, 5, 1)) AS u
 

ИСПРАВЛЕНИЕ # 2 — выберите только r

 SELECT
    (SELECT AS STRUCT RAND() AS r, ARRAY(SELECT 1) AS a).r
FROM UNNEST(GENERATE_ARRAY(0, 5, 1)) AS u
 

Исправление # 3 — Массив содержит «u»

 SELECT
    (SELECT AS STRUCT RAND() AS r, ARRAY(SELECT u) AS a).r
FROM UNNEST(GENERATE_ARRAY(0, 5, 1)) AS u
 

Не понял, почему первый запрос не сработал, но у меня есть более простая версия, которая работает для вас:

 SELECT (
          SELECT array_agg(RAND()) AS random
          FROM UNNEST(GENERATE_ARRAY(0, 10, 1)) AS _time
       ) AS random_for_times
FROM UNNEST(GENERATE_ARRAY(0, 10, 1))
 

Обновление: позже я понял, что проблема в ARRAY(subquery) том, что до тех пор, пока вы можете избежать использования его в вашем случае (как в моем запросе выше), все должно быть в порядке.