sql - 根据条件查找Oracle SQL中的日期差异

Question

我有一张桌子：

表格1

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

演示 -链接

这是逻辑：

我需要为按 start_date 排序的成员查找两者之间的天差，trans_id其中没有重叠的 start_date 和 end_date。

我们需要检查一次处理一条记录的用户的最大 end_date。

逻辑是：

对于会员：

• user_id按and对所有记录进行排序start_date
• trans_id= 1，end_date= 2018 年 6 月 18 日，设置max_end_date= 2018年 6 月 18 日
• trans_id= 2, end_date= 14-06-2018, end_date< max_end_date, 前进
• trans_id= 3, end_date= 19-07-2018, end_date> max_end_date, 在输出中添加一条记录
  • transidfrom= 1（因为这是带有 的记录max_end_date）
  • transidto= 4（因为这是end_date>的记录max_end_date）
  • transidfrom_end_date= 18-06-2018，选择end_date的trans_idtransidfrom
  • transidto_start_date= 19-07-2018，选择start_date的trans_idtransidto
  • datediff= transidto_start_date-transidfrom_end_date

输出如下：

表2

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 in 1 查询中做到这一点？

标签： sqloracle

Answer 1

如果我正确理解了您的要求，那么也许可以这样做：

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.

SQL 小提琴示例