Блог

Главная - Вопросы по программированию - Сравнение массива BQ из одной строки с остальными строками

Сравнение массива BQ из одной строки с остальными строками

# #sql #arrays #google-bigquery #difference

Вопрос:

У меня есть стол в BQ

     WITH tbl_campaign_ipmapping AS
(
  SELECT 'advertiser1' as advertiser, 'campaign1' as campaign,  ['10.0.0.0','20.0.0.0','30.0.0.0', '40.0.0.0'] AS ip_array UNION ALL
  SELECT 'advertiser1' as advertiser, 'campaign2' as campaign, ['10.0.0.0', '20.0.0.0', '50.0.0.0']  UNION ALL 
  SELECT 'advertiser1' as advertiser, 'campaign3' as campaign, ['10.0.0.0', '40.0.0.0', '60.0.0.0', '70.0.0.0', '80.0.0.0']  UNION ALL 
  SELECT 'advertiser1' as advertiser, 'campaign4' as campaign, ['10.0.0.0', '20.0.0.0', '30.0.0.0'] UNION ALL 
  
  SELECT 'advertiser2' , 'campaign1' ,  ['10.1.1.1','20.1.1.1','30.1.1.1', '40.1.1.1']  UNION ALL
  SELECT 'advertiser2' , 'campaign2' , ['10.1.1.1', '20.1.1.1', '50.1.1.1']  UNION ALL 
  SELECT 'advertiser2' , 'campaign3' , ['10.1.1.1', '40.1.1.1', '60.1.1.1', '70.1.1.1', '80.1.1.1']  UNION ALL 
  SELECT 'advertiser2', 'campaign4' , ['10.1.1.1', '20.1.1.1', '30.1.1.1']  
  
) select * from tbl_campaign_ipmapping

Для входного рекламодателя, допустим, рекламодателя 1, Логика, которая должна быть реализована, зависит
Для каждой строки извлеките список IP-адресов и сравните их с IP-адресами из всех остальных строк(за исключением текущей строки) и выведите IP-адреса, которых нет.

Таким образом, ожидаемый результат будет

 advertiser1, campaign1, ['50.0.0.0', '60.0.0.0', '70.0.0.0']
advertiser1,campaign2, ['30.0.0.0', '40.0.0.0', '60.0.0.0', '70.0.0.0']
advertiser1,campaign3, ['20.0.0.0','30.0.0.0', '50.0.0.0' ]
advertiser1,campaign4, ['40.0.0.0', '50.0.0.0', '60.0.0.0', '70.0.0.0'

На самом деле мне нужен не массив, а только счетчик. Просто перечислил массив в приведенном выше выводе, чтобы прояснить логику.

Ответ №1:

Попробуйте нижеприведенную версию — я чувствую, что она должна быть более оптимальной, чем предыдущие

 with advertiser_campaign_ip as (
  select advertiser, campaign, ip
  from (
    select advertiser, ip
    from tbl_campaign_ipmapping, unnest(ip_array) ip
    group by advertiser, ip
  ) join (
    select advertiser, campaign
    from tbl_campaign_ipmapping
  )
  using (advertiser)
)
select a.advertiser, a.campaign, 
  array_agg(a.ip) as missing_ip
from advertiser_campaign_ip a
left join (select advertiser, campaign, ip from tbl_campaign_ipmapping, unnest(ip_array) ip) b
using (advertiser, campaign, ip)
where b.ip is null
group by a.advertiser, a.campaign

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

1. вы пробовали эту версию?

Ответ №2:

Вы можете попробовать использовать другой CTE для поиска всех возможных IP-адресов, прежде чем использовать этот список для сбора всех других IP-адресов, не выделенных из текущего массива ip, например

 WITH tbl_campaign_ipmapping AS
(
  SELECT 'advertiser1' as advertiser, 'campaign1' as campaign,  ['10.0.0.0','20.0.0.0','30.0.0.0', '40.0.0.0'] AS ip_array UNION ALL
  SELECT 'advertiser1' as advertiser, 'campaign2' as campaign, ['10.0.0.0', '20.0.0.0', '50.0.0.0']  UNION ALL 
  SELECT 'advertiser1' as advertiser, 'campaign3' as campaign, ['10.0.0.0', '40.0.0.0', '60.0.0.0', '70.0.0.0', '80.0.0.0']  UNION ALL 
  SELECT 'advertiser1' as advertiser, 'campaign4' as campaign, ['10.0.0.0', '20.0.0.0', '30.0.0.0'] UNION ALL 
  
  SELECT 'advertiser2' , 'campaign1' ,  ['10.1.1.1','20.1.1.1','30.1.1.1', '40.1.1.1']  UNION ALL
  SELECT 'advertiser2' , 'campaign2' , ['10.1.1.1', '20.1.1.1', '50.1.1.1']  UNION ALL 
  SELECT 'advertiser2' , 'campaign3' , ['10.1.1.1', '40.1.1.1', '60.1.1.1', '70.1.1.1', '80.1.1.1']  UNION ALL 
  SELECT 'advertiser2', 'campaign4' , ['10.1.1.1', '20.1.1.1', '30.1.1.1']  
  
),
all_ips AS (
    SELECT DISTINCT ip 
    FROM tbl_campaign_ipmapping, unnest(ip_array) ip
    WHERE advertiser='advertiser1'
)
select 
    advertiser ,
    campaign,
    ARRAY(
        SELECT i.ip 
        FROM all_ips i 
        WHERE i.ip NOT IN (
                  SELECT ipa FROM UNNEST(t.ip_array) ipa
        )
    ) as other_ips
from 
    tbl_campaign_ipmapping t
WHERE advertiser='advertiser1'

или присоединение к таблице всех возможных IP-адресов (вы также можете рассмотреть возможность материализации all_ips), например

 WITH tbl_campaign_ipmapping AS
(
  SELECT 'advertiser1' as advertiser, 'campaign1' as campaign,  ['10.0.0.0','20.0.0.0','30.0.0.0', '40.0.0.0'] AS ip_array UNION ALL
  SELECT 'advertiser1' as advertiser, 'campaign2' as campaign, ['10.0.0.0', '20.0.0.0', '50.0.0.0']  UNION ALL 
  SELECT 'advertiser1' as advertiser, 'campaign3' as campaign, ['10.0.0.0', '40.0.0.0', '60.0.0.0', '70.0.0.0', '80.0.0.0']  UNION ALL 
  SELECT 'advertiser1' as advertiser, 'campaign4' as campaign, ['10.0.0.0', '20.0.0.0', '30.0.0.0'] UNION ALL 
  
  SELECT 'advertiser2' , 'campaign1' ,  ['10.1.1.1','20.1.1.1','30.1.1.1', '40.1.1.1']  UNION ALL
  SELECT 'advertiser2' , 'campaign2' , ['10.1.1.1', '20.1.1.1', '50.1.1.1']  UNION ALL 
  SELECT 'advertiser2' , 'campaign3' , ['10.1.1.1', '40.1.1.1', '60.1.1.1', '70.1.1.1', '80.1.1.1']  UNION ALL 
  SELECT 'advertiser2', 'campaign4' , ['10.1.1.1', '20.1.1.1', '30.1.1.1']  
  
),
all_ips AS (
    SELECT DISTINCT ip 
    FROM tbl_campaign_ipmapping, unnest(ip_array) ip
    WHERE advertiser='advertiser1'
)
select 
    advertiser,
    campaign,
    ARRAY_AGG(i.ip) as other_ips
from 
    tbl_campaign_ipmapping t
inner join 
    all_ips i on i.ip NOT IN UNNEST(t.ip_array)
WHERE advertiser='advertiser1'
group by 
    advertiser,campaign

Дайте мне знать, если это сработает для вас

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

1. Спасибо. Это работает, но я упростил проблему, есть дополнительный рекламодатель столбца, и нам нужно фильтровать только для конкретного рекламодателя. Я изменил логику с помощью того же, но столкнулся с «Коррелированными подзапросами, которые ссылаются на другие таблицы, не поддерживаются, если их нельзя отменить, например, преобразовав их в эффективное ОБЪЕДИНЕНИЕ».

2. Пожалуйста, обновите вопрос с вашими образцами данных и попыткой запроса, которая выдает это сообщение об ошибке

3. @ShivakumarGokaram Я обновил ответ, чтобы он соответствовал вашему образцу и опубликованным результатам. Дайте мне знать, если это сработает для вас.

4. Спасибо. Образец прекрасно работает с обоими предоставленными вами вариантами. Я расширил то же самое еще несколькими столбцами,чтобы имитировать мою таблицу I, e, добавил еще 2 поля, ключ и поле даты, и все хорошо. Проблема, с которой я сталкиваюсь, заключается в

5. Когда я пытаюсь выполнить один и тот же запрос к моим фактическим таблицам, то для первого метода я получаю «Коррелированные подзапросы, ссылающиеся на другие таблицы, не поддерживаются, если они не могут быть отменены». Я пытаюсь найти различия в определении таблицы, которое я предоставил в приведенном выше примере, с моими исходными таблицами. Единственное, что у меня есть, это количество строк около 600 , а разные IP-адреса огромны, около 70 тыс. Поэтому я не уверен, что оптимизатор запросов Google BQ решает эту проблему по-другому.

Ответ №3:

Рассмотрим следующий подход

 with advertiser_ips as (
  select advertiser, array_agg(distinct ip) ip_array 
  from tbl_campaign_ipmapping, unnest(ip_array) ip
  group by advertiser
)
select advertiser, campaign,
  array( select distinct ip
    from b.ip_array ip
    where not ip in unnest(a.ip_array) 
  ) as missing_ip
from tbl_campaign_ipmapping a
join advertiser_ips b
using(advertiser)

если применить к образцам данных в вашем вопросе — вывод будет

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

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

1. спасибо вам за альтернативный подход. При размере таблицы более 600 строк и общем количестве различных IP-адресов 70 Тыс. Для этого требуется время. Мне пришлось отменить запрос, так как он превысил 2 минуты.

2. Мне просто нужно количество IP-адресов для каждой строки, которая не найдена. Мне даже не нужен список IP-адресов, например: рекламодатель1, кампания 1, 4, который представляет собой общее количество элементов в массиве

3. @ShivakumarGokaram как насчет покрытия массива внутри функции array_length? Подобный этому array_length(array( select distinct ip from b.ip_array ip where not ip in unnest(a.ip_array) )) as missing_ip

4. Длина массива в порядке, и это сработает. Проблема в том, что время для запроса, так как у меня много IP-адресов, это соединение занимает очень много времени

Ответ №4:

Попробуй Это. Не уверен, что это оптимизированный способ, но я пробовал использовать операторы НАБОРА

   WITH tbl_campaign_ipmapping AS
(
  SELECT 'advertiser1' as advertiser, 'campaign1' as campaign,  ['10.0.0.0','20.0.0.0','30.0.0.0', '40.0.0.0'] AS ip_array UNION ALL
  SELECT 'advertiser1' as advertiser, 'campaign2' as campaign, ['10.0.0.0', '20.0.0.0', '50.0.0.0']  UNION ALL 
  SELECT 'advertiser1' as advertiser, 'campaign3' as campaign, ['10.0.0.0', '40.0.0.0', '60.0.0.0', '70.0.0.0', '80.0.0.0']  UNION ALL 
  SELECT 'advertiser1' as advertiser, 'campaign4' as campaign, ['10.0.0.0', '20.0.0.0', '30.0.0.0'] UNION ALL 
  
  SELECT 'advertiser2' , 'campaign1' ,  ['10.1.1.1','20.1.1.1','30.1.1.1', '40.1.1.1']  UNION ALL
  SELECT 'advertiser2' , 'campaign2' , ['10.1.1.1', '20.1.1.1', '50.1.1.1']  UNION ALL 
  SELECT 'advertiser2' , 'campaign3' , ['10.1.1.1', '40.1.1.1', '60.1.1.1', '70.1.1.1', '80.1.1.1']  UNION ALL 
  SELECT 'advertiser2', 'campaign4' , ['10.1.1.1', '20.1.1.1', '30.1.1.1']  
  
),
advertiser_all_ip as
(
select advertiser ,array_concat_agg(ip_array ) as all_ips
 from tbl_campaign_ipmapping
 group by advertiser 
),cte as (
select tci.advertiser ,campaign ,ip_array  , all_ips
from tbl_campaign_ipmapping tci
join advertiser_all_ip aaip
on tci.advertiser  = aaip.advertiser
)
select advertiser ,campaign,array_length(array_agg(arr))  as distinct_count
from (
    select advertiser ,campaign,arr  from cte,unnest(all_ips) as arr
    except distinct 
    select advertiser ,campaign,arr  from cte,unnest(ip_array) as arr
)
group by advertiser ,campaign

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

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

1. Спасибо вам за запрос. Но из-за больших IP-адресов это работает не очень хорошо.

Метки: