Оценка строки через запятую в BigQuery

Я использую BigQuery и пытаюсь проанализировать строку с разделителями-запятыми, чтобы найти в ней определенные числа.

Примерная таблица, как показано ниже

|--------|-----------------------------------------------------------|
| userID | sequence                                                  |
|--------|-----------------------------------------------------------|
| 123abc | 1,2,3,4,5,6,7,8                                           |                                          
|--------|-----------------------------------------------------------|
| 456bcd | 1,2,3,4,5,6,7,8,9,10,11                                   |
|--------|-----------------------------------------------------------|
| 789def | 1,2,3,4                                                   |
|--------|-----------------------------------------------------------|

Мне нужно создать оператор CASE, в котором каждое значение строки «последовательность» оценивается в соответствии со следующей логикой, а результат выводится в отдельный столбец.

SELECT userID
,sequence
,CASE WHEN sequence CONTAINS '1' THEN 1 ELSE 0 END AS action1 
,CASE WHEN sequence CONTAINS '2' THEN 1 ELSE 0 END AS action2 
,CASE WHEN sequence CONTAINS '3' THEN 1 ELSE 0 END as action3
....
,CASE WHEN sequence CONTAINS '9' AND '11' THEN 1 ELSE 0 END as action10

Это даст следующий результат.

|--------|-------------------------|-------|-------|-------|---------|
| userID | sequence                |action1|action2|action3|action10 |
|--------|-------------------------|-------|-------|-------|---------|
| 123abc | 1,2,3,4,5,6,7,8         |   1   |   1   |   1   |    0    |                                          
|--------|-------------------------|-------|-------|-------|---------|
| 456bcd | 1,2,3,4,5,6,7,8,9,10,11 |   1   |   1   |   1   |    1    |
|--------|-------------------------|-------|-------|-------|---------|
| 789def | 1,2                     |   1   |   1   |   0   |    0    |
|--------|-------------------------|-------|-------|-------|---------|

Пожалуйста, не последний оператор CASE WHEN очень важен, поскольку мне нужно учитывать эту очень специфическую комбинацию строковых значений как его собственное уникальное действие.

Я считаю, что это можно было бы достичь в SQL Server, используя что-то вроде:

CASE WHEN CHARINDEX('1', 'sequence')>0 THEN 1 ELSE 0 END as action1
,CASE WHEN CHARINDEX('2', 'sequence')>0 THEN 1 ELSE 0 END as action2
,CASE WHEN CHARINDEX('3', 'sequence')>0 THEN 1 ELSE 0 END as action3
...
,CASE WHEN CHARINDEX('9', 'sequence')>0 AND CHARINDEX('11', 'sequence')>0 THEN 1 ELSE 0 END as action10

Однако я не могу найти в BigQuery эквивалентную функцию, которая дала бы такой же результат, и мои попытки REGEX также потерпели неудачу.

Я был бы очень благодарен за руководство здесь. Заранее спасибо.

sql regex string google-bigquery

03.10.2018 20:20

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

1 624

Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

см. направление ниже (для BigQuery Standard SQL)

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '123abc' userID, '1,2,3,4,5,6,7,8' sequence UNION ALL
  SELECT '456bcd', '1,2,3,4,5,6,7,8,9,10,11' UNION ALL
  SELECT '789def', '1,2' 
)
SELECT userID, 
  IF((SELECT COUNT(1) FROM UNNEST(SPLIT(sequence)) value WHERE value = '1' ) > 0, 1, 0)  action1,
  IF((SELECT COUNT(1) FROM UNNEST(SPLIT(sequence)) value WHERE value = '2' ) > 0, 1, 0)  action2,
  IF((SELECT COUNT(1) FROM UNNEST(SPLIT(sequence)) value WHERE value = '3' ) > 0, 1, 0)  action3,
  IF((SELECT COUNT(1) FROM UNNEST(SPLIT(sequence)) value WHERE value = '4' ) > 0, 1, 0)  action4,
  IF((SELECT COUNT(1) FROM UNNEST(SPLIT(sequence)) value WHERE value = '5' ) > 0, 1, 0)  action5,
  IF((SELECT COUNT(1) FROM UNNEST(SPLIT(sequence)) value WHERE value = '6' ) > 0, 1, 0)  action6,
  IF((SELECT COUNT(1) FROM UNNEST(SPLIT(sequence)) value WHERE value = '7' ) > 0, 1, 0)  action7,
  IF((SELECT COUNT(1) FROM UNNEST(SPLIT(sequence)) value WHERE value = '8' ) > 0, 1, 0)  action8,
  IF((SELECT COUNT(1) FROM UNNEST(SPLIT(sequence)) value WHERE value = '9' ) > 0, 1, 0)  action9,
  IF((SELECT COUNT(1) FROM UNNEST(SPLIT(sequence)) value WHERE value IN ('10', '11' )) > 0, 1, 0)  action10
FROM `project.dataset.table`
-- ORDER BY userID

это даст вам что-то вроде ниже

Row userID  action1 action2 action3 action4 action5 action6 action7 action8 action9 action10     
1   123abc  1       1       1       1       1       1       1       1       0       0    
2   456bcd  1       1       1       1       1       1       1       1       1       1    
3   789def  1       1       0       0       0       0       0       0       0       0

он упрощен - но дает вам some guidance, как вы просили: o)

См. Ниже идею рефакторинга (который обычно является бесконечным процессом), поэтому он, по крайней мере, менее подробен.

#standardSQL
WITH `project.dataset.table` AS (
  SELECT '123abc' userID, '1,2,3,4,5,6,7,8' sequence UNION ALL
  SELECT '456bcd', '1,2,3,4,5,6,7,8,9,10,11' UNION ALL
  SELECT '789def', '1,2' 
)
SELECT userID, 
  IF('1' IN UNNEST(SPLIT(sequence)), 1, 0) AS action1,
  IF('2' IN UNNEST(SPLIT(sequence)), 1, 0) AS action2,
  IF('3' IN UNNEST(SPLIT(sequence)), 1, 0) AS action3,
  IF('4' IN UNNEST(SPLIT(sequence)), 1, 0) AS action4,
  IF('5' IN UNNEST(SPLIT(sequence)), 1, 0) AS action5,
  IF('6' IN UNNEST(SPLIT(sequence)), 1, 0) AS action6,
  IF('7' IN UNNEST(SPLIT(sequence)), 1, 0) AS action7,
  IF('8' IN UNNEST(SPLIT(sequence)), 1, 0) AS action8,
  IF('9' IN UNNEST(SPLIT(sequence)), 1, 0) AS action9,
  IF((SELECT COUNT(1) FROM UNNEST(SPLIT(sequence)) value WHERE value IN ('10', '11' )) > 0, 1, 0)  action10
FROM `project.dataset.table`

Update to address your comments about UNION ALL

Выше используются фиктивные данные из вашего вопроса, чтобы вы могли протестировать, поиграть с ними - а пока решение на самом деле

#standardSQL
SELECT userID, 
  IF('1' IN UNNEST(SPLIT(sequence)), 1, 0) AS action1,
  IF('2' IN UNNEST(SPLIT(sequence)), 1, 0) AS action2,
  IF('3' IN UNNEST(SPLIT(sequence)), 1, 0) AS action3,
  IF('4' IN UNNEST(SPLIT(sequence)), 1, 0) AS action4,
  IF('5' IN UNNEST(SPLIT(sequence)), 1, 0) AS action5,
  IF('6' IN UNNEST(SPLIT(sequence)), 1, 0) AS action6,
  IF('7' IN UNNEST(SPLIT(sequence)), 1, 0) AS action7,
  IF('8' IN UNNEST(SPLIT(sequence)), 1, 0) AS action8,
  IF('9' IN UNNEST(SPLIT(sequence)), 1, 0) AS action9,
  IF((SELECT COUNT(1) FROM UNNEST(SPLIT(sequence)) value WHERE value IN ('10', '11' )) > 0, 1, 0)  action10
FROM `project.dataset.table`

Спасибо @Mikhail Berlyant, это хорошее решение, хотя и не идеальное, потому что у меня много-много строк идентификаторов пользователей, поэтому я бы предпочел использовать решение, которое не требует обширного списка UNION ALL.

— 03.10.2018 21:39

если вы ссылаетесь на UNION ALL в предложении WITH - это не часть решения, а способ использования демонстрационных данных - см. обновление через несколько секунд в моем ответе!

— 03.10.2018 21:40

См. Обновление в ответе - проверьте и дайте мне знать, если все еще неясно! - и, очевидно, вы должны использовать имена вашего проекта, набора данных и таблиц вместо project.dataset.table

— 03.10.2018 21:43

Спасибо, @Mikhail Berlyant. Это был долгий день, поэтому я неверно истолковал ваш ответ, но он имеет смысл. Мне пришлось изменить оператор CASE следующим образом: ,IF((SELECT COUNT(1) FROM UNNEST(SPLIT(sequence)) value WHERE value IN ('10') AND value NOT IN ('11'))>0, 1, 0) AS action10 и ,IF((SELECT COUNT(1) FROM UNNEST(SPLIT(sequence)) value WHERE value IN ('10') AND value IN ('11')) > 0, 1, 0) AS action11, потому что в противном случае IN('','') по сути является OR, который дает неверные результаты, но ваша логика ясна, а ваша методология совершенно понятна. Спасибо!

— 04.10.2018 12:54

03.10.2018 20:47