Сравните два идентификатора / строки в BigQuery

Думаю, это довольно простой запрос, но я просто не могу заставить его работать. Решений пока не найдено.

У меня есть две таблицы с разными данными. Обе таблицы содержат идентификатор в виде строки, называемой clientID.

Таблица A имеет идентификатор, а также различные события и пользовательские измерения из всех сеансов Google Analytics.

SELECT 
clientId,
visitNumber,
hits.page.pagePath AS pagePath,
trafficSource.source AS source,

--Events
SUM(CASE WHEN (hits.eventInfo.eventCategory = 'Download' AND hits.type = 
'EVENT' AND hits.eventInfo.eventAction = 'pdf') THEN 1 ELSE 0 END) AS 
Downloads,
SUM(CASE WHEN (hits.eventInfo.eventLabel  = 'search-header' OR 
hits.eventInfo.eventLabel  = 'search-mainpage') AND hits.type = 'EVENT' THEN 
1 ELSE 0 END) AS Search,

--CustomDimension
(SELECT MAX(cd.value) FROM UNNEST(hits.customDimensions) AS cd WHERE 
cd.index = 1) AS realURL

FROM `xxxx.xxxx.ga_sessions_*` AS t, UNNEST(hits) AS hits

WHERE 
hits.page.pagePath LIKE 'news-and-media' AND _TABLE_SUFFIX BETWEEN 
 FORMAT_DATE('%Y%m%d', date '2018-05-31') AND FORMAT_DATE('%Y%m%d', date 
'2018-10-01')
GROUP BY 
clientId,
visitNumber,
pagePath,
source,
realURL

LIMIT 100;

Таблица B имеет только один столбец с некоторыми clientID. Поэтому я хочу сопоставить / найти clientIDs в таблице A для clientIDs, которые находятся в таблице B. Я хочу только данные из таблицы А, где идентификаторы совпадают.

Я использую Стандартный SQL здесь. Есть мысли о том, как решить эту проблему? Думаю, я должен использовать JOIN здесь, но не имеет опыта с этим.

Источник

2 ответа

Как вы предложили, JOIN поможет вам достичь вашей цели.

Позвольте мне использовать таблицы tableA а также tableB ниже для примера:

$ bq query --use_legacy_sql=false 'SELECT * FROM `dataset.tableA`'
+----------+------------+-----------+
| clientID | clientName | clientNum |
+----------+------------+-----------+
| id1      | aaa        |       111 |
| id1      | bbb        |       222 |
| id2      | ccc        |       333 |
| id3      | ddd        |       444 |
| id4      | eee        |       555 |
| id5      | fff        |       666 |
+----------+------------+-----------+

$ bq query --use_legacy_sql=false 'SELECT * FROM `dataset.tableB`'
+----------+
| clientID |
+----------+
| id1      |
| id4      |
| id5      |
+----------+

Теперь вы можете использовать операцию JOIN в BigQuery для извлечения только строк из tableA где колонна clientID соответствует этим значениям в том же столбце в tableB:

SELECT
  tA.clientID,
  tA.clientName,
  tA.clientNum
FROM
  `dataset.tableA` AS tA
JOIN
  `dataset.tableB` AS tB
ON
  tA.clientID = tB.clientID

Этот запрос предоставляет следующие результаты:

$ bq query --use_legacy_sql=false 'SELECT tA.clientID, tA.clientName, tA.clientNum FROM `dataset.tableA` AS tA JOIN `dataset.tableB` AS tB ON tA.clientID = tB.clientID'
+----------+------------+-----------+
| clientID | clientName | clientNum |
+----------+------------+-----------+
| id1      | aaa        |       111 |
| id1      | bbb        |       222 |
| id4      | eee        |       555 |
| id5      | fff        |       666 |
+----------+------------+-----------+

В вашем случае вы можете присоединиться к clientId после извлечения из xxxx.xxxx.ga_sessions_* или получить результаты этого запроса, а затем присоединиться к ним в clientId,

Учитывая, что вы начинаете работать с JOIN в BigQuery, позвольте мне поделиться с вами несколькими ссылками на документацию, которые также могут быть полезны:

Источник

Это должно быть очень просто, используя предложение IN (только что протестировано, IN поддерживается в bigquery).

select * from tableA where ID in (select ID from tableB)
Источник
Другие вопросы по тегам