Использование SPLIT_PART в PostgreSQL

У меня есть столбец данных, который извлекает тег UTM из Google Рекламы с указанными ниже идентификаторами. Он содержит идентификаторы кампании (начальная часть перед "___"), а затем идентификатор группы объявлений. В некоторых случаях у нас есть только идентификаторы кампании, которые являются строками, поэтому я выполняю приведение типов с помощью :: TEXT.

Вот как выглядят UTM-теги при втягивании.

835783587___42385125483
eu
968720083___47551372269
en_usa_search_brand
648594695___38174608372
886097479___45386492795
en_trust_control
competitors
es
en_esp_search_route
1072851000___55370810634

Я пытаюсь отделить идентификаторы друг от друга и удалить подчеркивания, а затем перенести их в другую таблицу.

umc.campaign - это столбец, содержащий тег UTM.

Я создаю эту временную таблицу, чтобы затем перейти к последней таблице ниже.

 CREATE TABLE reports.tmp_sem_attribution  AS (
        SELECT DISTINCT ON (umc.user_id)
            umc.user_id,
            umc.source,
            umc.campaign ::TEXT,
    (SPLIT_PART(REPLACE(campaign,'__','_'),'__',1)) :: TEXT AS campaign_id,
    (SPLIT_PART(REPLACE(campaign,'__','_'),'__',2)) :: TEXT AS adgroup_id,

Когда я использую приведенный ниже запрос для проверки результатов, я вижу, что некоторые идентификаторы группы объявлений пусты или содержат пробелы.

reports.sem_attribution_v2 - это таблица, в которой я помещаю идентификаторы в два разных столбца.

SELECT * FROM reports.sem_attribution_v2 WHERE adgroup_id =''

**RESULT**
Campaign_ID                     AdGroup ID
eu  
1560591282  
en_usa_search_brand 
1560608121  
en_trust_control    
1560591282  
en_fra_search_generic_manual    
990427417   
eu

Если бы вы, ребята, могли пролить свет на то, как я могу подойти к этому по-другому или этот запрос неверен. Это было бы очень признательно.

Спасибо.

Можете ли вы предоставить исходный текст для идентификаторов кампании: 1560608121, 1560591282 и 990427417?

— 17.12.2018 14:38

@Adam Для этих трех идентификаторов исходный текст точно такой же, как и выше, он содержит только идентификатор кампании без скобок или идентификаторов группы объявлений.

— 17.12.2018 15:13

sql postgresql

17.12.2018 14:06

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

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

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

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

4 951

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

Ответы 2

Функция SPLIT_PART вернет пустую строку, если после разделения будет меньше полей, чем было запрошено. Например: когда есть только одно поле, и вы хотите получить второе поле, вы получите пустую строку. Что правильно и хорошо для вашего подхода.

Вы можете упростить свой запрос, потому что часть REPLACE не нужна:

(SPLIT_PART(campaign, '___', 1))::TEXT AS campaign_id,
(SPLIT_PART(campaign, '___', 2))::TEXT AS adgroup_id

Еще одно улучшение могло заключаться в замене пустых строк значениями NULL. Вы можете сделать это при вставке данных в таблицу reports.sem_attribution_v2:

CASE WHEN adgroup_id = '' THEN NULL ELSE adgroup_id END

Спасибо за помощь Адам

— 18.12.2018 14:38

17.12.2018 15:29

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

Вы можете использовать REGEXP_REPLACE

SELECT   REGEXP_REPLACE(campaign,'(\d+)___\d+','\1') as campaign_id,
         REGEXP_REPLACE(campaign,'\d+___(\d+)','\1') as adgroup_id
                 FROM t;

ИЛИ SUSBTRING с условием корпуса.

SELECT CASE 
         WHEN campaign ~ '(\d+)___(\d+)' THEN 
         substring(campaign FROM '(\d+)___')  --extracts string before "__"
         ELSE campaign                        --same string when pattern not found
       end AS campaign_id, 
       CASE 
         WHEN campaign ~ '(\d+)___(\d+)' THEN 
         substring(campaign FROM '___(\d+)')   --extracts string after "__"
         ELSE campaign 
       end AS adgroup_id 
FROM   t;

Демо

Спасибо, Kaushik, инструкция REGEXP_REPLACE выполнила свою работу.

— 18.12.2018 14:37

17.12.2018 15:37