场景描述
现有用户还款计划表 user_repayment ,该表内的一条数据,表示用户在指定日期区间内 [date_start, date_end] ,每天还款 repayment 元。
如何统计任意时段内(如:2024-01-15至2024-01-16)每天所有用户的应还款总额?
02
—
数据准备
with user_repayment as (
select stack(
3,
'101', '2024-01-01', '2024-01-15', 10,
'102', '2024-01-05', '2024-01-20', 20,
'103', '2024-01-10', '2024-01-25', 30
)
-- 字段:用户,开始日期,结束日期,每日还款金额
as (user_id, date_start, date_end, repayment)
)
select * from user_repayment;
03
—
问题分析
我们将需求转换为时序图,如下图所示:
时间点对应下图离散的点,图中标注了每个用户对应的起始点和结束点
通过上图可以看出,需求时间段对应的为15,和16两个点,15处此时有三个用户还款,因此当天所有用户还款总额为10+20+30 = 60,16点处当天有2个用户还款,还款应为20+30=50.上述的分析我们通过SQL怎么实现呢?
方法1:分情况讨论,找出重叠区间
我们先来了解一下,判断一个区间是否与已知的区间存在包含(重叠)关系的几种情况。
假设 已知的区间起点为st,结束点为et,当前需要判断的起点为ct,结束点为cet,如下图所示:
情况1:区间在右
判断条件 cet >= et and ct <= et 重叠区间为【ct,et]】
情况2:区间在内
判断条件为 ct>= st and cet <= et 重叠区间为 【ct,cet】
情况3:区间在左
判断条件 ct <= st and cet >= st 重叠区间为【st,cet】
基于上述情况的讨论,我们用SQL如下:
步骤1:计算重叠区间
注意需要需要判断区间2个断点重合的情况
select user_id
, date_start
, date_end
, repayment
, case
when '2024-01-15' <= date_end and '2024-01-16' >= date_end then
if('2024-01-15' = date_end, date_end, concat_ws(',', '2024-01-15', date_end))
when '2024-01-15' >= date_start and '2024-01-16' <= date_end
then concat_ws(',', '2024-01-15', '2024-01-16')
when '2024-01-15' <= date_start and '2024-01-16' >= date_start then
if('2024-01-15' = date_start, date_start, concat_ws('-,', date_start, '2024-01-16'))
end overlap_date
from user_repayment
步骤2:将重叠区间展开
利用如下方法展开
lateral view explode(split(overlap_date, ','))
SQL如下:
select user_id
, date_start
, date_end
, repayment
, dt
from (select user_id
, date_start
, date_end
, repayment
, case
when '2024-01-15' <= date_end and '2024-01-16' > date_end then
if('2024-01-15' = date_end, date_end, concat_ws(',', '2024-01-15', date_end))
when '2024-01-15' >= date_start and '2024-01-16' <= date_end
then concat_ws(',', '2024-01-15', '2024-01-16')
when '2024-01-15' < date_start and '2024-01-16' >= date_start then
if('2024-01-15' = date_start, date_start, concat_ws('-,', date_start, '2024-01-16'))
end overlap_date
from user_repayment) t
lateral view explode(split(overlap_date, ',')) tmp as dt
步骤3:按照dt进行分组,计算每天所有用户的还款金额
select dt
, sum(repayment) repayment
from (select user_id
, date_start
, date_end
, repayment
, case
when '2024-01-15' <= date_end and '2024-01-16' > date_end then
if('2024-01-15' = date_end, date_end, concat_ws(',', '2024-01-15', date_end))
when '2024-01-15' >= date_start and '2024-01-16' <= date_end
then concat_ws(',', '2024-01-15', '2024-01-16')
when '2024-01-15' < date_start and '2024-01-16' >= date_start then
if('2024-01-15' = date_start, date_start, concat_ws('-,', date_start, '2024-01-16'))
end overlap_date
from user_repayment) t
lateral view explode(split(overlap_date, ',')) tmp as dt
group by dt
;
方法2:暴力美学法。按区间展开成日期明细表
步骤1:区间日期展开,形成明细数据
select date_add(date_start, pos) as dt,
repayment
from user_repayment
lateral view posexplode(split(space(datediff(date_end, date_start)), '(?!&)')) t as pos, val;
步骤2:基于展开的明细数据,进行输入日期的过滤筛选,并分组求和
select dt
, sum(repayment) as repayment
from (select date_add(date_start, pos) as dt,
repayment
from user_repayment
lateral view posexplode(split(space(datediff(date_end, date_start)), '(?!&)')) t as pos, val) t
where dt >= '2024-01-15'
and dt <= '2024-01-16'
group by dt
order by dt;
04
拓展案例
场景描述
商品活动表 goods_event,g_id(有可能重复),t1(开始时间),t2(结束时间)
给定时间段(t3,t4),求在时间段内做活动的商品数
问题分析
利用前面分情况讨论总结的结论,直接给出答案
select count(distinct g_id) as event_goods_numfrom goods_event
where (t1<=t4 and t1>=t3) --区间在左
or (t1 <= t3 and t2>=t4) --区间在内
or (t2>=t3 and t2<=t4) --区间在右
05小结
本文分析了一种按任意时段计算时间区间问题的方法和思路,其本质是区间重叠问题的应用,文中给出了两种方法,一种按照区间重叠的方式进行分情况讨论划分区间,找出重叠区间,并计算问题,一种按照区间展开的形式形成全量的明细表,基于明细表求解任意给定的时段统计问题。