BiqQuery — Как найти уникальные идентификаторы, перекрывающиеся для нескольких категорий в столбце?
Я новичок в BigQuery и стандартном SQL. Возможно, я не смогу найти правильный подход к решению проблемы. Пожалуйста, помогите мне.
У меня есть столбец страны и столбец идентификатора. Пример показан ниже:
Страна | ID
США | id_1
США | id_2
США | id_1
Великобритания | id_1
Великобритания | id_1
Великобритания | id_2
Великобритания | id_3
Австралия | id_3
Австралия | id_4
Австралия | id_2
Мне нужен результирующий столбец, например:
Выход 1: УНИКАЛЬНЫЕ ПЕРЕКРЫВАЮЩИЕСЯ значения ID во всех странах
Страна | Unique_overlapping_ids
США | 2
Великобритания | 3
Австралия | 2
Выход 2: УНИКАЛЬНЫЕ НЕПЕРЕСЕКАЮЩИЕСЯ значения ID во всех странах
Страна | Non_Unique_overlapping_ids
США | 0
Великобритания | 0
Австралия | 1
У меня есть 88 различных стран и более 5 миллионов уникальных идентификаторов.
Пожалуйста, помогите мне. Спасибо за ваше время и терпение.
пожалуйста, объясните логику ожидаемых результатов, просто UNIQUE OVERLAPPING и UNIQUE non-OVERLAPPING не объясняют всего, так как это можно интерпретировать по-разному
@MikhailBerlyant UNIQUE OVERLAPPING будет подсчитывать уникальные идентификаторы для Country = USA, которые повторяются во всех других значениях Country, а также в самом себе. Итак, если вы посмотрите на основную таблицу, мы увидим, что у США есть 2 уникальных идентификатора, которые появляются в других странах. Та же логика применима к Country = UK , Country = AUS. Таблица UNIQUE non-OVERLAPPING вернет те уникальные IDS для Country = USA OR Country = UK или Country = AUS, которые никогда не появлялись в других значениях страны, кроме самой себя. Имеет ли это смысл? Извините, это очень запутанно, но да.
это не соответствует представленному output1 ... поэтому объясните, почему в output1 usa есть 2, а не 1 (только id_2 является общим для всех стран, поэтому я ожидал увидеть 1, а не 2)
@BenP У меня пока ничего нет, так как я не понимаю, как к этому подойти.
@MikhailBerlyant правильно! хорошее наблюдение ... поэтому, если Country = USA имеет одно или несколько перекрывающихся значений в других странах, мы классифицируем группу в выход 1. Поскольку id_2 является общим для США и Великобритании, мы объединяем их в перекрывающееся значение...
Итак, уточните - должно ли это быть 1 или 2 для США на основе приведенного вами примера? в вашем вопросе - вы используете формулировку across all the countries, которая, я думаю, сбивает с толку. так это across all the countries или across at least one other country?
@MikhailBerlyant извините за неясность. Когда Country = USA, id_1 используется совместно с 1 или несколькими странами, кроме самих США, мы классифицируем id_1 как перекрывающееся значение для США. Когда Country = USA, id_2 используется совместно с 1 или несколькими странами, кроме самих США, мы классифицируем id_2 как перекрывающееся значение для США. Итак, у США есть 2 пересекающихся значения с другими странами.
@MikhailBerlyant Это должно быть хотя бы в одной стране! Извините, что недостаточно ясно выразился.
конечно. понятно. теперь это имеет больше смысла :о)
@MikhailBerlyant Спасибо большое!!! ^_^
Ответы 1
Ниже приведен стандартный 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
если применить к выборке данных из вашего вопроса - вывод
Добро пожаловать, у вас есть пример запроса, который вы пробовали до сих пор?