我们如何将昨天下午5点到今天下午5点进行分组以记录到今天的日期

Question

甲骨文表

id  timestamp                status
1  2019-10-20 12:34:56.000   approved
1  2019-10-22 12:34:56.000   approved
2  2019-10-20 17:34:56.000   approved
2  2019-10-21 12:34:56.000   approved
3  2019-10-23 18:10:10.000   mod_in_ip
3  2019-10-24 11:10:10.000   approved
3  2019-10-24 12:10:10.000   approved
4  2019-10-25 12:10:10.000   approved
4  2019-10-25 18:10:10.000   approved

我想将id标记为 new 或 edited 。问题是从5pm到5pm的记录中被认为是工作时间段， 即从
‘2019-10-25 17:00:00’到‘2019-10-26 17:00:00’被认为是工作时间段
或昨天的下午5点到今天下午5点是一个工作时间。

例如：如果您查看表格并获得预期结果，则带有昨天6:00 PM记录和今天11AM记录的id记录应标记为new。

预期结果/结果应为

1 edited
2 new
3 new
4 edited

最初我尝试过此方法，但它无法解决上述问题

select id,
case
when count(id)<=1 then 'New' 
else 'Edited' End AS prefix 
from(select id,status ,trunc(timestamp) from table 
where 
status='approved' and id in (1,2,3,4)
group by id,status,trunc(timestamp))
group by id

结果是

1 Edited
2 Edited
3 new
4 new

但预期结果是

1 edited
2 new
3 new
4 edited

我正在寻找解决方案，例如 从5pm到5pm 的 innerquery组记录， 以便外部查询可以正常工作，或者完全不同的解决方案也是可行的

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=abd90416004000043c85316423d64b17

Answer 1

如果要将5pm到5pm视为“同一”天，可以很容易地将Oracle日期向前或向后移动一天的时间（例如，可以将5pm向前移动7小时以成为“下一个”的开始）天）

SQL> create table ora_table (id number, time_data timestamp, status varchar2(30));

Table created.

SQL> insert into ora_table values (1  , to_timestamp('2019-10-20 12:34:56.000', 'yyyy-mm-dd hh24:mi:ss.ff'), 'approved');

1 row created.

SQL> insert into ora_table values (1  , to_timestamp('2019-10-22 12:34:56.000', 'yyyy-mm-dd hh24:mi:ss.ff'), 'approved');

1 row created.

SQL> insert into ora_table values (2  , to_timestamp('2019-10-20 17:34:56.000', 'yyyy-mm-dd hh24:mi:ss.ff'), 'approved');

1 row created.

SQL> insert into ora_table values (2  , to_timestamp('2019-10-21 12:34:56.000', 'yyyy-mm-dd hh24:mi:ss.ff'), 'approved');

1 row created.

SQL> insert into ora_table values (3  , to_timestamp('2019-10-23 18:10:10.000', 'yyyy-mm-dd hh24:mi:ss.ff'), 'mod_in_ip');

1 row created.

SQL> insert into ora_table values (3  , to_timestamp('2019-10-24 11:10:10.000', 'yyyy-mm-dd hh24:mi:ss.ff'), 'approved');

1 row created.

SQL> insert into ora_table values (3  , to_timestamp('2019-10-24 12:10:10.000', 'yyyy-mm-dd hh24:mi:ss.ff'), 'approved');

1 row created.

SQL> insert into ora_table values (4  , to_timestamp('2019-10-25 12:10:10.000', 'yyyy-mm-dd hh24:mi:ss.ff'), 'approved');

1 row created.

SQL> insert into ora_table values (4  , to_timestamp('2019-10-25 18:10:10.000', 'yyyy-mm-dd hh24:mi:ss.ff'), 'approved');

1 row created.

SQL>
SQL> select id, time_data, trunc(time_data) true_date, trunc(time_data+7/24) mapped_date
  2  from ora_table;

        ID TIME_DATA                          TRUE_DATE MAPPED_DA
---------- ---------------------------------- --------- ---------
         1 20-OCT-19 12.34.56.000000 PM       20-OCT-19 20-OCT-19
         1 22-OCT-19 12.34.56.000000 PM       22-OCT-19 22-OCT-19
         2 20-OCT-19 05.34.56.000000 PM       20-OCT-19 21-OCT-19 <===
         2 21-OCT-19 12.34.56.000000 PM       21-OCT-19 21-OCT-19
         3 23-OCT-19 06.10.10.000000 PM       23-OCT-19 24-OCT-19 <===
         3 24-OCT-19 11.10.10.000000 AM       24-OCT-19 24-OCT-19
         3 24-OCT-19 12.10.10.000000 PM       24-OCT-19 24-OCT-19
         4 25-OCT-19 12.10.10.000000 PM       25-OCT-19 25-OCT-19
         4 25-OCT-19 06.10.10.000000 PM       25-OCT-19 26-OCT-19

9 rows selected.