google-analytics - BigQuery:识别交易之前的所有匹配

Question

假设以下BQ查询：

SELECT * FROM (
SELECT CONCAT(CAST(fullVisitorId AS String), CAST(visitId AS STRING)) AS CustVisitId, hits.hitnumber AS HitNr, hits.transaction.transactionRevenue/1000000 AS Rev
FROM
  `[projectid].[dataset].ga_sessions_*` AS t, t.hits AS hits
WHERE
  _TABLE_SUFFIX BETWEEN '20190120' AND '20190120'
ORDER BY HitNr)
WHERE CustVisitId = '49889154628941922861547956449'

这将产生如下表：

CustVisitId HitNr   Rev
123456789   1   
123456789   2   
123456789   3   
123456789   4       8
123456789   5   
123456789   6   
123456789   7   
123456789   8   
123456789   9       40
123456789   10  

现在，我想添加一列（在下面的示例中为“ New”）来标识交易之前（包括交易）的所有匹配，如下所示：

CustVisitId HitNr   Rev   New
123456789   1             1
123456789   2             1
123456789   3             1
123456789   4       8     1
123456789   5             2
123456789   6             2
123456789   7             2
123456789   8             2
123456789   9       40    2
123456789   10            3

有谁知道如何调整查询以便生成所需的表？

Answer 1

以下是BigQuery标准SQL

#standardSQL
SELECT *,
  1 + (COUNT(Rev) OVER(PARTITION BY CustVisitId ORDER BY HitNr ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)) `New`
FROM (
SELECT CONCAT(CAST(fullVisitorId AS STRING), CAST(visitId AS STRING)) AS CustVisitId, hits.hitnumber AS HitNr, hits.transaction.transactionRevenue/1000000 AS Rev
FROM
  `[projectid].[dataset].ga_sessions_*` AS t, t.hits AS hits
WHERE
  _TABLE_SUFFIX BETWEEN '20190120' AND '20190120'
ORDER BY HitNr)
WHERE CustVisitId = '49889154628941922861547956449'      

如您所见，我保持查询不变，只是为 New添加了一行