解决办法是

SELECT * FROM (
SELECT id
       ,domain
       ,LAG(domain) OVER (PARTITION BY id ORDER BY date) AS lag_domain
FROM t_domain
) t1
WHERE lag_domain NOT LIKE domain

在sql中，我们可以在hive中使用cte，我想我们必须将varchar（100）改为string，我怀疑corelated子查询在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;