sql解决连续登录问题变形-节假日过滤

比眉伴天荒 2023-10-04 18:59 10阅读 0赞

#### 内容目录 ####

*   *   *  一、说明
         *  二、需求说明
         *  三、过滤节假日
         *   *  思路一
             *  思路二
         *  四、功能实现
         *   *  方式1：等差求解
             *  方式2：前后相减

#### 一、说明 ####

> 先说需要实现的效果，就是判断节假日和工作日，如果是工作日，返回当日，如果是节假日或者休息，返回一下次工作的日期  
> 06-01 工作日 06-01  
> 06-02 双休日 06-04 \*因为 下一个工作日是06-04  
> 06-03 双休日 06-04  
> 06-07、06-08、06-09节假日， 返回06-10， 即下一个工作日

连续登录天数之前讲解过有两种解法，可以解决连续登录问题，那么在实际生产中也是经常使用到，但是对于一些此类问题的变形，是否能够轻松解决？

#### 二、需求说明 ####

最近接到一个需求，也不算是需求，是一个小的功能实现。之前一直再做事件告警的需求，需要对告警事件进行反馈，判断是否是当日反馈。  
最开始实现时候想法很简单，只考虑到了双休日，就设定如果是双休日的告警事件，在周一时候反馈就认为是当日反馈，当时是这么实现的：  
如果是周六，反馈日期增加2，如果是周日，反馈日期增加1

case dayofweek(alarm_date)
    	when 7 then date_add(fb_date,2)
    	when 0 then date_add(fb_date,1)
    else fb_date end as fb_date

但是在实际使用中，发现了存在节假日的卡点，尤其是上半年的节假日比较多，所以不过滤节假日对结果有很大的影响，另外，对于一些双休日，也可能是工作日串休。

#### 三、过滤节假日 ####

为什么会说这个问题是连续登录问题的变形呢？我们想一想，节假日是不是连续的？无论是双休还是节假日，都是连续的日期。那么这个问题就变成了，我们要分辨出哪些是节假日？哪些是工作日？  
这种实现的思路我想到的有两个

##### 思路一 #####

编写UDF，在jar包中维护一个文件，文件可以记录节假日的日期，写一个自定义的UDF实现这个功能  
这个方法可以实现，但是过程会有一些繁琐，但是好处是可以复用性强，一次编写，到处使用

##### 思路二 #####

维护一张节假日的维表，通过一些sql逻辑实现，优点是实现过程不繁琐，缺点是复用性不强。对于缺点，其实可以把结果输出到一张维表中使用，同时还可以增加一些其他维度，因为生产环境中类似这种时间节假日的维度使用的还挺多。

#### 四、功能实现 ####

采用思路二实现，维护一张维表，维表中记录了日期是否为节假日，实现方式也有两种

##### 方式1：等差求解 #####

回忆连续登录问题的解法，求等差，找到连续的天数，按照正序排序，返回连续天数的下一天，就是需要的结果  
流程图如下：  
![在这里插入图片描述][9b8d0fdd999449989ade7ab810998584.png]

<table> 
 <thead> 
  <tr> 
   <th>日期</th> 
   <th>是否节假日</th> 
   <th>返回值</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>06-01</td> 
   <td>1</td> 
   <td>06-03</td> 
  </tr> 
  <tr> 
   <td>06-02</td> 
   <td>1</td> 
   <td>06-03</td> 
  </tr> 
  <tr> 
   <td>06-03</td> 
   <td>0</td> 
   <td>06-03</td> 
  </tr> 
  <tr> 
   <td>06-04</td> 
   <td>1</td> 
   <td>06-05</td> 
  </tr> 
  <tr> 
   <td>06-05</td> 
   <td>0</td> 
   <td>06-05</td> 
  </tr> 
 </tbody> 
</table>

1	select 
    2	  log_date, 
    3	  date_add(log_date, rn) next_work_day 
    4	from ( 
    5	select 
    6	  log_date, 
    7	  row_number() 
    8	  over(partition by start_day order by log_date desc) rn 
    9	from ( 
    10	select 
    11	  log_date, 
    12	  date_sub(log_date, rn) start_day 
    13	from( 
    14	select 
    15	  log_date, 
    16	  row_number() 
    17	  over(order by log_date) rn 
    18	from ( 
    19	select 
    20	  log_date 
    21	from 
    22	  tmp_bdp.tmp_log_date 
    23	) t1 
    24	) t2 
    25	) t3 
    26	) t4

##### 方式2：前后相减 #####

也是模拟连续登录的方式，找到连续天数的下一天，就是要返回的值

1	select 
    2	  log_date, 
    3	  max(log_date) 
    4	  over(partition by flag order by log_date) next_day 
    5	from ( 
    6		select 
    7		  log_date, 
    8		  sum(if(diff_date > 1, 1, 0)) 
    9		  over(order by log_date) flag 
    10		from ( 
    11			select  
    12			  log_date, 
    13			  datediff(log_date, lag_date) diff_date 
    14			from ( 
    15				select 
    16				  `date` as log_date, 
    17				  lag(`date`, 1, '1970-01-01')  
    18				  over(order by `date`) lag_date 
    19				from 
    20				  bili_dim.dim_date_info_d 
    21				where year(`date`) = 2022 and holiday_type <> 0 
    22			) t1 
    23		) t2 
    24	) t3

[9b8d0fdd999449989ade7ab810998584.png]: https://img-blog.csdnimg.cn/9b8d0fdd999449989ade7ab810998584.png