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

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

已注册用户请登录

这是一个创建于 1262 天前的主题，其中的信息可能已经有所发展或是发生改变。

需求是在 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 条附言 2022-06-17 14:54:34 +08:00

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

第 2 条附言 2022-06-17 17:17:25 +08:00

SELECT
'2022-06-18 10:10:10' AS d1,
'2022-06-18 20: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) AND d1_date != d2_date) 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) AND d1_date != d2_date) THEN dateDiff('minute', toStartOfDay(d2_date), toDateTime(d2)) ELSE 0 END AS d2_minture,
CASE WHEN ((week_start_day > toDayOfWeek(d2_date) OR toDayOfWeek(d2_date) > week_end_day) AND d1_date == d2_date) THEN dateDiff('minute', toDateTime(d1), toDateTime(d2)) ELSE 0 END AS d1_d2_between_minture,
all_minture - d1_minture - d2_minture - d1_d2_between_minture AS work_minture

处理当 d1 和 d2 为同一天且都为周六或周日的情况

11 条回复 2022-06-17 15:30:59 +08:00

larisboy

2022-06-17 11:03:35 +08:00

有大佬能解答一下吗

panpanpan

2022-06-17 11:10:06 +08:00

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

larisboy

2022-06-17 11:12:18 +08:00

@panpanpan 感觉应该是可以的

aimiyooo

2022-06-17 11:22:17 +08:00

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

larisboy

2022-06-17 11:32:06 +08:00

@aimiyooo 好的，我试试

nanmu42

2022-06-17 13:13:22 +08:00 via iPhone

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

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 ; 另外注意处理下前后各一天的这种情况 ...

没了

leonhao

2022-06-17 13:29:04 +08:00

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

shylockhg

2022-06-17 13:41:23 +08:00

@leonhao 咋实现

larisboy

2022-06-17 14:54:26 +08:00

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;