我正在嘗試計算僅在看到“產品/搜索/詳細資訊”頁面后才開始的 AddToCart 指標的運行總計。
這是 SQL Fiddle 的鏈接：http ://sqlfiddle.com/#!15/bbf9b/1
在 sqlfiddle 鏈接中，我手動創建了一個列來反映我想要的輸出。作業輸出列顯示了我的代碼到哪里。
SUM(AddToCart) OVER (PARTITION BY SessionID ORDER BY HitNumber ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as workingoutput
我知道下面的語法都是錯誤的，但這基本上是我想要實作的
SUM(AddToCart) OVER (PARTITION BY SessionID ORDER BY HitNumber ROWS BETWEEN UNBOUNDED PRECEDING AND FIRST_VALUE(ROW LIKE "%/product/search/details%")) as workingoutput
uj5u.com熱心網友回復：
你需要在這里嵌套你的視窗函式
- 從一個運行的條件計數開始，檢查我們是否已經到達
/product/search/details，然后僅AddToCart根據該計數回傳 - 對那個結果做一個總和
SELECT
wd.SessionID,
wd.HitNumber,
wd.HitType,
wd.EventType,
wd.PageName,
wd.AddToCart,
SUM(wd.AddToCartFromSearch) OVER (PARTITION BY wd.SessionID
ORDER BY HitNumber ROWS UNBOUNDED PRECEDING) AS DesiredOutput
FROM (
SELECT *,
CASE WHEN COUNT(CASE WHEN wd.PageName = '/product/search/details' THEN 1 END)
OVER (PARTITION BY wd.SessionID ORDER BY HitNumber ROWS UNBOUNDED PRECEDING) > 0
THEN AddToCart ELSE 0 END AS AddToCartFromSearch
FROM WebData wd
) wd
ORDER BY HitNumber;
SQL小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/313273.html
標籤：sql 查询语句 谷歌-bigquery