All geek questions in one place

Column of flags based on mines (date) of different rows every 2 months

I have a table like this one that has information about various complaints.

telephone   motive  complaint_id    complaint_date
980761524   motive1 R1234561        23/05/2017
980761524   motive1 R1234562        23/05/2017
980761524   motive1 R1234563        25/08/2017
980761524   motive1 R1234564        26/09/2017
980761524   motive1 R1234565        10/10/2017
980761524   motive1 R1234566        30/12/2017
991761525   motive2 R4454222        24/06/2017
991761525   motive2 R4454223        29/06/2017
991761525   motive2 R4454224        30/10/2017
940789563   motive3 R8993271        24/06/2017
940789563   motive3 R8993272        29/06/2017
940789563   motive3 R8993273        30/10/2017

I need to write a query (oracle-sql) that notes with repeat_flag (new column) for complaints (s) having the same phone, the same motive with min (complaints_date) as 0, but this minimum (complaints_date) changes every 2 months.

Let me explain in detail.

So, let's say I analyze the first group of phones / motives, which

telephone  motive  complaint_id    complaint_date
980761524   motive1 R1234561        23/05/2017
980761524   motive1 R1234562        23/05/2017
980761524   motive1 R1234563        25/08/2017
980761524   motive1 R1234564        26/09/2017
980761524   motive1 R1234565        10/10/2017
980761524   motive1 R1234566        30/12/2017

(_) 23/05/2017, 2 ​​ . , , Claim_id, , . complaints_id R+Number, - (R1234561) 0. , 2 (complaints_date), 1.

, , 1. 23/05/2017 , 1.

, , "0". request_date 25/08/2017, min (complaints_date)

, 26/09/2017, 2 25/08/2017, , 1.

, 10/10/2017, 2 25/08/2017, , 1.

, 30/12/2017, 2 25/08/2017, , . min (complaints_date)

.

:

telephone   motive  complaint   complaint_date  2months_repeat_flag
980761524   motive1 R1234561        23/05/2017                   0
980761524   motive1 R1234562        23/05/2017                   1
980761524   motive1 R1234563        25/08/2017                   0
980761524   motive1 R1234563        26/09/2017                   1
980761524   motive1 R1234563        10/10/2017                   1
980761524   motive1 R1234563        30/12/2017                   0

:

telephone   motive  complaint   complaint_date  2months_repeat_flag
980761524   motive1 R1234561        23/05/2017                    0
980761524   motive1 R1234562        23/05/2017                    1
980761524   motive1 R1234563        25/08/2017                    0
980761524   motive1 R1234564        26/09/2017                    1
980761524   motive1 R1234565        10/10/2017                    1
980761524   motive1 R1234566        30/12/2017                    0
991761525   motive2 R4454222        24/06/2017                    0
991761525   motive2 R4454223        29/06/2017                    1
991761525   motive2 R4454224        30/10/2017                    0
940789563   motive3 R8993271        24/06/2017                    0
940789563   motive3 R8993272        29/06/2017                    1
940789563   motive3 R8993273        30/10/2017                    0

2 30 .

SQL? . SQL, sp.

+4
4

, Oracle 10 . model ( match_recognize). match_recognize, Oracle 12 , , , OP ( oracle11g).

alter session set nls_date_format = 'dd/mm/yyyy';

create table test_data ( telephone, motive, complaint_id, complaint_date ) as
    select 980761524, 'motive1', 'R1234561', to_date('23/05/2017') from dual union all
    select 980761524, 'motive1', 'R1234562', to_date('23/05/2017') from dual union all
    select 980761524, 'motive1', 'R1234563', to_date('25/08/2017') from dual union all
    select 980761524, 'motive1', 'R1234564', to_date('26/09/2017') from dual union all
    select 980761524, 'motive1', 'R1234565', to_date('10/10/2017') from dual union all
    select 980761524, 'motive1', 'R1234566', to_date('30/12/2017') from dual union all
    select 991761525, 'motive2', 'R4454222', to_date('24/06/2017') from dual union all
    select 991761525, 'motive2', 'R4454223', to_date('29/06/2017') from dual union all
    select 991761525, 'motive2', 'R4454224', to_date('30/10/2017') from dual union all
    select 940789563, 'motive3', 'R8993271', to_date('24/06/2017') from dual union all
    select 940789563, 'motive3', 'R8993272', to_date('29/06/2017') from dual union all
    select 940789563, 'motive3', 'R8993273', to_date('30/10/2017') from dual
;

commit;

Query

select telephone, motive, complaint_id, complaint_date, flag
from   test_data
model  
  partition by (telephone, motive)  
  dimension by (row_number() over (partition by telephone, motive 
                                   order by     complaint_date, complaint_id) rn)
  measures (complaint_id, complaint_date, complaint_date s, 0 flag)  
  rules (  
    s[rn>1]    = case when complaint_date[cv(rn)] < add_months(s[cv(rn) - 1], 2)  
                      then s[cv(rn) - 1]  
                      else complaint_date[cv(rn)]  
                 end,
    flag[rn>1] = case when s[cv(rn)] = s[cv(rn) - 1] then 1 else 0 end
  )
order by telephone, motive, rn
;

: ( , - )

 TELEPHONE   MOTIVE    COMPLAINT_ID COMPLAINT_DATE FLAG
 ---------   -------   ------------ -------------- ----
 940789563   motive3   R8993271     24/06/2017        0
 940789563   motive3   R8993272     29/06/2017        1
 940789563   motive3   R8993273     30/10/2017        0
 980761524   motive1   R1234561     23/05/2017        0
 980761524   motive1   R1234562     23/05/2017        1
 980761524   motive1   R1234563     25/08/2017        0
 980761524   motive1   R1234564     26/09/2017        1
 980761524   motive1   R1234565     10/10/2017        1
 980761524   motive1   R1234566     30/12/2017        0
 991761525   motive2   R4454222     24/06/2017        0
 991761525   motive2   R4454223     29/06/2017        1
 991761525   motive2   R4454224     30/10/2017        0
+4

CONNECT BY

SELECT t.*,
       CASE WHEN t1.complaint_id IS NULL
       THEN 1 ELSE 0 
       END As two_months_repeat_flag
FROM test_data t
LEFT JOIN (
    SELECT complaint_id
    FROM (
        select t.complaint_id,
               (
                 SELECT min( complaint_id ) KEEP (DENSE_RANK FIRST ORDER BY complaint_date, complaint_id )
                 FROM test_data t1
                 WHERE t1.telephone = t.telephone
                   AND t1.motive = t.motive 
                   AND t1.complaint_date > add_months( t.complaint_date, 2 )
               ) as next_complaint_id,
               row_number() over (partition by telephone, motive order BY complaint_date, complaint_id) as rn
        from test_data t
    )
    start with rn = 1
    connect by complaint_id = prior next_complaint_id
) t1
ON t.complaint_id = t1.complaint_id
ORDER BY 1, 2,4,3

 TELEPHONE MOTIVE  COMPLAIN COMPLAINT_ TWO_MONTHS_REPEAT_FLAG
---------- ------- -------- ---------- ----------------------
 940789563 motive3 R8993271 24/06/2017                      0
 940789563 motive3 R8993272 29/06/2017                      1
 940789563 motive3 R8993273 30/10/2017                      0
 980761524 motive1 R1234561 23/05/2017                      0
 980761524 motive1 R1234562 23/05/2017                      1
 980761524 motive1 R1234563 25/08/2017                      0
 980761524 motive1 R1234564 26/09/2017                      1
 980761524 motive1 R1234565 10/10/2017                      1
 980761524 motive1 R1234566 30/12/2017                      0
 991761525 motive2 R4454222 24/06/2017                      0
 991761525 motive2 R4454223 29/06/2017                      1
 991761525 motive2 R4454224 30/10/2017                      0

12 rows selected.
+3

Oracle LAG, :

 SELECT telephone,
        motive,
        complaint_id,
        complaint_date,
        CASE WHEN 
           ADD_MONTHS(LAG  (complaint_date, 1 , NULL) 
                        OVER (PARTITION BY  telephone, motive 
                              ORDER BY complaint_date),2) > complaint_date   
             THEN 1 ELSE 0  END AS flag
   FROM test_data

, , .

0

For Oracle 12.1 and above, this is a solution that uses a suggestion match_recognize. The OP must try various solutions (those that solve the problem, as suggested, one way or another) to see which one is most effective for his / her specific data.

select telephone, motive, complaint_id, complaint_date,
       case classif when 'A' then 0 else 1 end
from   test_data
match_recognize (
  partition by telephone, motive
  order by complaint_date, complaint_id
  measures classifier() as classif
  all rows per match
  pattern ( a x* )
  define  x as complaint_date <= add_months(a.complaint_date, 2)
)
order by telephone, motive, complaint_date, complaint_id
;
0
source

Source: https://habr.com/ru/post/1680008/

More articles:


All Articles