使用contains_substr和多个子字符串在大查询中选择“大查询”行

发布于 2025-01-28 03:15:56 字数 571 浏览 3 评论 0原文

我正在尝试在Google Big查询中不努力Google Analytics（分析）数据。我的目标是为所选的一组页面找到页面性能指标，其中我只有整个页面路径。

为此，我在where子句中使用contains_substr函数。

当我运行这样的代码时，它可以很好地工作：

WHERE
    _table_suffix BETWEEN "20210501"
    AND "20210831"
    AND  CONTAINS_SUBSTR(hits.page.PagePath, '/62150/')

但是，我对只有 / 62150 /（〜30页）的页面感兴趣。每当我尝试将更多页面添加到Where contains_substring函数时，它都无法正常工作。例如，

    _table_suffix BETWEEN "20210501"
    AND "20210831"
    AND  CONTAINS_SUBSTR(hits.page.PagePath, IN('/62150/', '/27000/'))

有人有任何建议吗？

最好的，

原文

I am trying to unnest Google Analytics data in Google Big Query. My goal is to find page performance indicators for a selected group of pages of which I only have the code not the entire PagePath.

To do this I am using the CONTAINS_SUBSTR function in the WHERE clause.

When I run the code like this, it works perfectly:

WHERE
    _table_suffix BETWEEN "20210501"
    AND "20210831"
    AND  CONTAINS_SUBSTR(hits.page.PagePath, '/62150/')

However, I am interested in more pages than just /62150/ (~30 pages). Whenever I try to add more pages to the where contains_substring function, it doesnt work. E.g.

    _table_suffix BETWEEN "20210501"
    AND "20210831"
    AND  CONTAINS_SUBSTR(hits.page.PagePath, IN('/62150/', '/27000/'))

Does anyone have any suggestions?

Best,

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

流云如水 2025-02-04 03:15:56

考虑这个而不是使用contains_substr：

SELECT * 
  FROM `your_sharded_tables_*`
 WHERE _TABLE_SUFFIX BETWEEN "20210501" AND "20210831"
   AND REGEXP_CONTAINS(hits.page.PagePath, r'\/(62150|27000)\/')

Consider this one instead of using CONTAINS_SUBSTR:

SELECT * 
  FROM `your_sharded_tables_*`
 WHERE _TABLE_SUFFIX BETWEEN "20210501" AND "20210831"
   AND REGEXP_CONTAINS(hits.page.PagePath, r'\/(62150|27000)\/')

回复收藏 0 原文

~没有更多了~