1. 求每个用户最大的连续登陆天数,断一天还算连续。(如果是绝对连续,使用一次等差思想即可)

      id dt
      1001 2021-11-05
      1001 2021-11-06
      1001 2021-11-08
      1001 2021-11-10
      1001 2021-11-15
      1001 2021-11-16
      1001 2021-11-18
      1001 2021-11-19
      1002 2021-11-05
      1002 2021-11-07
      1002 2021-11-09
      1002 2021-11-11
      1002 2021-11-13
      1002 2021-11-16
      方案一：等差数列思想
      整体思路：利用等差数列思想，相差一天等差为1，对数据进行rank()排序、开窗，得到一列排序的值，使用日期减去排序的值，日期相差为一天的在运算后和第一行的天数相等，这种是绝对连续，先进行一次count()，然后再进行一次rank()排序、开窗，相减，可得到差两天的的绝对连续，最后 对天数求和+所有相同日期的-1 就是断一天也连续的绝对连续的天数。

      1. --1.1 按照用户分组,按照时间排序,给定Rank值,相当于了一列
      2. select
      3.  id,
      4.  dt,
      5.  rank() over(partition by id order by dt) rk
      6. from test1;t1
      7. --结果
      8. /*
      9. id            dt                 rk
      10. 1001    2021-11-05 1
      11. 1001    2021-11-06 2
      12. 1001    2021-11-08 3
      13. 1001    2021-11-10 4
      14. 1001    2021-11-15 5
      15. 1001    2021-11-16 6
      16. 1001    2021-11-18 7
      17. 1001    2021-11-19 8
      18. */
      19. --1.2 进行平行相减操作,即dt-rk这样得到的结果中相同的dt即是像个一天的日期,date_sub
      20. select
      21.  id,
      22.  date_sub(dt,rk) dt
      23. from
      24.  t1;t2
      25. /*
      26. id            dt
      27. 1001    2021-11-04
      28. 1001    2021-11-04
      29. 1001    2021-11-05
      30. 1001    2021-11-06
      31. 1001    2021-11-10
      32. 1001    2021-11-10
      33. 1001    2021-11-11
      34. 1001    2021-11-11
      35. */
      36. --1.3 计算原始数据中绝对连续的天数,按照id、dt分组进行聚合操作,算相同id同一天的次数,即是绝对连续天数
      37. select
      38.  id,
      39.  dt,
      40.  count(*) days
      41. from
      42.  t2
      43. group by id,dt;t3
      44. /*
      45. 1001    2021-11-04  2
      46. 1001    2021-11-05  1
      47. 1001    2021-11-06  1
      48. 1001    2021-11-10  2
      49. 1001    2021-11-11  2
      50. */
      51. --1.4 再次使用等差数列思想
      52. select
      53.  id,
      54.  dt,
      55.  days,
      56.  rank() over(partition by id order by dt) rk
      57. from
      58.  t3;t4
      59. /*
      60. 1001    2021-11-04  2  1
      61. 1001    2021-11-05  1  2
      62. 1001    2021-11-06  1  3
      63. 1001    2021-11-10  2  4
      64. 1001    2021-11-11  2  5
      65. */
      66. --1.5 再次平行相减
      67. select
      68.  id,
      69.  date_sub(dt,rk) dt,
      70.  days
      71. from
      72.  t4;t5
      73. /*
      74. 1001    2021-11-03  2
      75. 1001    2021-11-03  1
      76. 1001    2021-11-03  1
      77. 1001    2021-11-06  2
      78. 1001    2021-11-06  2
      79. */
      80. --1.6 计算连续天数(考虑上断一天的情况)
      81. select
      82.  id,
      83.  dt,
      84.  (sum(days)+count(*)-1) days
      85. from
      86.  t5
      87. group by id,dt;
      88. /*
      89. 1001  | 2021-11-06  | 5     |
      90. 1002  | 2021-11-04  | 1     |
      91. 1002  | 2021-11-03  | 9     |
      92. 1001  | 2021-11-03  | 6     |
      93. */

      方案二：直接分组，可以应对所有情况，断一天、两天依旧算连续的情况。
      整体思路：通过lat函数将当前行下移一行，放到新的字段中，第一行没有的补计算机元年，然后用原日期字段减去新的字段可以得到邻近两行相距的天数，使用sumif判断天数是否大于2，如果大于2则分组加1，否则分组不变，使用同一分组中最大天数减去(最小的天数+1)，即是最后的天数。 ```sql —2.1 将上一行数据移动至当前行,根据id和dt进行开窗,使用lag函数将上一行数据移动到当前行 select id, dt, lag(dt,1,’1970-01-01’) over(partition by id order by dt) lag_dt from test1; / | 1001 | 2021-11-05 | 1970-01-01 | | 1001 | 2021-11-06 | 2021-11-05 | | 1001 | 2021-11-08 | 2021-11-06 | | 1001 | 2021-11-10 | 2021-11-08 | | 1001 | 2021-11-15 | 2021-11-10 | | 1001 | 2021-11-16 | 2021-11-15 | | 1001 | 2021-11-18 | 2021-11-16 | | 1001 | 2021-11-19 | 2021-11-18 | / —2.2 将当前行数据的日期减去上一行数据的日期 select id, dt, datediff(dt,lag_dt) dt_diff from t1;t2 /* | 1001 | 2021-11-05 | 18936 | | 1001 | 2021-11-06 | 1 | | 1001 | 2021-11-08 | 2 | | 1001 | 2021-11-10 | 2 | | 1001 | 2021-11-15 | 5 | | 1001 | 2021-11-16 | 1 | | 1001 | 2021-11-18 | 2 | | 1001 | 2021-11-19 | 1 |

    / —2.3 分组,间隔小于等于2的数据划分到一个组,每遇到一个大于2的数据,分组+1 select id, dt, sum(if(dt_diff>2,1,0)) over(partition by id order by dt) g_id from t2;t3 / | 1001 | 2021-11-05 | 1 | | 1001 | 2021-11-06 | 1 | | 1001 | 2021-11-08 | 1 | | 1001 | 2021-11-10 | 1 | | 1001 | 2021-11-15 | 2 | | 1001 | 2021-11-16 | 2 | | 1001 | 2021-11-18 | 2 | | 1001 | 2021-11-19 | 2 | / —2.4 计算连续的登陆天数 select id, g_id, (datediff(max(dt),min(dt))+1) days from t3 group by id,g_id; / | 1001 | 1 | 6 | | 1001 | 2 | 5 | | 1002 | 1 | 9 | | 1002 | 2 | 1 | */ ``` 注：在使用方案二时不能使用spark引擎，汇报错误：Error: Error while processing statement: FAILED: Execution Error, return code 3 from org.apache.hadoop.hive.ql.exec.spark.SparkTask. Spark job failed during runtime. Please check stacktrace for the root cause. (state=42000,code=3)；原因：Spark引擎+datediff函数(在子查询且开窗使用)+时间字段使用的是String类型 空指针
    解决：使用MR引擎或修改时间字段为Date类型。