使用您所引用的CASE EXPRESSION将是最好的方法。

create table my_table (
  order_num integer, 
  test_iteration integer, 
  complete_date timestamp
  );

insert all 
into my_table (order_num, test_iteration, complete_date) values
 (123456, 12, to_date('01/23/2023 12:18:36 AM', 'mm/dd/yyyy hh:mi:ss AM'))
into my_table (order_num, test_iteration, complete_date) values
 (9998, 4, to_date('01/23/2023 8:59:36 AM', 'mm/dd/yyyy hh:mi:ss AM'))  
into my_table (order_num, test_iteration, complete_date) values
 (5446, 2, to_date('01/23/2023 10:00:00 PM', 'mm/dd/yyyy hh:mi:ss AM')) 
into my_table (order_num, test_iteration, complete_date) values
 (654, 3, to_date('01/23/2023 5:00:00 PM', 'mm/dd/yyyy hh:mi:ss AM')) 
select 1 from dual;

select order_num, test_iteration, complete_date, 
 case
  when extract(hour from complete_date) >= 22  or extract(hour from complete_date) < 6 then 3
  when extract(hour from complete_date) >= 14  and extract(hour from complete_date) < 22 then 2
  else 1
 end shift
from my_table

| 测试迭代|完成日期|换档| SHIFT |
| - -----|- -----|- -----| ------------ |
| 十二岁|www.example.com|三个00.18.36.000000| 3 |
| 4| 23年1月23日08.59.36.000000|一个| 1 |
| 2| www.example.com|三个22.00.00.000000| 3 |
| 三个|www.example.com| 217.00.00.000000| 2 |
fiddle

编辑

这个表达式可以缩短为这个，只要你保持这个顺序：

case
  when extract(hour from complete_date) >= 22  or extract(hour from complete_date) < 6 then 3
  when extract(hour from complete_date) >= 14  then 2
  else 1
 end shift

我将向您展示SQL示例;

declare @dt datetime = '1/23/2023 11:00:00 PM'

select  
    case 
        when cast(@dt as time) between cast('06:00:00 AM' as time) and cast('01:59:59 PM' as time) then 1 
        when cast(@dt as time) between cast('02:00:00 PM' as time) and cast('09:59:59 PM' as time) then 2 
        when cast(@dt as time) between cast('10:00:00 PM' as time) and cast('11:59:59 PM' as time) then 3
        when cast(@dt as time) between cast('00:00:00 AM' as time) and cast('05:59:59 AM' as time) then 3
        else 0 
        end as shift

最后2排覆盖到12 AM和直到早晨

您可以使用以下公式计算：

SELECT order_num,
       test_iteration,
       complete_date,
       FLOOR(EXTRACT(HOUR FROM CAST(complete_date - INTERVAL '6' HOUR AS TIMESTAMP)) / 8) + 1 AS shift
FROM   table_name;

其中，对于样本数据：

CREATE TABLE table_name (order_num, test_iteration, complete_date) AS
SELECT 123456, 12, DATE '2023-01-23' + INTERVAL '08:18:36' HOUR TO SECOND FROM DUAL UNION ALL
SELECT LEVEL, LEVEL, DATE '2023-01-23' + (LEVEL - 1)/24 FROM DUAL CONNECT BY LEVEL <= 24;

输出：
| 测试迭代|完成日期|换档| SHIFT |
| - -----|- -----|- -----| ------------ |
| 十二岁|2023-01-23 08：18：36|一个| 1 |
| 一个|2019 -01-23 00：00：00|三个| 3 |
| 2| 2023-01-23 01：00：00|三个| 3 |
| 三个|2023-01-23 02：00：00|三个| 3 |
| 4| 2023-01-23 03：00：00|三个| 3 |
| 5个|2023-01-23 04：00：00|三个| 3 |
| 六|2023-01-23 05：00：00|三个| 3 |
| 七个|2023-01-23 06：00：00|一个| 1 |
| 八|2023-01-23 07：00：00|一个| 1 |
| 九个|2023-01-23 08：00：00|一个| 1 |
| 十个|2023-01-23 09：00：00|一个| 1 |
| 十一|2023-01-23 10：00：00|一个| 1 |
| 十二岁|2023-01-23 11：00：00|一个| 1 |
| 十三个|2023-01-23 12：00：00|一个| 1 |
| 十四|2023-01-23 13：00：00|一个| 1 |
| 十五|2023-01-23 14：00：00| 2| 2 |
| 十六|2023-01-23 15：00：00| 2| 2 |
| 十七岁|2023-01-23 16：00：00| 2| 2 |
| 十八岁|2023-01-23 17：00：00| 2| 2 |
| 十九|2023-01-23 18：00：00| 2| 2 |
| 二十个|2023-01-23 19：00：00| 2| 2 |
| 二十一|2023-01-23 20：00：00| 2| 2 |
| 二十二|2023-01-23 21：00：00| 2| 2 |
| 二十三|2023-01-23 22：00：00|三个| 3 |
| 二十四|2023-01-23 23：00：00|三个| 3 |
fiddle