Hive连续系列1

现有数据如下

image.png

需求：
求连续出现三次的数字number

方法一：
根据肉眼可以看出连续出现三次的number是1和3，那么用SQL如何实现呢？
第一种SQL实现如下

SELECT DISTINCT(a.number)
FROM safety.da_series_df a 
JOIN safety.da_series_df b 
ON a.id=b.id-1
JOIN safety.da_series_df c
ON b.id=c.id-1
WHERE a.number=b.number
and b.number=c.number

得到结果：

image.png

这种方式比较死板，当我们要求连续5次、10次的时候就很麻烦。

方法二：
使用Hive的窗口函数lag

SELECT
    id,
    number,
    lag(id,2)  OVER(partition BY number order by id) pr
    FROM 
    safety.da_series_df

得到结果如下

image.png

分析得到连续出现的数字的id等于pr+2，所以

SELECT 
*
FROM
(
    SELECT
    id,
    number,
    lag(id,2)  OVER(partition BY number order by id) pr
    FROM 
    safety.da_series_df
) t 
WHERE t.id=t.pr+2

得到结果

image.png