实际项目中遇到需要查询记录之间日期范围重叠的记录,因为每条记录都包括开始和结束时间,本质就是查询日期重叠的记录,不需要完全重叠,只有有重叠就满足条件。举例:查询同时请假的人员、同时浏览网站的用户等。
在ClickHouse中可以在SQL中查询比较每个范围的开始和结束日期,判断是否有任何重叠。下面通过实例进行说明,首先定义请假表,结构如下:
CREATE TABLE vacation_schedule (
employee_id UInt32,
start_date Date,
end_date Date
) ENGINE = MergeTree()
ORDER BY (employee_id, start_date);
表包含员工请假记录,每天记录有各自的开始和结束日期。为了识别日期重叠,执行下面SQL查询语句:
SELECT a.employee_id AS employee1,
b.employee_id AS employee2,
a.start_date AS start_date1,
a.end_date AS end_date1,
b.start_date AS start_date2,
b.end_date AS end_date2
FROM vacation_schedule AS a JOIN vacation_schedule AS b
ON a.employee_id < b.employee_id
AND a.start_date <= b.end_date
AND a.end_date >= b.start_date;
结果包括员工间有日期重叠的记录,结构如下:
employee1 | employee2 | start_date1 | end_date1 | start_date2 | end_date2
------------------------------------------------------------------------
1 | 2 | 2022-08-01 | 2022-08-10| 2022-08-05 | 2022-08-12
2 | 3 | 2022-09-01 | 2022-09-10| 2022-09-08 | 2022-09-15
结果显示,employee1和employee1有重叠,从 2022-08-05到2022-08-10;类似employee2和employee3也有重叠,从2022-09-08到2022-09-10。
对于数据集中包括日期范围,通过这个方法可以有效识别日期重叠的记录。