1 2 Previous Next 16 Replies Latest reply on Feb 18, 2020 3:11 PM by Ranagal

    Extract records which are present for two or more days in a row

    rootsman

      Could you please help me. I have one huge table which has this two columns.

      Screenshot_1.jpg

      I do need to list same records (cols_id) which they are appear for two or more days in a row. So to be more clear if i have table which has records for last 5 years, and i need to list all same hr_coil_id records which are repeating for 2 or more days in a row.

        • 1. Re: Extract records which are present for two or more days in a row
          Frank Kulash

          Hi,

           

          rootsman wrote:

           

          Could you please help me. I have one huge table which has this two columns.

          Screenshot_1.jpg

          I do need to list same records (cols_id) which they are appear for two or more days in a row. So to be more clear if i have table which has records for last 5 years, and i need to list all same hr_coil_id records which are repeating for 2 or more days in a row.

          Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the exact results you want from that data, so that the people who want to help you can re-create the problem and test their ideas.

          Explain, using specific examples, how you get those results from that data.

          Always say what version of Oracle you're using (e.g. 12.2.0.2.0).

          See the forum FAQ: Re: 2. How do I ask a question on the forums?

          • 2. Re: Extract records which are present for two or more days in a row
            mathguy

            I don't understand. If you are interested in something that may or may not happen "2 or more days in a row", how is the data you posted going to be helpful in understanding, when that data all happens in a single day?

            • 3. Re: Extract records which are present for two or more days in a row
              rootsman

              Please sorry its hard to avoid duplicates cause this table is full of it. We have many duplicate hr_coil_id records for same day. Our developers are seeking for solution to somehow list all those records which they have same hr_coil_id for 2 or more days in a row.

               

              Oracle server is version 11.2.0.4.0.

              • 4. Re: Extract records which are present for two or more days in a row
                Frank Kulash

                Hi,

                 

                It sounds like you want something like this:

                SELECT    hr_coil_id, sample_time

                FROM      huge

                MATCH_RECOGNIZE

                          (

                              PARTITION BY  hr_coil_id

                              ORDER BY      sample_time

                              ALL ROWS PER MATCH

                              PATTERN       (f m* l)

                              DEFINE        m  AS   TRUNC (m.sample_time) <= TRUNC (PREV (sample_time)) + 1

                      ,                     l  AS   TRUNC (l.sample_time) <= TRUNC (PREV (sample_time)) + 1

                                               AND  TRUNC (l.sample_time) >= TRUNC (f.sample_time) + 1

                          )

                ORDER BY  hr_coil_id, sample_time  -- or whatever you want

                ;

                This will display rows if (and only if) they are part of a group of rows all having the same coil_id, and where there are sample_times on at least two consecutive calendar days (where "consecutive" is determined by sample_time).

                Of course, I can't test it without any sample data.

                • 5. Re: Extract records which are present for two or more days in a row
                  Ranagal

                  How about just this ?

                   

                  Frank wrote:

                  Hi,

                   

                  It sounds like you want something like this:

                  SELECT    hr_coil_id, sample_time

                  FROM      huge

                  MATCH_RECOGNIZE

                            (

                                PARTITION BY  hr_coil_id

                                ORDER BY      sample_time

                                ALL ROWS PER MATCH

                                PATTERN       (f m* l)

                                DEFINE        m  AS   TRUNC (m.sample_time) <= TRUNC (PREV (sample_time)) + 1

                        ,                     l  AS   TRUNC (l.sample_time) <= TRUNC (PREV (sample_time)) + 1

                                                 AND  TRUNC (l.sample_time) >= TRUNC (f.sample_time) + 1

                            )

                  ORDER BY  hr_coil_id, sample_time  -- or whatever you want

                  ;

                  This will display rows if (and only if) they are part of a group of rows all having the same coil_id, and where there are sample_times on at least two consecutive calendar days (where "consecutive" is determined by sample_time).

                  Of course, I can't test it without any sample data.

                   

                  select * from inputs

                  match_recognize

                  (

                       partition by hr_coil_id

                       order by sample_time

                       all rows per match

                       pattern (a)

                       define a as  trunc(sample_time) = trunc(prev(sample_time)) and match_number() = 1

                  );

                   

                  EDIT: I misunderstood the requirements.

                   

                  Hey Frank,

                   

                  I think your code can be simplified like below:

                   

                  select * from inputs

                  match_recognize

                  (

                      partition by hr_coil_id

                      order by sample_time

                      all rows per match

                      pattern (a+ b)

                      define  b as trunc(sample_time) = trunc(prev(sample_time)) + 1

                  )

                  order by 1, 2

                   

                   

                  Regards,

                  Ranagal

                  • 6. Re: Extract records which are present for two or more days in a row
                    Ranagal

                    Could you please help me. I have one huge table which has this two columns.

                    Screenshot_1.jpg

                    I do need to list same records (cols_id) which they are appear for two or more days in a row. So to be more clear if i have table which has records for last 5 years, and i need to list all same hr_coil_id records which are repeating for 2 or more days in a row.

                     

                     

                    If you want all the rows that are repeating then

                     

                     

                    min_max as

                    (

                        select t.*, min(sample_time) over (partition by hr_coil_id) as min_time,

                                    max(sample_time) over (partition by hr_coil_id) as max_time

                        from inputs t

                    )

                    select hr_coil_id, sample_time from min_max where min_time <> max_time

                     

                    EDIT: I misunderstood the requirement.

                     

                    Regards,

                    Ranagal

                    • 7. Re: Extract records which are present for two or more days in a row
                      L. Fernigrini

                      That would produce the exact same result set... It seems that there are not real "exact" duplicates, but actually that the same ID has many rows for the same day, the difference is on the hour / minute / second part...

                      • 8. Re: Extract records which are present for two or more days in a row
                        Ranagal

                        You must be talking about Reply 6.

                         

                        with inputs as

                        (

                        select '9G01038' as hr_coil_id, to_date('31/07/2019 11:56:43', 'dd/mm/yyyy hh24:mi:ss') as sample_time from dual union all

                        select '9G01038' as hr_coil_id, to_date('31/07/2019 12:01:02', 'dd/mm/yyyy hh24:mi:ss') as sample_time from dual union all

                        select '9G01038' as hr_coil_id, to_date('31/07/2019 12:01:32', 'dd/mm/yyyy hh24:mi:ss') as sample_time from dual union all

                        select '9G01037' as hr_coil_id, to_date('31/07/2019 12:02:03', 'dd/mm/yyyy hh24:mi:ss') as sample_time from dual union all

                        select '9G01037' as hr_coil_id, to_date('31/07/2019 12:16:10', 'dd/mm/yyyy hh24:mi:ss') as sample_time from dual union all

                        select '9G01037' as hr_coil_id, to_date('31/07/2019 13:15:19', 'dd/mm/yyyy hh24:mi:ss') as sample_time from dual union all

                        select '9G01036' as hr_coil_id, to_date('31/07/2019 13:16:19', 'dd/mm/yyyy hh24:mi:ss') as sample_time from dual union all

                        select '9G01034' as hr_coil_id, to_date('31/07/2019 14:15:19', 'dd/mm/yyyy hh24:mi:ss') as sample_time from dual union all

                        select '9G01034' as hr_coil_id, to_date('31/07/2019 14:25:19', 'dd/mm/yyyy hh24:mi:ss') as sample_time from dual union all

                        select '9G01033' as hr_coil_id, to_date('31/07/2019 13:15:19', 'dd/mm/yyyy hh24:mi:ss') as sample_time from dual

                        ),

                        min_max as

                        (

                            select t.*, min(sample_time) over (partition by hr_coil_id) as min_time,

                                        max(sample_time) over (partition by hr_coil_id) as max_time

                            from inputs t

                        )

                        select hr_coil_id, sample_time from min_max where min_time <> max_time;

                         

                        Output:

                         

                         

                        I mean it excludes the ones that are not repeating for a day.

                        EDIT: I misunderstood the requirements

                        Regards,

                        Ranagal

                        • 9. Re: Extract records which are present for two or more days in a row
                          L. Fernigrini

                          They are not repeating, they are for different hr_coil_id.

                           

                          And OP never requested to delete duplicates, the request is to identify those hr_coil_id that have rows for two consecutive days....(2 days in a row..)

                          • 10. Re: Extract records which are present for two or more days in a row
                            Ranagal

                            Oh, got it now. I completely misunderstood the requirement. Whatever I posted till now is completely wrong. Thanks for pointing it out.

                            • 11. Re: Extract records which are present for two or more days in a row
                              rootsman

                              Ok, thank you guys for helping out. I got sql query which satisfies their condition.

                               

                              SELECT HR_COIL_ID, min(sample_time), max(sample_time), (max(sample_time) - min(sample_time)) FROM table

                                 group by HR_COIL_ID

                                 HAVING (max(sample_time) - min(sample_time)) > (interval '2' day)

                              order by (max(sample_time) - min(sample_time)) desc;

                              • 12. Re: Extract records which are present for two or more days in a row
                                L. Fernigrini

                                Are you sure that this satisfies the "2 days in a row" condition? I understand that as 2 consecutive days (maybe I'm doing m wrong translation). See this example (the WITH clause simulates your table, I had to rename it to MyTable since TABLE is a reserved word in Oracle and replace the INTERVAL with a simple 2 since the default date arithmetic in Oracle uses days as units)

                                 

                                WITH MyTable AS (

                                    SELECT 1 AS HR_COIL_ID, SYSDATE - 4 AS Sample_Time FROM DUAL

                                    UNION ALL

                                    SELECT 1 AS HR_COIL_ID, SYSDATE - 1 AS Sample_Time FROM DUAL

                                )

                                SELECT HR_COIL_ID, min(sample_time), max(sample_time), (max(sample_time) - min(sample_time))

                                FROM MyTable

                                group by HR_COIL_ID

                                HAVING (max(sample_time) - min(sample_time)) > 2

                                order by (max(sample_time) - min(sample_time)) desc;

                                 

                                Results:

                                • 13. Re: Extract records which are present for two or more days in a row
                                  Ranagal

                                  Are you sure what you posted really works ?

                                   

                                  rootsman wrote:

                                   

                                  Ok, thank you guys for helping out. I got sql query which satisfies their condition.

                                   

                                  SELECT HR_COIL_ID, min(sample_time), max(sample_time), (max(sample_time) - min(sample_time)) FROM table

                                     group by HR_COIL_ID

                                     HAVING (max(sample_time) - min(sample_time)) > (interval '2' day)

                                  order by (max(sample_time) - min(sample_time)) desc;

                                   

                                   

                                  Here is what I get on LiveSQL

                                   

                                   

                                  Regards,

                                  Ranagal

                                  • 14. Re: Extract records which are present for two or more days in a row
                                    L. Fernigrini

                                    If you want to know those that appear on any 2 different days (not consecutive) then you can just use:

                                     

                                    WITH MyTable AS (

                                        SELECT 1 AS HR_COIL_ID, SYSDATE - 4 AS Sample_Time FROM DUAL

                                        UNION ALL

                                        SELECT 1 AS HR_COIL_ID, SYSDATE - 1 AS Sample_Time FROM DUAL

                                        UNION ALL

                                        SELECT 2 AS HR_COIL_ID, SYSDATE - 2 AS Sample_Time FROM DUAL

                                    )

                                    SELECT HR_COIL_ID

                                    FROM MyTable

                                    group by HR_COIL_ID

                                    HAVING COUNT(DISTINCT TRUNC(sample_time)) > 1

                                     

                                    Results:

                                    1 2 Previous Next