sql - 大查询 SQL : How to find different sets of duplicates within a visit with same unique keys but different period of the visit?

Question

这篇文章的目的是什么？

在 bigquery 中，我需要使用 caveat

删除重复的行

对于访问者访问具有相同页面名称的页面，重复发生在同一次访问中。

分组不能解决问题

下面，我已尽我所能解释数据、问题、可能的解决方案和采取的措施。

我使用的是什么数据？

Adobe 数据导入 Bigquery(导入没有问题)

每一行都是按访问者的页面浏览量收集的数据。

每次访问者移动到新页面时，它都会在相同的 visit_num 和相同的 visitor_id 中计算 visit_page_num + 1。

为访问的每个页面记录页面名称。

问题:

在这张图片中，

某些 visit_page_num 在唯一 visitor_id、visit_num 上被视为唯一，但由于页面名称相同而重复。

ISSUE
visit_page_num      pagename
       1               a
       2               b
       3               c  -issue
       4               c  -issue
       5               d
       6               d
       7               d
       8               e
       9               c  -issue
      10               c  -issue
      11               c  -issue 


Solution so far with GROUP BY
visit_page_num      pagename
       1               a
       2               b
       3               c  -issue
       5               d
       8               e


  GOAL
visit_page_num      pagename
       1               a
       2               b
       3               c  -Goal
       5               d
       8               e
       9               c  -Goal

当相同页面名称上出现重复但在访问的不同时间发生时，我们如何确保不会消除后来的重复，而是将其计为不同的页面访问。

使用的查询:

SELECT visitor_id
,visit_num
,pagename
,first (visit_page_num) AS first
,ROW_NUMBER() OVER(PARTITION BY visitor_id, visit_num ORDER BY visitor_id, visit_num, pagename) AS int_var
FROM [table] 
GROUP BY visitor_id, visit_num, pagename
ORDER BY visitor_id, visit_num, first

输出:

一切都很好除了，

visitor_id = A on visit_num = 1 and pagename = c

采取的行动:

1. 我在第二步中尝试了 LEAD 和 LAG 函数以及 MIN 和 MAX 函数>>> SAME OUTPUT

2. 检查了网络，可以翻译成 BIGQUERY 的正常 SQL 函数 >> 相同的输出

3. 询问我的团队领导 >> 相同的输出

4. 5 小时的实验 >> 相同的输出

警告

不能使用 field_date 或任何时间字段或表中提到的字段以外的任何其他字段

Answer 1

尝试仅定位页面之间的转换，例如

SELECT
    visitor_id
  , visit_num
  , pagename
FROM (
    select
    *
    , lead(pagename) over(partition by visitorid order by visit_page_num)
      as nxt_page
    from table1
    ) derived
WHERE nxt_page <> pagename or nxt_page IS NULL
ORDER BY visitor_id, visit_num