BiqQuery - Как найти перекрывающиеся уникальные идентификаторы для нескольких категорий в столбце?

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

У меня есть столбец страны и столбец идентификатора. Пример показан ниже:

Страна | ID
США | id_1
США | id_2
США | id_1
Великобритания | id_1
Великобритания | id_1
Великобритания | id_2
Великобритания | id_3
AUS | id_3
AUS | id_4
AUS | id_2

Мне нужен результирующий столбец, например:

Результат 1: УНИКАЛЬНЫЕ значения ПЕРЕКЛЮЧЕНИЯ ID во всех странах

Страна | Уникальные_наложенные_идентификаторы
США | 2
Великобритания | 3
AUS | 2

Результат 2: УНИКАЛЬНЫЕ значения идентификаторов без ПЕРЕКЛЮЧЕНИЯ во всех странах

Страна | Non_Unique_overlapping_ids
США | 0
Великобритания | 0
Австралия | 1

У меня 88 разных стран и более 5 миллионов уникальных идентификаторов

Помогите, пожалуйста. Спасибо за ваше время и терпение.


google-bigquery distinct-values overlapping
person Pavithra G    schedule 10.12.2020    source источник
comment
Добро пожаловать, у вас есть пример запроса, который вы уже пробовали?   -  person Ben P    schedule 10.12.2020
comment
пожалуйста, объясните логику ожидаемых результатов, просто UNIQUE OVERLAPPING и UNIQUE non-OVERLAPPING не объясняют всего - так как это можно интерпретировать по-разному   -  person Mikhail Berlyant    schedule 10.12.2020
comment
@MikhailBerlyant UNIQUE OVERLAPPING будет подсчитывать уникальные идентификаторы для Country = USA, который повторяется во всех других значениях Country, а также сам. Итак, если вы посмотрите на основную таблицу, мы увидим, что у США есть 2 уникальных идентификатора, которые появляются в других странах. Та же логика применима к Country = UK, Country = AUS. Таблица UNIQUE non-OVERLAPPING вернет те уникальные IDS для Country = USA OR Country = UK или Country = AUS, которые никогда не появлялись в других значениях страны, кроме самой себя. Имеет ли это смысл? Извините, это очень сбивает с толку, но да.   -  person Pavithra G    schedule 11.12.2020
comment
это не соответствует представленному output1 ... так объясните, почему в output1 usa есть 2, а не 1 (только id_2 используется всеми странами - поэтому я ожидал бы увидеть 1, а не 2)   -  person Mikhail Berlyant    schedule 11.12.2020
comment
@BenP У меня пока ничего нет, так как я не понимаю, как к этому подойти.   -  person Pavithra G    schedule 11.12.2020
comment
@MikhailBerlyant поправьте! приятное наблюдение ... поэтому, если Country = USA имеет одно или несколько перекрывающихся значений в других странах, мы классифицируем их по выходу 1. Поскольку id_2 используется как в США, так и в Великобритании, мы объединяем их в перекрывающиеся значения ...   -  person Pavithra G    schedule 11.12.2020
comment
Итак, уточните - должно ли это быть 1 или 2 для США на основе приведенного вами примера? в вашем вопросе - вы используете формулировку across all the countries, что, я думаю, сбивает с толку. так это across all the countries или across at least one other country?   -  person Mikhail Berlyant    schedule 11.12.2020
comment
@MikhailBerlyant извините за непонятность. Когда Country = USA, id_1 используется в одной или нескольких странах, кроме США, мы классифицируем id_1 как перекрывающееся значение для США. Когда Country = USA, id_2 используется в одной или нескольких странах, кроме США, мы классифицируем id_2 как перекрывающееся значение для США. Итак, у США есть 2 пересекающихся значения с другими странами.   -  person Pavithra G    schedule 11.12.2020
comment
@MikhailBerlyant Это должно быть хотя бы в одной другой стране! Извините, что недостаточно ясно.   -  person Pavithra G    schedule 11.12.2020
comment
Конечно. понятно. теперь это имеет гораздо больше смысла: o)   -  person Mikhail Berlyant    schedule 11.12.2020
comment
@MikhailBerlyant Большое спасибо !!! ^ _ ^   -  person Pavithra G    schedule 11.12.2020

Ответы (1)


arrow_upward
1
arrow_downward

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

#standardSQL
select country, 
  count(distinct if(shared, id, null)) as Unique_overlapping_ids,
  count(distinct if(shared, null, id)) as Unique_non_overlapping_ids
from `project.dataset.table` 
join (
  select id, count(distinct country) > 1 shared
  from `project.dataset.table`
  group by id
) using(id)
group by country

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

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

person Mikhail Berlyant    schedule 10.12.2020