我想使用“像任何人一样”的功能。但是,BigQuery无法识别“ ANY”运算符。有什么功能可以代替“ LIKE ANY”吗?
Bigquery-标准SQL
SELECT
date,
CONCAT(fullvisitorid, CAST(visitid AS string)) ord_browsers,
hits.appInfo.landingScreenName,
channelgrouping,
trafficSource.source,
trafficSource.medium,
CASE
WHEN channelgrouping = 'seo_brand' THEN 'Branded'
WHEN hits.appInfo.landingScreenName LIKE '%/article%'
OR hits.appInfo.landingScreenName LIKE '%/p/%' THEN 'ADS'
WHEN (hits.appInfo.landingScreenName LIKE ANY ('%/damesmode/%', '%/herenkleding/%', '%/kinderkleding/%', '%/sportkleding/%')) THEN 'Plist'
ELSE 'rest'
END Landing_page
FROM
`meta-geography-145406.81187684.ga_sessions_*`,
UNNEST(hits) AS hits
WHERE
(_table_suffix BETWEEN '20190201'
AND '20190207')
语法错误:[12:47]处出现意外关键字ANY
具有相同问题的更简单查询:
WITH table AS (
SELECT 'XXXX/herenkleding/XXX' x
)
SELECT x
FROM TABLE
WHERE x LIKE ANY (
'%/damesmode/%' '%/herenkleding/%', '%/kinderkleding/%', '%/sportkleding/%'
)
Syntax error: Unexpected keyword ANY at [2:6]
备选方案1:替换为一系列“喜欢或”
SELECT x
FROM TABLE
WHERE (x LIKE '%/damesmode/%'
OR x LIKE '%/herenkleding/%'
OR x LIKE '%/kinderkleding/%'
OR x LIKE'%/sportkleding/%')
XXXX/herenkleding/XXX
备选方案2:合并为一个正则表达式
WITH table AS (
SELECT 'XXXX/herenkleding/XXX' x
)
SELECT x
FROM TABLE
WHERE REGEXP_CONTAINS(x
, '/(damesmode|herenkleding|kinderkleding|sportkleding)/')
XXXX/herenkleding/XXX
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句