Hql求同一个人在不同公司任职期间社保缴纳每段开始时间和结束时间，包括任职其他公司后又回到原来公司的情况，要作为新的缴纳期间

1.问题引出于群友的实际工作需求，原提问如下

有一个人在一家公司工作了2年换另外两家公司又工作了3年然后又回到之前的公司。字段a公司，b身份证，c缴费月份。然后按照公司和身份证求在这3家公司4段社保缴费的开始时间和结束时间是多少？

2.解决思路

(1)使用lag(公司字段,1,公司字段)函数+over(partition by 身份证 order by 缴费月份 )开窗,得出新的一列公司数据company_re，用来为下一步嵌套查询比较一个人当月任职公司和上个月任职公司是否是一家。

+----------+------+---------+-------------+
| company  | idc  |   dt    | company_re  |
+----------+------+---------+-------------+
| a        | 123  | 2022-1  | a           |
| a        | 123  | 2022-2  | a           |
| c        | 123  | 2022-3  | a           |
| c        | 123  | 2022-4  | c           |
| a        | 123  | 2022-5  | c           |
| a        | 123  | 2022-6  | a           |
| a        | 123  | 2022-7  | a           |
| b        | 234  | 2022-1  | b           |
| b        | 234  | 2022-2  | b           |
+----------+------+---------+-------------+

(2)使用sum(if(company=company_re,0,1))函数+over(partition by 身份证 order by 缴费月份)开窗给每一条数据做标记，得出新的一列flag。在同一家公司连续任职缴纳社保的期间标记flag应该是一致的。不连续任职比如之前在这工作跳槽到别家公司又跳回来任职，这属于新的社保缴纳区间，标记也是新的区间标记。

+----------+------+---------+-------------+-------+
| company  | idc  |   dt    | company_re  | flag  |
+----------+------+---------+-------------+-------+
| a        | 123  | 2022-1  | a           | 0     |
| a        | 123  | 2022-2  | a           | 0     |
| c        | 123  | 2022-3  | a           | 1     |
| c        | 123  | 2022-4  | c           | 1     |
| a        | 123  | 2022-5  | c           | 2     |
| a        | 123  | 2022-6  | a           | 2     |
| a        | 123  | 2022-7  | a           | 2     |
| b        | 234  | 2022-1  | b           | 0     |
| b        | 234  | 2022-2  | b           | 0     |
+----------+------+---------+-------------+-------+

(3)根据公司、身份证信息、flag标记分组，使用min(),max()得出员工在每段任职期间社保缴纳的起始时间和结束时间以及公司信息。

3.具体步骤

(1)建表

CREATE TABLE `accumulationfund`(                   
   `company` string COMMENT '公司名',                                
   `idc` string COMMENT '身份证信息',                                    
   `dt` string COMMENT '社保缴纳月份')                                     
 ROW FORMAT SERDE                                   
   'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'  
 STORED AS INPUTFORMAT                              
   'org.apache.hadoop.mapred.TextInputFormat'       
 OUTPUTFORMAT                                       
   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
 LOCATION                                           
   'hdfs://hadoop103:8020/user/hive/warehouse/accumulationfund' 
 TBLPROPERTIES (                                    
   'bucketing_version'='2',                         
   'transient_lastDdlTime'='1657886275');

(2)插入自己造的数据

insert into table accumulationfund values('a','123','2022-1'),('a','123','2022-2'),('b','234','2022-1'),('b','234','2022-2'),('c','123','2022-3'),('c','123','2022-4'),('a','123','2022-5'),('a','123','2022-6'),('a','123','2022-7');

(3)跑Hql(复制执行不了话，把前面所有空格去掉)

select
	company,
	idc,
	min(dt) start_date,
	max(dt) end_date
from
	(select
		company,
		idc,
		dt,
		company_re,
		sum(if(company=company_re,0,1)) over(partition by idc order by dt) flag
	from
			(select 
				company,
				idc,
				dt,
				lag(company,1,company) over(partition by idc order by dt) company_re
			from accumulationfund
			)t1
	)t2
group by company,idc,flag;

(4)结果，可以看到身份证为123的员工3家公司4段社保缴纳起止时间

+----------+------+-------------+-----------+
| company  | idc  | start_date  | end_date  |
+----------+------+-------------+-----------+
| a        | 123  | 2022-1      | 2022-2    |
| a        | 123  | 2022-5      | 2022-7    |
| b        | 234  | 2022-1      | 2022-2    |
| c        | 123  | 2022-3      | 2022-4    |
+----------+------+-------------+-----------+