我们如何将昨天下午5点到今天下午5点进行分组以记录到今天的日期 [英] how can we group 5 pm yesterday to 5 pm today records to todays date
问题描述
oracle表
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标记为新或已编辑.问题是从下午5点到下午5点的记录被认为是工作时间即
从'2019-10-25 17:00:00'到'2019-10-26 17:00:00'将被视为工作期间
或
昨天的下午5点到今天的下午5点是一个工作时段.
I want to label id's as new or edited. problem is from the records from 5pm to 5pm is considered working period i.e
'2019-10-25 17:00:00' to '2019-10-26 17:00:00' would be considered for a working period
or
yesterday's 5 pm to today's 5 pm is a working period.
例如:一个ID记录，该记录具有昨天的6:00 PM记录和一个今天的11AM记录，应标记为new 如果您看一下表格和预期结果，您就会知道
For example: an id record with yesterday 6:00PM record and today 11AM record should be labeled new if you look at the table and expected outcome you can get the idea
预期结果/结果应该是
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
我正在寻找诸如从下午5点到下午5点的内部查询组记录之类的解决方案，以便外部查询可以正常工作 或者完全不同的解决方案也是可行的
I am looking for solutions such as the innerquery groups records from 5pm to 5pm such that outer query can work fine or an entirely different solution is also feasible
https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle = abd90416004000043c85316423d64b17
推荐答案
如果要将5pm到5pm视为同一天"，可以很容易地将Oracle日期向前或向后移动一天的时间，例如，可以将下午5点向前移动7个小时，以成为第二天"的开始)
If you want to treat 5pm to 5pm as the "same" day, it is easy to shift an Oracle date either forward or backward with fractions of day, (eg 5pm can be shifted 7 hours forward to become the start of the 'next' day)
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.
这篇关于我们如何将昨天下午5点到今天下午5点进行分组以记录到今天的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
