连续登录hiveSQL实现

窗口和分析函数的应用

• 需求：统计连续N天登陆人数
  table
  logindate userid
  2019-08-28 00:03:00 tom
  2019-08-28 10:00:00 frank
  2019-08-28 11:00:00 jack
  2019-08-29 00:03:00 tom
  2019-08-29 10:00:00 frank
  2019-08-30 10:00:00 tom
  2019-08-30 12:00:00 jack
  • 统计连续登陆2天的人数：tom、frank

    • 第一种方案：使用笛卡尔积来做：date_add(date1,1) = date2
      select
      a.longindate as longina,
      a.userid as userida,
      b.longindate as longinb,
      b.userid as useridb
      from table a join table b;–没有给定关联字段就产生笛卡尔积

      结果是多少条：a*b = 49条
      longina				userida		longinb				useridb
      2019-08-28 00:03:00 tom			2019-08-28 00:03:00 tom
      2019-08-28 00:03:00 tom			2019-08-28 10:00:00 frank
      2019-08-28 00:03:00 tom			2019-08-28 11:00:00 jack
      2019-08-28 00:03:00 tom			2019-08-29 00:03:00 tom
      ……
      a表中的 每一条会与b表的每一条Join一下
      select
      *
      from rs
      where userida = useridb and date_add(date(longina),1) = date(longinb)
      
      2019-08-28 00:03:00 tom			2019-08-29 00:03:00 tom
      

      • 工作中不能使用这样的 方案
        • 数据量多大的时候，笛卡尔积非常大，导致性能非常差
        • 只能实现连续两天的登录统计

    • 第二种方案：窗口函数来做
      logindate userid
      2019-08-28 tom
      2019-08-28 frank
      2019-08-28 jack
      2019-08-29 tom
      2019-08-29 frank
      2019-08-30 tom
      2019-08-30 jack

      • lead来实现
        select
        longindate,
        userid,
        date_add(longindate,1) as tomorrow,–取当前登录日期的后一天
        lead(longindate,1,0) over (partition by userid order by longindate) as nextLogin
        from table;
        logindate userid tomorrow nextLogin
        2019-08-28 tom 2019-08-29 2019-08-29
        2019-08-29 tom 2019-08-30 2019-08-30
        2019-08-30 tom 2019-08-31 0
        2019-08-28 frank 2019-08-29 2019-08-29
        2019-08-29 frank 2019-08-30 0
        2019-08-28 jack 2019-08-29 2019-08-30
        2019-08-30 jack 2019-08-31 0
        • 哪些人连续登陆两天：tomorrow = nextLogin
  • 统计连续登陆3天的人数：tom

    • 如果使用笛卡尔积来做
      2019-08-28 11:00:00 jack 2019-08-30 12:00:00 jack

      select
      *
      from rs
      where userida = useridb and date_add(date(longina),2) = date(longinb);
      

    • 使用窗口来实现连续三天登录
      select
      longindate,
      userid,
      date_add(longindate,2) as tomorrow,–取当前登录日期的后两天
      lead(longindate,2,0) over (partition by userid order by longindate) as nextLogin
      from table;
      logindate userid tomorrow nextLogin
      2019-08-28 tom 2019-08-30 2019-08-30
      2019-08-29 tom 2019-08-31 0
      2019-08-30 tom 2019-09-01 0
      2019-08-28 frank 2019-08-30 0
      2019-08-29 frank 2019-08-31 0
      2019-08-28 jack 2019-08-30 0
      2019-08-30 jack 2019-09-01 0

      • 哪些人连续登陆三天：tomorrow = nextLogin