Блог

У меня есть реляционный набор данных в Bigquery, содержащий две таблицы.

Первая таблица содержит данные клиента

  ------------- -------- 
| Customer ID | Name   |
 ------------- -------- 
| 1           | Bob    |
 ------------- -------- 
| 2           | Jenny  |
 ------------- -------- 
| 3           | Janice |
 ------------- -------- 
  

Вторая таблица содержит различные пары имя / значение, связанные с клиентом в первой таблице:

  ------------- ---------- ------- 
| Customer ID | Category | Value |
 ------------- ---------- ------- 
| 1           | A        | A     |
 ------------- ---------- ------- 
| 1           | A        | B     |
 ------------- ---------- ------- 
| 1           | B        | A     |
 ------------- ---------- ------- 
| 2           | B        | B     |
 ------------- ---------- ------- 
  

Я хотел бы сгенерировать отчет, в котором перечисляется каждый клиент, и устанавливает значение TRUE под каждым именем: значение, где оно находится в таблице 2, например:

  ------------- ------ ------ ----- ------ ------ 
| Customer ID | A:A  | A:B  | A:C | B:A  | B:B  |
 ------------- ------ ------ ----- ------ ------ 
| 1           | TRUE | TRUE |     | TRUE |      |
 ------------- ------ ------ ----- ------ ------ 
| 2           |      |      |     |      | TRUE |
 ------------- ------ ------ ----- ------ ------ 
| 3           |      |      |     |      |      |
 ------------- ------ ------ ----- ------ ------ 
  

Я попытался указать каждую из комбинаций category:value в виде столбцов в моем операторе select

 select
  customer id,
  a:a, 
  a:b, 
  a:c, 
  b:a,
  b:b 
from 
  table_1 t1
join 
  table_2 t2 
on
  t1.customer_id = t2.customer_id  

  

Но это мне ничего не дает, потому что я не знаю, как заставить запрос установить для ячейки значение TRUE, как только значение найдено.

Прошу прощения, если это очевидно, я новичок в SQL.

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

 #standardSQL
SELECT customer_id,
  LOGICAL_OR((category, value) = ('A', 'A')) AS a_a,
  LOGICAL_OR((category, value) = ('A', 'B')) AS a_b,
  LOGICAL_OR((category, value) = ('A', 'C')) AS a_c,
  LOGICAL_OR((category, value) = ('B', 'A')) AS b_a,
  LOGICAL_OR((category, value) = ('B', 'B')) AS b_b
FROM `project.dataset.table1`  
JOIN `project.dataset.table2`
USING (customer_id)
GROUP BY customer_id   
  

Вы можете протестировать, поиграть с приведенным выше примером данных из вашего вопроса, как в примере ниже

 #standardSQL
WITH `project.dataset.table1` AS (
  SELECT 1 Customer_ID, 'Bob' Name UNION ALL
  SELECT 2, 'Jenny' UNION ALL
  SELECT 3, 'Janice' 
), `project.dataset.table2` AS (
  SELECT 1 Customer_ID, 'A' Category, 'A' Value UNION ALL
  SELECT 1, 'A', 'B' UNION ALL
  SELECT 1, 'B', 'A' UNION ALL
  SELECT 2, 'B', 'B' 
)
SELECT customer_id,
  LOGICAL_OR((category, value) = ('A', 'A')) AS a_a,
  LOGICAL_OR((category, value) = ('A', 'B')) AS a_b,
  LOGICAL_OR((category, value) = ('A', 'C')) AS a_c,
  LOGICAL_OR((category, value) = ('B', 'A')) AS b_a,
  LOGICAL_OR((category, value) = ('B', 'B')) AS b_b
FROM `project.dataset.table1`  
JOIN `project.dataset.table2`
USING (customer_id)
GROUP BY customer_id   
  

с результатом

 Row customer_id a_a     a_b     a_c     b_a     b_b  
1   1           true    true    false   true    false    
2   2           false   false   false   false   true      
  

В случае, если вам нужен / нужен вывод точно так же, как в вашем вопросе — вы можете использовать приведенную ниже скорректированную версию

 #standardSQL
SELECT customer_id,
  IF(LOGICAL_OR((category, value) = ('A', 'A')), 'TRUE', '') AS a_a,
  IF(LOGICAL_OR((category, value) = ('A', 'B')), 'TRUE', '') AS a_b,
  IF(LOGICAL_OR((category, value) = ('A', 'C')), 'TRUE', '') AS a_c,
  IF(LOGICAL_OR((category, value) = ('B', 'A')), 'TRUE', '') AS b_a,
  IF(LOGICAL_OR((category, value) = ('B', 'B')), 'TRUE', '') AS b_b
FROM `project.dataset.table1`  
JOIN `project.dataset.table2`
USING (customer_id)
GROUP BY customer_id
  

с результатом

 Row customer_id a_a     a_b     a_c     b_a     b_b  
1   1           TRUE    TRUE            TRUE         
2   2                                           TRUE     
  

Примечание: в приведенных выше примерах — вам действительно не нужны объединения, поскольку вы не используете поля из table1, а не в качестве фильтра (для представления только пользователей из table1)

Вам нужна какая-то агрегация, например:

 select t1.customer_id,
       bool_or(t2.category = 'a' and t2.value = 'a') as a_a,
       bool_or(t2.category = 'a' and t2.value = 'b') as a_b,
       bool_or(t2.category = 'a' and t2.value = 'c') as a_c,
       bool_or(t2.category = 'b' and t2.value = 'a') as b_a,
       bool_or(t2.category = 'b' and t2.value = 'b') as b_b
from table_1 t1 join
     table_2 t2 
     on t1.customer_id = t2.customer_id  
group by t1.customer_id;