如何实施"LIKE ANY"在BigQuery(标准SQL)中? [英] How to implement "LIKE ANY" in BigQuery (standard SQL)?

问题描述

我想使用像任何东西一样"的功能.但是，BigQuery无法识别"ANY"运算符.有什么功能可以代替"LIKE ANY"吗?

I would like to use "like any" function. However, BigQuery doesnot recognize "ANY" operator. Is there any function which can replace "LIKE ANY" ?

Bigquery-标准SQL

Bigquery - Standard 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

Syntax error: Unexpected keyword ANY at [12:47]

推荐答案

具有相同问题的简单查询:

Simpler query with the same problem:

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:替换为一系列喜欢或"

Alternative 1: Replace with a series of LIKE OR

SELECT x
FROM TABLE
WHERE (x LIKE '%/damesmode/%' 
OR x LIKE '%/herenkleding/%'
OR x LIKE '%/kinderkleding/%'
OR x LIKE'%/sportkleding/%')

XXXX/herenkleding/XXX

替代2:组合成一个正则表达式

Alternative 2: Combine into one regular expression

WITH table AS (
  SELECT 'XXXX/herenkleding/XXX' x
)

SELECT x
FROM TABLE
WHERE REGEXP_CONTAINS(x
  , '/(damesmode|herenkleding|kinderkleding|sportkleding)/')

XXXX/herenkleding/XXX

这篇关于如何实施"LIKE ANY"在BigQuery(标准SQL)中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！