使用REGEXP_SUBSTR匹配URL子目录分组统计时返回NULL问题求解

Type字段全返回NULL的核心原因是REGEXP_SUBSTR使用的正则规则完全不符合URL结构：

• 原正则'/type/?$'中的$代表匹配字符串末尾位置，而所有目标URL中/type/后紧跟水果品类名、查询参数，/type/子串根本不在路径末尾，永远无法命中匹配
• 正则中的?是量词，仅代表前面的/出现0或1次，没有编写捕获/type/后品类值的匹配规则
• 原SQL还存在两处隐性问题：SELECT子句中DATE字段后漏写逗号会触发语法报错；无意义关联UNNEST(hits.customdimensions)会因单hit多自定义维度产生笛卡尔积，导致访问量计数虚高。

• 替换正则规则为r'/type/[^?]+'：匹配/type/开头、后续所有非?的连续字符，可精准提取/type/品类名片段，不会带入后面的查询参数；套LOWER()函数统一转小写，避免Apple/apple因大小写被拆分为不同分组
• 补全SQL语法缺失的逗号，为提取的品类字段设置别名Type
• 删除未使用的customdimensions关联逻辑，避免计数错误
• 简化GROUP BY逻辑，直接使用字段别名分组即可

SELECT 
  DATE,
  COUNT(*) AS Sessions,
  LOWER(REGEXP_SUBSTR(hits.page.pagePath, r'/type/[^?]+', 1, 1)) AS Type
FROM
  `159484249.ga_sessions_*`
  LEFT JOIN UNNEST(hits) AS hits
WHERE
  _TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)) AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
  AND (
    totals.pageviews>1 
    OR totals.pageviews IS NULL 
    OR totals.totalTransactionRevenue IS NOT NULL 
    OR totals.newVisits IS NULL 
    OR totals.timeOnSite IS NOT NULL
  )
  AND hits.page.pagePath LIKE '%/type/%' 
  AND hits.type = 'PAGE'
GROUP BY DATE, Type
ORDER BY DATE ASC, Sessions ASC;

针对提供的URL样例，正则提取结果完全符合预期：

• www.fruits.com/type/banana?=asdasdasdwa → 提取值为/type/banana
• www.fruits.com/type/Apple?=asdasdasdwa → 转小写后提取值为/type/apple
• www.fruits.com/type/Orange?=asdasdasdwa → 转小写后提取值为/type/orange

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