Как в Google BigQuery разделить значение столбца по символу

Часто в UTM-метках используется символ вертикальной черты (|), который разделяет параметры визуально. Например, нередко мы можем встретить такое оформление UTM рекламной кампании и ее идентификатора: 

В результате при передаче таких данных в базу два важных параметра попадают в один столбец, что затрудняет их дальнейший анализ. Возникает вопрос: как их “физически” разделить, если работа с данными ведется в Google BigQuery?

В качестве решения можно использовать следующий код, написанный на Legacy SQL. 

В примере ниже column1 - это название столбца в вашей базе данных, где содержатся данные, которые необходимо разделить по символу разделителя. 

SELECT

  CASE

    WHEN INSTR(REGEXP_REPLACE(column1, r'%7C', '|'),"|") = 0 THEN column1

    ELSE subSTRING(column1,

    1,

    INSTR(REGEXP_REPLACE(column, r'%7C', '|'),"|")-1)

  END AS CampaignName,

  CASE

    WHEN INSTR(REGEXP_REPLACE(column, r'%7C', '|'),"|") = 0 THEN ""

    ELSE subSTRING(REGEXP_REPLACE(column, r'%7C', '|'),

    INSTR(REGEXP_REPLACE(gcolumn, r'%7C', '|'),"|")+1)

  END AS CampaignId

FROM

WHERE

GROUP BY

  CampaignName,

  CampaignID 

Вы получите два столбца: CampaignName и CampaignId. В первый попадет текст до символа-разделителя, а во второй - идентификатор кампании. 

Вы можете заметить, что в коде используется регулярное выражение, которое также производит разделение столбцов по символу %7C, который является эквивалентом | в URL-кодировании.

Разделителем может быть любой символ (запятая, точка, дефис, нижнее подчеркивание, слэш, любая буква, цифра, символ или их набор). Чтобы предложенный выше сценарий реализовать с другим разделителем, замените в коде вертикальную черту (|) на ваш символ-разделитель. 

Удачи!

Узнайте больше про сквозную и предиктивную аналитику. Посмотрите выпуск с руководителем отдела аналитики MediaNation Александром Вахтиным и аналитиком больших данных Романом Святовым: