需求是在 clickhouse 中计算两个日期之前的相差分钟数,如何排除周六日时间段不算
例如时间为 2022-06-17 10:50:55 到 2022-06-19 15:00:00 计算出来的分钟数应为 2022-06-17 10:50:55 到 2022-06-17 23:59:59 的分钟数
1
larisboy OP 有大佬能解答一下吗
|
2
panpanpan 2022-06-17 11:10:06 +08:00
数据库感觉不适合干这事,看看有没有大佬有什么好的办法
|
4
aimiyooo 2022-06-17 11:22:17 +08:00
sql 应该实现不了吧,写个 udf ,把输入的时间段转换为天的列表[{'day': '*', 'start_time': '*', 'end_time': '*'}],把周末的天去掉。如果时间跨度比较大,性能有问题,再想更优的方法
|
6
nanmu42 2022-06-17 13:13:22 +08:00 via iPhone
解法分两步,第一步用函数+where 条件,只取周一到周五的数据,第二步再计算相差时长。第一步为第二步的子查询。
|
7
samzong 2022-06-17 13:20:39 +08:00
提供另外一个思路
1. 计算出 2 个时间点 t1,t2 的时间差值,转化为分钟值 X min 2. 把两个时间点用 toWeek? 转化为 周数值,然后算出 t1 和 t2 的周数差值 Y ,Y * 2800 = Y min 3. 用 X - Y 得出分钟数 小于 1 周,Y=0 ; 另外注意处理下 前后各 一天的这种情况 ... 没了 |
8
leonhao 2022-06-17 13:29:04 +08:00
很多人对 SQL 抱有成见,这个需求最起码用 postgresql 非常容易实现。clickhouse 不清楚,不是成熟的产品。
|
10
larisboy OP SELECT
'2022-06-18 10:10:10' AS d1, '2022-06-20 10:10:10' AS d2, toDate(d1) AS d1_date, toDate(d2) AS d2_date, addDays(d1_date, 1) AS d1_next_day, toInt8('1') AS week_start_day, toInt8('5') AS week_end_day, arrayMap(x -> (x + d1_next_day), arrayFilter(x -> (week_start_day > toDayOfWeek(addDays(d1_next_day, x)) OR toDayOfWeek(addDays(d1_next_day, x)) > week_end_day), CASE WHEN (d2_date - d1_next_day) < 0 THEN [] ELSE range(abs(d2_date - d1_next_day)) END )) as nonworkdays, dateDiff('minute', toDateTime(d1), toDateTime(d2)) - 24 * 60 * length(nonworkdays) AS all_minture, CASE WHEN (week_start_day > toDayOfWeek(d1_date) OR toDayOfWeek(d1_date) > week_end_day) THEN dateDiff('minute', toDateTime(d1), addDays(toStartOfDay(d1_date), 1)) ELSE 0 END AS d1_minture, CASE WHEN (week_start_day > toDayOfWeek(d2_date) OR toDayOfWeek(d2_date) > week_end_day) THEN dateDiff('minute', toStartOfDay(d2_date), toDateTime(d2)) ELSE 0 END AS d2_minture, all_minture - d1_minture - d2_minture AS work_minture 2022-06-18 10:10:10 2022-06-20 10:10:10 2022-06-18 2022-06-20 2022-06-19 1 5 ['2022-06-19'] 1440 830 0 610 已用 SQL 实现,欢迎指出 BUG |
11
leonhao 2022-06-17 15:30:59 +08:00
@shylockhg
with t as ( SELECT tstzrange(now(), now()+interval'1 day')*tstzrange(the_day::timestamptz, the_day::timestamptz+interval'1 day') as range FROM generate_series(date(now()),date(now())+interval'1 day',interval '1 day') the_day WHERE extract('ISODOW' FROM the_day) < 6) select sum(extract(epoch from upper(range)-lower(range))*60) from t; |