Блог

Главная - Вопросы по программированию - Могу ли я объединить данные в SQL с помощью предложения like или contains, чтобы исключить некоторые значения?

Могу ли я объединить данные в SQL с помощью предложения like или contains, чтобы исключить некоторые значения?

#sql #google-analytics #google-bigquery

#sql #google-analytics #google-bigquery

Вопрос:

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

В моем списке может быть что-то вроде:
sitename.com

Однако мне нужно сопоставить это со столбцом eventLabel в BigQuery, где URL-адрес может вернуться как:
http://sitename.com/subpage/extra-subpage

Я не могу сделать Not In, так как для этого требуется прямое совпадение, я попытался использовать оператор like, однако я получаю следующую ошибку

 Scalar subquery produced more than one element

Я не совсем уверен, как еще поступить, и мне интересно, нужно ли мне выполнить запрос, в котором указано, соответствует ли строка (поскольку я могу заставить ее работать, если я использую внутреннее соединение, а затем использую эту новую таблицу для выполнения исключений, поскольку я могу сохранить eventLabel, а затем выполнить свою NotНа основе этого? 

 SELECT Distinct
h.eventinfo.eventAction  eventAction,
h.eventinfo.eventlabel eventlabel

FROM `projectName.ga_sessions_*`, unnest(Hits) h 


 WHere 
 _TABLE_SUFFIX BETWEEN "20190101" AND  FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

and type = 'EVENT'
 and h.eventInfo.eventCategory = 'EventName'

and Replace(Replace(Replace(h.eventInfo.eventLabel,'http://',''),'https://',''),'www.','') 
                                                Not like (select concat(ThirdPartyURL,'%') from `projectName.datasetName.ExclusionList`)

Я надеюсь, что вышесказанное имеет смысл.

TIA.

Ответ №1:

После воспроизведения вашей проблемы решение заключается в использовании NOT IN вместо NOT LIKE следующего:

 WITH `projectName.datasetName.ExclusionList` AS
  (SELECT 'label1' AS ThirdPartyURL UNION ALL
   SELECT 'label2')

SELECT DISTINCT h.eventinfo.eventAction eventAction,
                h.eventinfo.eventlabel eventlabel
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`,
     unnest(Hits) h
WHERE _TABLE_SUFFIX BETWEEN "20170801" AND "20170802"
  AND TYPE = 'EVENT'
  AND h.eventInfo.eventCategory = 'EventName'
  AND Replace(Replace(Replace(h.eventInfo.eventLabel, 'http://', ''), 'https://', ''), 'www.', '') 
  NOT IN
        (SELECT ThirdPartyURL FROM `projectName.datasetName.ExclusionList`)

Это ссылка на документацию по SQL, связанную с BigQuery

Комментарии:

1. Использование Not In мне не поможет (или, по крайней мере, ничего не делает для меня, поскольку я все равно получаю одинаковое количество строк данных) Возможно, я не был ясен в приведенном выше сообщении. Например, мне нужно исключить любые URL-адреса, которые имеют facebook.com но это могут быть реальные ссылки на страницы, и так может быть facebook.com/somewebpage facebook.com/anotherwebpage и т.д. итак, мне нужно что-то, что позволяет мне просто сопоставлять частичную строку и игнорировать остальную часть URL. Отредактировано: нажал отправить слишком рано.

2. Это означает, что помимо использования NOT IN вам нужно выполнить еще некоторые манипуляции с меткой, которую вы хотите исключить. Итак, в вашем примере я бы использовал разделение на точки и взял первое значение в массиве, которое будет Facebook. Очевидно, вам может понадобиться более сложное регулярное выражение или разделение, которое будет охватывать другие случаи. Комбинация этих двух изменений поможет вам решить вашу проблему