sql - 在 Google BigQuery 中使用 TIME_DIFF 和多个条件

Question

我正在尝试计算 Google BigQuery (SQL) 中特定日期的工作时间。

白天工作的工资是 10 美元，夜间工作的工资是 15 美元。白天时间定义为早上 6 点到晚上 10 点，而晚上时间定义为晚上 10 点到早上 6 点。

员工可以灵活地工作，因为他们是豪华轿车司机。

以下是我的表的示例：

ID	start_at	end_at	日期
abc123	04:00:00	07:00:00	2020-01-05
abc123	09:00:00	15:32:00	2020-01-05
abc123	23:00:00	23:35:00	2020-01-05
abc123	23:40:00	23:59:00	2020-01-05
abc123	23:59:00	01:35:00	2020-01-05
abc123	02:02:00	04:35:00	2020-01-06
abc123	05:40:00	06:59:00	2020-01-06

因此，实际工作时间是通过计算 start_at 和 end_at 之间的差异来计算的，但是白天和晚上的时间条件在我的查询中变得很麻烦..

*日期列基于 start_at。即使您从晚上 11:59 开始并在第二天凌晨 12:05 结束，日期也会跟随 start_at 而不是 end_at 的日期。

有任何想法吗？提前致谢！

标签： sqlgoogle-bigquery

Answer 1

考虑以下解决方案

create temp function night_day_split(start_at time, end_at time, date date) as (array(
  select as struct 
    extract(date from time_point) day,
    if(extract(hour from time_point) between 6 and 22, 'day', 'night') day_night,
    count(1) minutes
  from unnest(generate_timestamp_array(
      timestamp(datetime(date, start_at)), 
      timestamp(datetime(if(start_at < end_at, date, date + 1), end_at)), 
      interval 1 minute
    )) time_point
  group by 1, 2
));
select id, day, 
  sum(if(day_night = 'day', minutes, null)) day_minutes,
  sum(if(day_night = 'night', minutes, null)) night_minutes
from yourtable, 
unnest(night_day_split(start_at, end_at, date)) v
group by id, day     

如果应用于您问题中的样本数据 - 输出是