我有一张桌子
table1
tran_id user_id start_date end_date
1 100 01-06-2018 18-06-2018
2 100 14-06-2018 14-06-2018
4 100 19-07-2018 19-07-2018
7 101 05-01-2018 06-01-2018
9 101 08-01-2018 08-01-2018
3 101 03-01-2018 03-01-2018
演示- Link
逻辑如下:
我需要找出按start_date排序的成员的两个trans_id
之间的天差,其中没有重叠的start_date和end_date。
我们需要检查一次处理一条记录的用户的最大end_date。
逻辑是这样的:
对于成员:
user_id
和start_date
trans_id
= 1,end_date
= 18-06-2018,user_id
= max_end_date
= 2,end_date
= 14-06-2018,end_date
< max_end_date
,move max_end_date
= 3,end_date
= 19-07-2018,<user_id
>D22 max_end_date
,在transidfrom
=1的输出中添加一条记录(因为这是max_end_date
)transidto
=4的记录(因为这是transidfrom
transidto_start_date
> max_end_date
)transidfrom_end_date
= 18-06-2018的记录,所以选择transidfrom
transidto_start_date
= 19-07-2018的trans_id
的trans_id
,选择transidfrom_end_date
= trans_id
- transidto_start_date
的trans_id
的start_date
输出将如下所示:
table2
my_id transidfrom transidto transidfrom_end_date transidto_start_date datediff
1 1 4 18-06-2018 19-07-2018 31
2 3 7 03-01-2018 05-01-2018 2
3 7 9 06-01-2018 08-01-2018 2
在Oracle SQL中,有没有一种方法可以在一个查询中做到这一点?
发布于 2019-07-09 03:10:50
如果我正确地理解了您的需求,那么也许下面的代码就可以做到:
FSITJA@db01 2019-07-08 12:08:59> with table1(tran_id, user_id, start_date, end_date) as (
2 select 1, 100, date '2018-06-01', date '2018-06-18' from dual union all
3 select 2, 100, date '2018-06-14', date '2018-06-14' from dual union all
4 select 4, 100, date '2018-07-19', date '2018-07-19' from dual union all
5 select 7, 101, date '2018-01-05', date '2018-01-06' from dual union all
6 select 9, 101, date '2018-01-08', date '2018-01-08' from dual union all
7 select 3, 101, date '2018-01-03', date '2018-01-03' from dual )
8 select rownum as my_id,
9 tran_id as transidfrom,
10 next_tran_id as transidto,
11 end_date as transidfrom_end_date,
12 next_end_date as transidto_start_date,
13 datediff
14 from (select tran_id,
15 user_id,
16 start_date,
17 end_date,
18 lead(tran_id) over (partition by user_id order by end_date) next_tran_id,
19 lead(start_date) over (partition by user_id order by end_date) next_end_date,
20 lead(start_date) over (partition by user_id order by end_date) - end_date datediff
21 from table1)
22 where datediff > 0;
MY_ID TRANSIDFROM TRANSIDTO TRANSIDFROM_END_DAT TRANSIDTO_START_DAT DATEDIFF
---------- ----------- ---------- ------------------- ------------------- ----------
1 1 4 2018-06-18 00:00:00 2018-07-19 00:00:00 31
2 3 7 2018-01-03 00:00:00 2018-01-05 00:00:00 2
3 7 9 2018-01-06 00:00:00 2018-01-08 00:00:00 2
3 rows selected.
https://stackoverflow.com/questions/56940002
复制相似问题