请问大佬一个问题 [关于 Clickhouse]

[larisboy]

需求是在 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 的分钟数

[larisboy]

有大佬能解答一下吗

[panpanpan]

数据库感觉不适合干这事，看看有没有大佬有什么好的办法

[larisboy]

@panpanpan 感觉应该是可以的

[aimiyooo]

sql 应该实现不了吧，写个 udf ，把输入的时间段转换为天的列表[{'day': '*', 'start_time': '*', 'end_time': '*'}]，把周末的天去掉。如果时间跨度比较大，性能有问题，再想更优的方法

[larisboy]

@aimiyooo 好的，我试试

[nanmu42]

解法分两步，第一步用函数+where 条件，只取周一到周五的数据，第二步再计算相差时长。第一步为第二步的子查询。

[samzong]

提供另外一个思路

1. 计算出 2 个时间点 t1,t2 的时间差值，转化为分钟值 X min
2. 把两个时间点用 toWeek? 转化为 周数值，然后算出 t1 和 t2 的周数差值 Y ，Y * 2800 = Y min
3. 用 X - Y 得出分钟数

小于 1 周，Y=0 ; 另外注意处理下 前后各 一天的这种情况 ...

没了

[leonhao]

很多人对 SQL 抱有成见，这个需求最起码用 postgresql 非常容易实现。clickhouse 不清楚，不是成熟的产品。

[shylockhg]

@leonhao 咋实现

[larisboy]

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

[leonhao]

@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;