#sql #google-bigquery
#sql #google-bigquery
Вопрос:
Есть ли способ сделать это предложение более компактным?
SELECT campaign
,event_list
FROM `adobe_analytics.raw_data_20200*`
WHERE campaign IS NOT NULL -- What does it mean when a campaign is null in Adobe_analytics
AND
(
"1" IN UNNEST(split(event_list,","))
OR "2" IN UNNEST(split(event_list,","))
OR "3" IN UNNEST(split(event_list,","))
)
Что-то вроде :
SELECT campaign
,event_list
FROM `adobe_analytics.raw_data_20200*`
WHERE campaign IS NOT NULL
AND ("1" OR "2" OR "3") IN UNNEST(split(event_list,","))
Ответ №1:
Я бы использовал exists
:
SELECT campaign, event_list
FROM `adobe_analytics.raw_data_20200*` rd
WHERE campaign IS NOT NULL AND
EXISTS (SELECT 1
FROM UNNEST(SPLIT(rd.event_list)) el
WHERE el IN ('1', '2', '3')
);
Ответ №2:
Ниже приведен стандартный SQL BigQuery
SELECT campaign ,event_list
FROM `adobe_analytics.raw_data_20200*`
WHERE campaign IS NOT NULL
AND (
SELECT COUNT(1) FROM UNNEST(SPLIT(event_list,",")) e
JOIN UNNEST(SPLIT("1,2,3",",")) e USING(e)
) > 0