sql - 在配置单元 SQL 中聚合点击流数据(组中的第一个值)CTE SQL

Question

我正在使用 Apache Hive SQL 分析来自不同网站的网站数据，我想找出客户旅程中涉及的域。点击流数据具有以下结构:

id1 domain
--- ---
1    1
1    1
1    1
1    3
1    1
1    2
3    4
3    5
3    5
6    7
6    6
6    7

为了以正确的方式分析数据，我想将数据转换成这种结构:

id1 domain
--- ---
1    1
1    3
1    1
1    2
3    4
3    5
6    7
6    6
6    7

这怎么能在 HIVE Sql 中完成？

我必须找出哪个接触点涉及哪个序列/顺序，并消除域之间的冗余点击。最终这个洞查询应该导致这样的表:

1: 1>3>1>2
3: 4>5
6: 7>6>7

我不会说这是一个特定于 hive 的问题!

Answer 1

在 sql 中，我们可以对 hive 使用 cte 我认为我们必须将 varchar(100) 更改为 string 我怀疑相关子查询在 hive 中是否有效。

;WITH list (id1 , domain, [level], list)
     AS (
    SELECT id1, domain, 1, CAST(domain AS varchar(100)) AS list
    FROM Hivetable
    WHERE domain IS NOT NULL

    UNION ALL

    SELECT list.id1, conn.domain, list.[level]+1,
          CAST(list+' <- '+CAST(conn.domain AS varchar(100)) AS varchar(100)) AS list
    FROM list
    INNER JOIN Hivetable AS conn ON list.domain=conn.id1
    WHERE conn.domain IS NOT NULL)

SELECT id1, list AS blockedByChain
FROM list
WHERE [level]=(SELECT MAX(sub.[level]) FROM list AS sub WHERE sub.id1=list.id1)
ORDER BY id1, [level], domain;