V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX 提问指南
larisboy
V2EX  ›  问与答

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

  •  
  •   larisboy · 2022-06-17 10:52:27 +08:00 · 1302 次点击
    这是一个创建于 650 天前的主题,其中的信息可能已经有所发展或是发生改变。

    需求是在 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
        1
    larisboy  
    OP
       2022-06-17 11:03:35 +08:00
    有大佬能解答一下吗
    panpanpan
        2
    panpanpan  
       2022-06-17 11:10:06 +08:00
    数据库感觉不适合干这事,看看有没有大佬有什么好的办法
    larisboy
        3
    larisboy  
    OP
       2022-06-17 11:12:18 +08:00
    @panpanpan 感觉应该是可以的
    aimiyooo
        4
    aimiyooo  
       2022-06-17 11:22:17 +08:00
    sql 应该实现不了吧,写个 udf ,把输入的时间段转换为天的列表[{'day': '*', 'start_time': '*', 'end_time': '*'}],把周末的天去掉。如果时间跨度比较大,性能有问题,再想更优的方法
    larisboy
        5
    larisboy  
    OP
       2022-06-17 11:32:06 +08:00
    @aimiyooo 好的,我试试
    nanmu42
        6
    nanmu42  
       2022-06-17 13:13:22 +08:00 via iPhone
    解法分两步,第一步用函数+where 条件,只取周一到周五的数据,第二步再计算相差时长。第一步为第二步的子查询。
    samzong
        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 ; 另外注意处理下 前后各 一天的这种情况 ...

    没了
    leonhao
        8
    leonhao  
       2022-06-17 13:29:04 +08:00
    很多人对 SQL 抱有成见,这个需求最起码用 postgresql 非常容易实现。clickhouse 不清楚,不是成熟的产品。
    shylockhg
        9
    shylockhg  
       2022-06-17 13:41:23 +08:00
    @leonhao 咋实现
    larisboy
        10
    larisboy  
    OP
       2022-06-17 14:54:26 +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
    leonhao
        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;
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   2770 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 00:24 · PVG 08:24 · LAX 17:24 · JFK 20:24
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.