如何在BigQuery中编写SQL从GA4提取指定URL的数据？

我需要在BigQuery中从GA4提取仅包含特定URL的数据，已编写如下SQL代码，但不知如何添加指定URL的过滤条件。指定URL包括："https://www.creatve.id/"、"https://www.creatve.id/jasa-pasang-atap-alderon/"、"https://www.creatve.id/jasa-pasang-kanopi/"。
现有SQL代码：

SELECT 

  user_pseudo_id,  
  PARSE_DATE('%Y%m%d', event_date) as Date,
  event_name,
  regexp_replace((select value.string_value from unnest(event_params) where key = 'page_location'), r'\?.*', '') as page_location,
  geo.region as Province,
  geo.city as City

 FROM `ga4-web-stream.analytics_396005669.events_intraday_*`

方法一：使用HAVING子句直接过滤 #

因为page_location是你在查询中通过regexp_replace计算生成的派生字段，BigQuery允许用HAVING子句直接对这类字段进行过滤，写法如下：

SELECT 
  user_pseudo_id,  
  PARSE_DATE('%Y%m%d', event_date) as Date,
  event_name,
  regexp_replace((select value.string_value from unnest(event_params) where key = 'page_location'), r'\?.*', '') as page_location,
  geo.region as Province,
  geo.city as City
FROM `ga4-web-stream.analytics_396005669.events_intraday_*`
HAVING page_location IN (
  'https://www.creatve.id/',
  'https://www.creatve.id/jasa-pasang-atap-alderon/',
  'https://www.creatve.id/jasa-pasang-kanopi/'
)

方法二：使用子查询预处理后过滤 #

如果数据集较大，把子查询作为预处理步骤再过滤，性能可能更优：

SELECT 
  user_pseudo_id,
  Date,
  event_name,
  page_location,
  Province,
  City
FROM (
  SELECT 
    user_pseudo_id,  
    PARSE_DATE('%Y%m%d', event_date) as Date,
    event_name,
    regexp_replace((select value.string_value from unnest(event_params) where key = 'page_location'), r'\?.*', '') as page_location,
    geo.region as Province,
    geo.city as City
  FROM `ga4-web-stream.analytics_396005669.events_intraday_*`
)
WHERE page_location IN (
  'https://www.creatve.id/',
  'https://www.creatve.id/jasa-pasang-atap-alderon/',
  'https://www.creatve.id/jasa-pasang-kanopi/'
)

补充说明 #

• 你已经用regexp_replace去掉了URL的查询参数（?后面的内容），所以过滤时直接匹配处理后的完整地址即可，确保不会因为参数不同漏掉目标页面。
• 如果需要匹配这些URL下的所有子页面（比如包含对应路径的任意页面），可以改用REGEXP_CONTAINS，示例：

  HAVING REGEXP_CONTAINS(page_location, r'^https://www.creatve.id/(jasa-pasang-atap-alderon|jasa-pasang-kanopi)?/?')
  

内容的提问来源于stack exchange，提问作者Ricky