1 序言

大数据项目、数据分析场景中，经常遇到需要切分时段的需求。
如下是一些项目上的经验总结。

2 实时数仓即席查询场景的解决方法

2.1 函数拼接法

方法特点：

时间数据以时间戳形式存储在数据库，而非以时间字符串

查询时实时运算时间段

基于此，支持根据终端用户所处位置/时区的不同，而实时计算时间段

数据量大、或并发量大时，需考虑数据库的计算资源（CPU、内存）是否充裕

案例1

以Clickhouse为例 / eg. timeZone = Aisa/Shanghai

concat( toString(toHour(create_time , '{{timeZone}}')) , ':' , toString( FLOOR( toMinute(create_time , '{{timeZone}}') / 30 ) * 30 ) ) as time_period -- eg. '8:0' / '18:30'

案例2

以 Clickhouse 为例 / eg. timeZone = Aisa/Shanghai

formatDateTime(
	addMinutes(
		toDate(alarm_recent_time, '{{timeZone}}'),
		floor(
			dateDiff('minute', toDate(alarm_recent_time, '{{timeZone}}') , toDateTime(alarm_recent_time, '{{timeZone}}')) / 30 ,
			0
		) * 30
	)
	, '%H:%M'
) as timePeriod

2.2 查询SQL按不同情况手动分隔时间段

方法特点

时间以时间戳形式存储在数据库

不建议/不适用于需要分几十上百个时段的情况（SQL会非常冗长、易错）

案例

以 Clickhouse 为例

...

(case
	when driverTime >= 0 and driverTime < 1800 then '0.0h-0.5h' -- 驾驶时长(单位：秒) := D 档信号个数 * tsp.vehicle_status_upload_frequency(30s)
	when driverTime >= 1800 and driverTime < 3600 then '0.5h-1.0h'
	when driverTime >= 3600 and driverTime < 7200 then '1.0h-2.0h'
	when driverTime >= 7200 and driverTime < 10800 then '2.0h-3.0h'
	when driverTime >= 10800 and driverTime < 14400 then '3.0h-4.0h'
	when driverTime >= 14400 and driverTime < 21600 then '4.0h-6.0h'
	else '≥6.0h'
END) as drivingTimePeriod

...
UNION ALL

(
	select '0.0h-0.5h' as drivingTimePeriod, 0 as vehicleCount
	union all
	select '0.5h-1.0h' as drivingTimePeriod, 0 as vehicleCount
	union all
	select '1.0h-2.0h' as drivingTimePeriod, 0 as vehicleCount
	union all
	select '2.0h-3.0h' as drivingTimePeriod, 0 as vehicleCount
	union all
	select '3.0h-4.0h' as drivingTimePeriod, 0 as vehicleCount
	union all
	select '4.0h-6.0h' as drivingTimePeriod, 0 as vehicleCount
	union all
	select '≥6.0h' as drivingTimePeriod, 0 as vehicleCount
)

...