根据列值将一行转换为多行

Question

我有一个表与员工登录小时历史，它有员工ID，打卡时间，打卡时间。我需要将此行转换为多行的钟表小时数。如果员工在上午10:00和下午6:00之间打卡，我需要8行，其中一行用于10am,11am..until 5 5pm

样本输入

Id  start time  end time    work hours
1       10:00   18:00       8
2       09:00   18:00       9

ID %1的所需结果

Id  st_time work hours
1   10:00   1
1   11:00   1
1   12:00   1
1   13:00   1
1   14:00   1
1   15:00   1
1   16:00   1
1   17:00   1
1   18:00   1

Answer 1

虽然Teradata支持TIME数据类型，但建议使用TIMESTAMP WITH TIME ZONE或TIMESTAMP (隐含时区)。下面是一个使用TIMESTAMP数据类型和EXPAND ON的示例

CREATE VOLATILE TABLE example (
  id INTEGER NOT NULL, str_time TIMESTAMP(0) NOT NULL, end_time TIMESTAMP(0) NOT NULL) 
  PRIMARY INDEX(id) on commit preserve rows;
  
INSERT example VALUES(1,'2020-01-07 10:00:00','2020-01-07 18:00:00');
INSERT example VALUES(2,'2020-01-07 09:00:00','2020-01-07 18:00:00');
INSERT example VALUES(3,'2020-01-07 17:15:00','2020-01-08 03:45:00');

SELECT id, str_time, end_time, BEGIN(pd) AS bucket_str, END(pd) AS bucket_end,
  CAST((LEAST(END(pd),end_time) - GREATEST(BEGIN(pd),str_time) MINUTE) AS DECIMAL(4,2))/60 AS worked_hours
FROM
  (SELECT id, str_time, end_time, pd
    FROM example
    EXPAND ON PERIOD(str_time,end_time) pd
    BY ANCHOR PERIOD ANCHOR_HOUR -- Expand to multiple rows starting/ending on the hour
  ) AS xpand
  ORDER BY id, bucket_str; 

添加额外的列和ORDER BY只是为了帮助澄清该过程，可以省略它们。