HIVE连续日期的统计

前文：

SELECT
id,
COLLECT_set (appdate) flag---- 竖横转换
FROM table
GROUP BY
id

------ 统计连续 日期的脚本

1，代码

SELECT
ID, MAX(activiteday) - MIN(activiteday) diff, COLLECT_set (activiteday)
FROM
(
SELECT a.ID, a.activiteday, (activiteday - rn)as num
FROM
(
SELECT ID , activiteday,row_number () over (PARTITION BY ID ORDER BY activiteday) rn
from (
SELECT ID, date_format(date1,‘yyyyMMdd’)activiteday— 本身本地日期是yy-mm-dd 格式，这里要转换哟
FROM table )t group by ID ,activiteday
) a
) a2
GROUP BY ID, num

2， 通过后期加工可以得到自己需要统计的数据。