10 Replies Latest reply on Feb 14, 2020 5:55 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 \

                   

                  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.