Forum Stats

  • 3,826,352 Users
  • 2,260,634 Discussions
  • 7,896,914 Comments

Discussions

How to get all the records using CTE

Albert Chao
Albert Chao Member Posts: 193 Green Ribbon
CREATE TABLE test_tab (
    col_name  VARCHAR2(20),
    log_time  TIMESTAMP(6),
    status    VARCHAR2(20)
);

INSERT INTO test_tab VALUES('Engineering','08-06-22 08:09:16.366000000 PM','UP');
INSERT INTO test_tab VALUES('Engineering','09-06-22 08:28:16.366000000 PM','UP');
INSERT INTO test_tab VALUES('Engineering','09-06-22 08:13:16.366000000 PM','DOWN');
INSERT INTO test_tab VALUES('Commerce','07-06-22 4:59:16.366000000 PM','DOWN');
INSERT INTO test_tab VALUES('Commerce','07-06-22 6:34:16.366000000 PM','UP');
INSERT INTO test_tab VALUES('Commerce','07-06-22 6:49:16.366000000 PM','DOWN');
INSERT INTO test_tab VALUES('Commerce','01-06-22 2:15:16.366000000 PM','UP');
INSERT INTO test_tab VALUES('Commerce','07-06-22 07:04:16.366000000 PM','UP');
COMMIT;

Tool used: Oracle Developer(18c)

I have one table test_tab in which there are multiple col_name values like 'Engineering', 'Commerce' etc just for testing purpose I have given two column names. I need to find out the start log time and end log time for particular col_name

Start_time logic : For particular col_name say 'Commerce' I need to check the minimum log time whose status is DOWN.

End_time logic: For the same col_name say 'Commerce' I need to check the start_time derived from above logic and see which is just greater than that time in status UP. That would be my end_time.

My attempt (Working only for 'Commerce'):

WITH a AS(
SELECT col_name,MIN(log_time)start_time
FROM test_tab WHERE status = 'DOWN'
GROUP BY col_name
),
b AS(
SELECT col_name,log_time end_time
    FROM( SELECT col_name,log_time,
        dense_rank() over (order by log_time asc)rnk
        FROM test_tab WHERE status = 'UP')
        WHERE rnk = 2
)
SELECT a.col_name,a.start_time,b.end_time FROM a
JOIN b ON(a.col_name = b.col_name);

But the above solution is not giving the result for 'Engineering'. It is just fetching details only for 'Commerce'

Expected Output:

Col_name    start_time                          end_time
Commerce    07-06-22 4:59:16.366000000 PM     07-06-22 6:34:16.366000000 PM
Engineering 09-06-22 08:13:16.366000000 PM    09-06-22 08:28:16.366000000 PM


Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,052 Red Diamond

    Hi, @Albert Chao

    End_time logic: For the same col_name say 'Commerce' I need to check the start_time derived from above logic and see which is just greater than that time in status UP. 

    Do you mean end_time should be the earliest log_time from rows with status='UP' and log_time later than start_time? Here's one way to do that:

    WITH  got_start_time  AS
    (
      SELECT  col_name, log_time, status
      ,	  MIN ( CASE
      	  	   WHEN status = 'DOWN'
    		   THEN log_time
    		 END
      	  	) OVER ( PARTITION BY col_name) AS start_time
      FROM  test_tab
    )
    SELECT   col_name
    ,	 start_time
    ,	 MIN ( CASE
    		  WHEN status   = 'UP'
    		  AND  log_time > start_time
    		  THEN log_time
    	   	END
    	   )		AS end_time
    FROM	 got_start_time
    GROUP BY col_name, start_time
    ORDER BY col_name
    ;
    


Answers

  • Alex Nuijten
    Alex Nuijten Member Posts: 242 Silver Badge

    When you include a partition clause for your analytic function, it should give you the desired effect:

     dense_rank() over (partition by col_name order by log_time asc)rnk

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,052 Red Diamond

    Hi, @Albert Chao

    CREATE TABLE test_tab (

      col_name VARCHAR2(20),

      log_time TIMESTAMP(6),

      status  VARCHAR2(20)

    );


    INSERT INTO test_tab VALUES('Engineering','08-06-22 08:09:16.366000000 PM','UP');

    Don't try to insert string values (such as '08-06-22 08:09:16.366000000 PM') into TIMESTAMP columns (such as col_time). It may not cause an error on your system today, and it may even produce the results you want, but it may not work tomorrow, or on any other system. Use TO_TIMESTAMP for TIMESTAMP values.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,052 Red Diamond

    Hi, @Albert Chao

    End_time logic: For the same col_name say 'Commerce' I need to check the start_time derived from above logic and see which is just greater than that time in status UP. 

    Do you mean end_time should be the earliest log_time from rows with status='UP' and log_time later than start_time? Here's one way to do that:

    WITH  got_start_time  AS
    (
      SELECT  col_name, log_time, status
      ,	  MIN ( CASE
      	  	   WHEN status = 'DOWN'
    		   THEN log_time
    		 END
      	  	) OVER ( PARTITION BY col_name) AS start_time
      FROM  test_tab
    )
    SELECT   col_name
    ,	 start_time
    ,	 MIN ( CASE
    		  WHEN status   = 'UP'
    		  AND  log_time > start_time
    		  THEN log_time
    	   	END
    	   )		AS end_time
    FROM	 got_start_time
    GROUP BY col_name, start_time
    ORDER BY col_name
    ;
    


  • mathguy
    mathguy Member Posts: 10,539 Blue Diamond

    You didn't specify what to do if there may be more than one row with the same column_name and same log_time - especially if they have different status. In my solution below, the result in that case is indeterminate. (Perhaps "that case" is not possible - for example if you have a unique constraint on the combination of two columns.)

    You didn't specify the required output if for a given column_name the status is never 'DOWN' (assuming that's possible). The query below will not show those column names at all in the output.

    You didn't specify the required output if for a given column_name there is no 'UP' row after the first 'DOWN' row. In those cases, my query will show start_time as you specified, and NULL in the end_time.

    You didn't specify if status can be anything other than 'UP' or 'DOWN', in particular if it may be NULL. In my query I assume the status can be anything (including NULL) - this just require a little bit of care in writing the query.

    You didn't tell us your Oracle version; in versions 12.1 and later, you can use match_recognize as shown below.

    select col_name, start_time, end_time
    from   test_tab
    match_recognize(
      partition by col_name
      order     by log_time
      measures  down.log_time as start_time,
                up.log_time   as end_time
      pattern   ( down not_up* up{0,1} )
      define    down   as status = 'DOWN' and match_number() = 1,
                not_up as lnnvl(status = 'UP'),
                up     as status = 'UP'
    );
    
    COL_NAME             START_TIME                     END_TIME                      
    -------------------- ------------------------------ ------------------------------
    Commerce             07-06-22 04:59:16.366000000 PM 07-06-22 06:34:16.366000000 PM
    Engineering          09-06-22 08:13:16.366000000 PM 09-06-22 08:28:16.366000000 PM
    


  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,471 Red Diamond

    Match recognize solution:

    select  *
      from  test_tab
      match_recognize(
                      partition by col_name
                      order by log_time
                      measures
                        first(down.log_time) start_time,
                        last(up.log_time)  end_time
                      pattern(up* down+ up any_status*)
                      define up   as status = 'UP',
                             down as status = 'DOWN'
                     )
    /
    
    COL_NAME             START_TIME                     END_TIME
    -------------------- ------------------------------ ------------------------------
    Commerce             07-06-22 04:59:16.366000 pm    07-06-22 06:34:16.366000 pm
    Engineering          09-06-22 08:13:16.366000 pm    09-06-22 08:28:16.366000 pm
    
    SQL>
    

    SY.

  • ora_1978
    ora_1978 Member Posts: 522 Bronze Badge

    drop table test_tab;

    CREATE TABLE test_tab (

      col_name VARCHAR2(20),

      log_time TIMESTAMP,

      status  VARCHAR2(20)

    );

    select sysdate from dual;

    INSERT INTO test_tab VALUES('Engineering','08-JUN-22 08:09:16.366000000 PM','UP');

    INSERT INTO test_tab VALUES('Engineering','09-JUN-22 08:28:16.366000000 PM','UP');

    INSERT INTO test_tab VALUES('Engineering','09-JUN-22 08:13:16.366000000 PM','DOWN');

    INSERT INTO test_tab VALUES('Commerce','07-JUN-22 4:59:16.366000000 PM','DOWN');

    INSERT INTO test_tab VALUES('Commerce','07-JUN-22 6:34:16.366000000 PM','UP');

    INSERT INTO test_tab VALUES('Commerce','07-JUN-22 6:49:16.366000000 PM','DOWN');

    INSERT INTO test_tab VALUES('Commerce','01-JUN-22 2:15:16.366000000 PM','UP');

    INSERT INTO test_tab VALUES('Commerce','07-JUN-22 07:04:16.366000000 PM','UP');

    COMMIT;


    select tbl.col_name, tbl.status_1,tbl.start_time,tbl.end_time, tbl.status_2 from 

    (

    SELECT a.col_name, a.log_time_1 start_time , b.LOG_TIME_2 end_time, a.status_1,ROW_NUMBER() OVER (partition by a.col_name ORDER BY b.log_time_2 desc) rn,

    b.status_2 FROM 

    (SELECT ROWNUM rn,COL_NAME,min(LOG_TIME) over(partition by col_name ) log_time_1 ,STATUS status_1 FROM test_tab where status ='DOWN') A,

    (SELECT ROWNUM rn, COL_NAME,LOG_TIME LOG_TIME_2 ,STATUS status_2 FROM test_tab where status ='UP' ) b

    WHERE b.rn - a.rn = 1  ) tbl where tbl.rn = 1;

    Albert Chao