[数据库] SQL特定查询场景之时间段切分方法

1 序言

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

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.1.1 【补充】时段补0问题（如：8:0 => 08:00）

select
	device_id
	, create_time 
	
	-- 方式1 如: '8:0' / '18:30' | 不支持左边自动补0
	, concat( toString(toHour(create_time , 'Asia/Shanghai')) , ':' , toString( FLOOR( toMinute(create_time , 'Asia/Shanghai') / 30 ) * 30 ) ) as time_period 
	
	-- 方式2 如: '08:00' / '18:30' | 支持左边自动补0
	, concat( 
		 right('00' || toString(toHour(create_time , 'Asia/Shanghai')) , 2 )
		, ':' 
		, right('00' || toString( FLOOR( toMinute(create_time , 'Asia/Shanghai') / 30 ) * 30 )  , 2)
	) as time_period2
	
		
	-- 方式3 如: '08:00' / '18:30' | 支持左边自动补0
	, formatDateTime(
		addMinutes(
			toDate(create_time, 'Asia/Shanghai'),
			floor(
				dateDiff('minute', toDate(create_time, 'Asia/Shanghai') , toDateTime(create_time, 'Asia/Shanghai')) / 30 ,
				0
			) * 30
		)
		, '%H:%M'
	) as time_period3
	
	-- 方式4 如: '08:00' / '18:30' | 支持左边自动补0 
    -- SELECT leftPad('abc', 7, '*'), leftPad('def', 7) | select * from system.functions where lower(name) like '%pad%'
	-- clickhouse 21.3.4.25 版本暂不支持，版本>= 21.8 (update at 2021-08-12)支持(参见 : )
	-- , concat( 
	-- 	 leftPad( toString(toHour(create_time , 'Asia/Shanghai')) , 2 , '0') , ':' , leftPad( toString( FLOOR( toMinute(create_time , 'Asia/Shanghai') / 30 ) * 30 ) , 2 , '0') 
	--) as time_period4 
FROM bdp_dwd.dwd_device_status_record_ri_d

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
)

...

2.3 写入数据库时即存储时段字段(timePeriod)

• 方法特点

• 不适用于分不同时区查询/分析数据的情况

因为写入的时段字段是某一个固定的时区，无法在查询时根据不同时区进行查询

3 离线数仓离线查询场景的解决方法

TODO

X 参考文献