跨天数据拆分为多条（每天一条）

1.需求场景

假如某个id对应的开始时间和结束时间存在跨天情况，而我们需要计算每一天的活跃id，可能会存在需要将跨天日期进行拆分（一条数据拆分为多条），如下图：

可以看出id为1的这条数据不存在跨天，就不需要进行拆分；id为2的这条数据跨了一天，就需要拆分为

2	2023-12-15 11:20:33	2023-12-15 23:59:59
2	2023-12-16 00:00:00	2023-12-16 11:50:33

id为3的这条数据也需要拆分为4条（跨天越多拆分得越多）...... 可以怎么实现呢？

2.实现步骤

由于不清楚到底会跨几天，最终需要拆分出几条，这里就引入一个辅助表

id取值为[0,31]（估计最多跨31天）,然后进行笛卡尔积就可以得到多条数据

select t1.Id ,
	t1.startTime ,
	t1.endTime ,
	t2.id ,
	t2.code 
from test t1,tmp_id t2
order by t1.Id,t2.id 

可见每一条数据都被拆分成32条，现在需要筛选出实际需要的，如果不存在跨天，只需要第一条(id=0)；如果跨一天，就需要第1,2条(id=0,id=1)……所以就需要算出每条数据的实际跨天数并过滤，具体实现如下：

select t1.Id ,
	t1.startTime ,
	t1.endTime ,
	t2.id ,
	t2.code 
from test t1,tmp_id t2
where t2.id < datediff( t1.endTime ,t1.startTime  ) + 1
order by t1.Id,t2.id sql

可见，现在已经得到需要的数据，接下来需要对日期字段进行更改：

select t1.Id ,
	t1.startTime ,
	t1.endTime ,
	t2.id ,
	t2.code ,
	case when t2.id=0 then t1.startTime
		 when t2.id>0 then concat(date(adddate(t1.startTime ,INTERVAL t2.id DAY)),' 00:00:00') 
	end as new_startTime,
	case when datediff( t1.endTime ,t1.startTime  )=t2.id then t1.endTime  
		else concat(date(adddate(t1.startTime ,INTERVAL t2.id DAY)),' 23:59:59') end as new_endTime
from test t1,tmp_id t2
where t2.id < datediff( t1.endTime ,t1.startTime  ) + 1
order by t1.Id,t2.id 

可见，最后2个就是我们的新字段。当然这只是一种思路，具体的sql可以变化多端。