嘗試使用 REGEXP_SUBSTR 來提取型別/水果并對其進行分組。
資料樣本例如
www.fruits.com/type/banana?=asdasdasdwa
www.fruits.com/type/Apple?=asdasdasdwa
www.fruits.com/type/Apple?=asdasd21414
www.fruits.com/type/Orange?=asdasdasdwa
使用以下 SQL
SELECT DATE
COUNT(*) AS Sessions
,REGEXP_SUBSTR(hits.page.pagePath, '/type/?$',1, 1)
FROM
`159484249.ga_sessions_*` WITH_VISIT
LEFT JOIN UNNEST(hits) AS hits
LEFT JOIN UNNEST(hits.customdimensions) AS customdimension
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,REGEXP_SUBSTR(hits.page.pagePath, '/type/?$',1, 1)
ORDER BY DATE ASC, Sessions ASC;
期望的結果
Date | Sessions | Type
01/01/2021 | 22342 | type/apple
02/01/2021 | 1000 | type/orange
03/01/2021 | 2000 | type/banana
我上面的查詢為 Type 回傳 null
uj5u.com熱心網友回復：
您可以在下面看到我在對問題的評論中提供的正則運算式字串的用法：
這將產生您正在尋找的正確子字串
with sample_data as (
select 'www.fruits.com/type/banana?=asdasdasdwa' as url union all
select 'www.fruits.com/type/Apple?=asdasdasdwa' union all
select 'www.fruits.com/type/Apple?=asdasd21414' union all
select 'www.fruits.com/type/Orange?=asdasdasdwa' union all
select 'www.fruits.com/type/ban-a-na?=asdasdasdwa'
)
select regexp_substr(url, r'type\/\w ') original
, regexp_substr(url, r'type\/([\w|-] )?') dashses
from sample_data
這會產生以下內容
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/491485.html