Как я могу фильтровать строки, если один массив содержит все значения из другого массива с помощью BigQuery?

#sql #arrays #google-bigquery

#sql #массивы #google-bigquery

Вопрос:

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

 with t0 as (SELECT 1 as big_id, '101' as small_id,  0.99 as bottom, 1.03 top
      UNION ALL SELECT 1, '102', 1.05, 1.09
      UNION ALL SELECT 1, '103', 1.09, 1.13
      UNION ALL SELECT 1, '104', 1.2, 1.25
      UNION ALL SELECT 1, '105', 1.33, 1.39
      UNION ALL SELECT 2, '102', 1.05, 1.09
      UNION ALL SELECT 2, '103', 1.09, 1.13
      UNION ALL SELECT 2, '104', 1.2, 1.25
      UNION ALL SELECT 2, '105', 1.33, 1.39)
SELECT t0.big_id, row_number() OVER (PARTITION BY t0.big_id) group_id, ARRAY_AGG(t1.small_id) my_arrays FROM t0
CROSS JOIN t0 t1

WHERE t0.big_id = t1.big_id AND t1.top/t0.bottom BETWEEN 1 AND 1.15
GROUP BY t0.big_id, t0.small_id

У меня есть таблица с идентификаторами и верхними и нижними доверительными интервалами. Я хочу сравнить все уникальные small_id пары small_id , начиная с нижнего нижнего.
Уникальная пара означает: не нужно сравнивать 102 and 101 , если 101 and 102 уже сравнивается.
Затем мне нужно сгруппировать small_id s с аналогичными доверительными интервалами в массивы.
Затем мне нужно уменьшить группу, если все идентификаторы совпадают в какой-то большей группе в одном разделенном окне.
small_id не является числовым. Просто текстовая строка. Поэтому невозможно напрямую сравнивать small_id как числа, используя <> .

Эти строки мне нужно уменьшить, потому что я получил значения в других массивах

Как мне нужно изменить мой запрос, чтобы получить ожидаемый результат?

1. все эти крестики и стрелки действительно не помогают — пожалуйста, уточните, каков ваш ожидаемый результат на виду, а также какова логика, которую вы пытаетесь реализовать в своем запросе

2. Спасибо за ответ! Я добавил более подробную информацию об ожидаемом результате

Ответ №1:

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

 #standardsql
with t0 as (SELECT 1 as big_id, '101' as small_id,  0.99 as bottom, 1.03 top
  UNION ALL SELECT 1, '102', 1.05, 1.09
  UNION ALL SELECT 1, '103', 1.09, 1.13
  UNION ALL SELECT 1, '104', 1.2, 1.25
  UNION ALL SELECT 1, '105', 1.33, 1.39
  UNION ALL SELECT 2, '102', 1.05, 1.09
  UNION ALL SELECT 2, '103', 1.09, 1.13
  UNION ALL SELECT 2, '104', 1.2, 1.25
  UNION ALL SELECT 2, '105', 1.33, 1.39
), temp as (      
  SELECT t0.big_id, 
    row_number() OVER (PARTITION BY t0.big_id) group_id, 
    ARRAY_AGG(t1.small_id) my_arrays FROM t0
  CROSS JOIN t0 t1
  WHERE t0.big_id = t1.big_id AND t1.top/t0.bottom BETWEEN 1 AND 1.15
  GROUP BY t0.big_id, t0.small_id
)
select big_id, group_id, any_value(my_arrays) my_arrays 
from (
  select t1.*,
    ( select count(1)
      from t1.my_arrays id
      join t2.my_arrays id
      using(id)
      where t1.group_id != t2.group_id
    ) = array_length(t1.my_arrays) as flag
  from temp t1 
  join temp t2
  using (big_id)
)
group by big_id, group_id
having countif(flag) = 0

с выводом

1. рад, что это сработало для вас. изначально я думал, что это чисто рекурсивная логика, которая может быть решена с помощью скриптов или js udf. но после некоторого сна с течением времени и понял, что это можно сделать на основе набора — итак, поехали: o)

2. Спасибо. Это выглядит намного лучше, чем js. Что мы теряем, используя js udf?

3. обычно вы хотели бы перевести свою логику на основе набора, чтобы вы могли использовать чистый SQL, который является наиболее эффективным! Если это невозможно, то когда вы будете искать скрипты или js udf, которые имеют некоторые ограничения. итак, чтобы ответить на вопрос «что мы теряем», я бы сказал — эффективность операций на основе наборов