SQL:如何仅在触发某些操作/条件/事件之前合并行

Question

我正在处理 BigQuery，并希望查看用户过去每个会话的行为，直到他们达到转化，用 result=1 表示。

现在，我将给出一个 1 个用户的表格示例。我还想为所有用户抽象它。请注意，visitId对于每个fullVisitorId都是唯一的。

#t1
fullVisitorId   visitId    visitNumber     page_views     result     
1               1214       4               11             0
1               112        5               9              0
1               1014       6               10             1
1               1511       7               10             0
1               1614       8               10             1

实际上，我希望查询在第一次转换后删除所有行，从而有效地给我们留下：

#t2
fullVisitorId   visitId    visitNumber     page_views     result     
1               1214       4               11             0
1               112        5               9              0
1               1014       6               10             1

如果该fullVisitorId没有转换，则保留所有行。我可以使用以下 SQL 将其聚合为 1 行：

SELECT fullVisitorId,
MAX(visitNumber)-MIN(visitNumber) AS number_visits,
SUM(page_views)/number_visits AS pages_per_s,
FROM t2
GROUP BY fullVisitorId

这会给我我想要的。

#t3_visitor_past
fullVisitorId   number_visits     pages_per_s       
1               3                 10

请记住，我希望拥有任意数量的不同fullVisitorId。

Answer 1

我在执行以下步骤中找到了解决方案：

1. 查找result=1称之为t4的所有行
2. 从上表中，运行SELECT fullVisitorId, visitId, MIN(visitNumber) FROM t4以获取第一个事件的列表，请调用此表t5
3. 执行内部联接：SELECT * FROM t1 INNER JOIN t5 ON t1.fullVisitorId = t5.fullVisitorId AND t1.visitNumber <= t5.visitNumber

对于非转换用户，过程相同。