hive sql（网友1）—— 用户记录去重，两次记录间隔超过90天算新的记录总数及平均年龄

用户记录去重，两次记录间隔超过90天算新的记录——此案例已征求网友同意

create table wangyou(
    uid string,
    time string
);

insert into wangyou values
("1","2021-01-01"),
("1","2021-02-03"),
("1","2021-04-02"),
("1","2021-05-07"),
("1","2021-07-02"),
("1","2021-08-15");

select
    t2.uid,
    min(t2.time),
    t2.flag
from
    (select
        t1.uid,
        t1.time,
        t1.diff_day,
        case
            when t1.diff_day>90 and t1.diff_day <=180 then 1
            when t1.diff_day>180 and t1.diff_day <=270 then 2
            when t1.diff_day>270 and t1.diff_day <=360 then 3
            else 0
        end flag
    from(select
            uid,
            time,
            datediff(time,min(time)over(partition by uid order by time)) as diff_day
        from
            wangyou
        )t1
    )t2
group by t2.uid,t2.flag
;

#网友要求的结果
uid  time  与当前满足条件的日期比较  间隔  是否满足条件
1  2021/1/1      1
1  2021/2/3  2021/1/1  33  
1  2021/4/2  2021/1/1  91  1
1  2021/5/7  2021/4/2  35  
1  2021/7/2  2021/4/2  91  1
1  2021/8/15  2021/7/2  44  

#实现语句的结果
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 5.82 sec   HDFS Read: 9756 HDFS Write: 189 SUCCESS
Stage-Stage-2: Map: 1  Reduce: 1   Cumulative CPU: 2.19 sec   HDFS Read: 6053 HDFS Write: 45 SUCCESS
Total MapReduce CPU Time Spent: 8 seconds 10 msec
OK
1  2021-01-01  0
1  2021-04-02  1
1  2021-07-02  2
Time taken: 60.922 seconds, Fetched: 3 row(s)

#网友提供的计算逻辑
1.每个用户有多条记录，将每个用户最早的一个日期作为参数A，每条记录都与最小值相减，得到间隔天数；
2.如果间隔天数<=90，则参数A仍然是最早日期。如果>90，则将参数A更新为第一条大于90天的记录所在的天数；
如此循环，最终返回最早一条记录，以及间隔天数大于90的记录。例如左边的例子，最终uid=1去重后保留2021-01-01，2021-04-02，2021-07-02

#实现语句的分析
思路：
1、先按uid开窗排序，并且取出日期最小的一条数据，作为参考
2、计算每条数据的日期与参考日期的差值
3、判断差值所以哪个范围，这样会得到同一个范围的有多个flag
4、对flag去重，每个flag组里日期最小的数据就是去重之后要保留的结果

重点：
数据是有特征的，符合一定逻辑。本次的逻辑在于先将数据分段，90天一周期就是数据分类的特征。

1、本次默认数据的范围是360天内，如果是数据间隔时间比较长怎么处理；一是通常认为间隔时间特别久的数据不会再分析，因为离线每次按批处理，如此，历史数据都处理过的，只需要一段时间处理一次；二是如果对历史数据处理，数据量必然很大，也会采用按时间切割处理；三是可以多加几个case when将时间拉长，这种做法成本最小，最简洁；四是如果想实现动态的方式，可以使用udf函数
2、类似这种数据更新去重，找到数据分段逻辑，巧用flag去重，之前写过的连续登录的也是类似的逻辑，建议对比看一下

1、min(col)：某组数据最小，可以作用于group by，也可以作用于over
2、case when：一个case when完整的语法包含case when then else end，如上；也可以简洁使用case when then end，如果不符合条件则返回null
3、datediff(日期1，日期2):返回日期1减日期2的差值