分区BY不适用于多个和语句

Question

我有一个4列的表: hitId、userId、timestamp和坎普。如果一个命中是一个新会话的开始(1或0)，我需要使用两个参数来分类: 1.命中之间的时间差；2.如果命中的来源是新的战役。

我需要BigQuery中的标准SQL查询。

如果下列情况之一为真，则将命中视为新会话的开始：

1. 这是它的userId的第一次成功
2. 来自同一userId的上一次命中的时间戳之间的时间差超过30分钟。
3. 来自同一userId的上一次命中的时间戳之间的时间差小于30分钟，但坎普(广告)值不是为零，而是在前30分钟内首次发生在相同的userId上。

因此，如果hit1 user1的营地等于Campaign1，而user1的hit2是等于Campaign1的，hit1和hit2之间的时间差小于30分钟，hit1将被视为会话的开始，hit2将不被视为开始。

我在竞选部分遇到麻烦了。我试过这个代码：

我试过这个代码：

WITH timeDifference AS (
  SELECT *, 
  TIMESTAMP_DIFF(timestamp, LAG(timestamp, 1) OVER
          (PARTITION BY userId ORDER BY timestamp), SECOND) AS difference
    FROM hitTable
      ORDER BY timestamp)
SELECT *, 
  CASE 
  WHEN difference >= 30 * 60 THEN 1 
  WHEN difference IS NULL THEN 1 
  WHEN difference <= 30 * 60 AND Camp IS NOT NULL AND RANK() 
  OVER (PARTITION BY userId ORDER BY Camp) = 1 THEN 1
  ELSE 0 END AS sess
  FROM timeDifference
  ORDER BY timestamp;

条件RANK() OVER (PARTITION BY userId ORDER BY Camp)似乎不起作用，因为我收到了这个表：

hitId | userId |  timestamp   |  Camp           |  difference  |  sess
_______________________________________________________________________
00150 | 858201 | 00:48:35.315 |  NULL           |  NULL        |  1
00151 | 858201 | 00:49:35.315 |  NULL           |  5           |  0
00152 | 858201 | 00:50:35.315 |  Search-Ads-US  |  10          |  0
00153 | 858201 | 00:53:35.315 |  Search-Ads-US  |  15          |  0
00154 | 858202 | 00:54:35.315 |  Facebook-Ads   |  NULL        |  1
00155 | 858202 | 00:54:55.315 |  Facebook-Ads   |  9           |  0
00156 | 858202 | 00:57:20.315 |  Facebook-Ads   |  12          |  0

虽然我希望sess列的hitId =00152有1：

hitId | userId |  timestamp   |  Camp           |  difference  |  sess
_______________________________________________________________________
00150 | 858201 | 00:48:35.315 |  NULL           |  NULL        |  1
00151 | 858201 | 00:49:35.315 |  NULL           |  5           |  0
00152 | 858201 | 00:50:35.315 |  Search-Ads-US  |  10          |  1
00153 | 858201 | 00:53:35.315 |  Search-Ads-US  |  15          |  0
00154 | 858202 | 00:54:35.315 |  Facebook-Ads   |  NULL        |  1
00155 | 858202 | 00:54:55.315 |  Facebook-Ads   |  9           |  0
00156 | 858202 | 00:57:20.315 |  Facebook-Ads   |  12          |  0

Answer 1

在用户有多个营地的情况下，这个级别() OVER (按userId命令分区)返回错误。

注意您的分区使用userId，同时您希望在每个阵营中标记会话。

级别()(.)的实际“1”对于userId 00150的语句是NULL (hitId 00150)，因此在hitId 00152中它错过了您的CASE条件。

您可以尝试按如下方式在分区中添加“坎普”：秩() OVER (按userId划分，按坎普排序)

或者，您可以替换级别()(.)和使用延迟(营)(.除滞后(时间戳)(.)你在算计。这将检索前一行的Camp值(称为“PreviousCampValue”)。然后您可以添加类似于WHEN PreviousCampValue != Camp然后1的内容。

希望这会有帮助