Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2011
    Posts
    8

    Unanswered: How to get change of IP in middle of session

    Summary Table Details :

    Code:
    CREATE TABLE SUMMARY (
    	USERID VARCHAR(50), 
    	SESSION_ID VARCHAR(50),
    	DATE DATE,
    	FROMIP VARCHAR(39),
    	LOCATION VARCHAR(100),
    	START_TIME TIME,
    	END_TIME TIME,
    	APPLICATION VARCHAR(30),
    );
    Sample Values:

    Code:
    insert into SUMMARY VALUES ('pgadekar','1234','2011-04-02','115.240.51.181','Pune','12.01.00','20.00.00','APP1');
    insert into SUMMARY VALUES ('pgadekar','1234','2011-04-02','115.240.51.181','Pune','04.01.00','05.00.00','APP2');
    insert into SUMMARY VALUES ('pgadekar','1234','2011-04-02','115.240.51.181','Pune','06.01.00','08.00.00','APP3');
    insert into SUMMARY VALUES ('pgadekar','1234','2011-04-02','217.110.110.30','Nagpur','03.02.00','07.00.00','APP1');
    insert into SUMMARY VALUES ('pavan',   '2145','2011-04-02','115.240.51.181','Pune','12.01.00','20.00.00','APP4');
    insert into SUMMARY VALUES ('pavan',   '2145','2011-04-02','118.230.10.50','Ludiyana','06.01.00','08.00.00','APP2');
    insert into SUMMARY VALUES ('swapnil', '3236','2011-04-02','113.111.222.20','Aurangabad','03.02.00','07.00.00','APP1');
    insert into SUMMARY VALUES ('pgadekar','1234','2011-04-03','115.240.51.181','Pune','04.01.00','05.00.00','APP1');
    insert into SUMMARY VALUES ('pgadekar','1234','2011-04-03','115.240.51.181','Pune','05.01.00','05.30.00','APP2');
    insert into SUMMARY VALUES ('pgadekar','1234','2011-04-03','117.210.10.50','Delhi','06.01.00','06.40.00','APP3');
    insert into SUMMARY VALUES ('pgadekar','1234','2011-04-03','217.110.110.30','Nagpur','07.02.00','08.00.00','APP1');
    Here I want to select the Rows that having change of 'FROMIP' for same 'DATE' for same 'SESSION_ID'
    e.g.
    Code:
    ---------------------------------------------------------------------------------------------------------
    USERID 		SESSION_ID	DATE		LOCATION	APPLICATION 	START TIME	END TIME 
    ---------------------------------------------------------------------------------------------------------
    pgadekar 	1234		2011-04-02	Pune		APP1		12.01.00	20.00.00
    pgadekar	1234		2011-04-02	Nagpur		APP1		03.02.00	07.00.00
    pavan 		2145		2011-04-02	Pune		APP4		12.01.00	20.00.00
    pavan 		2145		2011-04-02	Pune		APP4		06.01.00	08.00.00
    pgadekar 	1234		2011-04-03	Pune		APP1		04.01.00	05.00.00
    pgadekar	1234		2011-04-03	Delhi		APP3		06.01.00	06.40.00
    pgadekar	1234		2011-04-03	Nagpur		APP1		07.02.00	08.00.00
    ---------------------------------------------------------------------------------------------------------
    Please anyone help me to write a sql query for above solution ?
    I tried to find out but got no luck .. Thanks in advance..

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    One way may be use of (tentative name)"COUNT DISTINCT and PARTITION" technique.
    comp.databases.ibm-db2 | Google Groups
    6. Tonkuma Apr 16, 11:05 am

    Example:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT VARCHAR(userid     , 10) AS userid
         , VARCHAR(session_id , 10) AS session_id
         , date
         , VARCHAR(location   , 10) AS location
         , VARCHAR(application, 10) AS application
         , start_time
         , end_time
     FROM  (SELECT s.*
                 , COUNT( CASE ROW_NUMBER()
                                 OVER(PARTITION BY date
                                                 , session_id
                                                 , fromip
                                     )
                          WHEN 1 THEN fromip
                          END
                        )
                     OVER(PARTITION BY date
                                     , session_id
                         ) AS count_distinct
                 , ROW_NUMBER()
                     OVER(PARTITION BY date
                                     , session_id
                                     , fromip
                         ) AS row_number
             FROM  summary s
           ) r
     WHERE count_distinct > 1
       AND row_number     = 1
     ORDER BY
           date
         , session_id
    ;
    ------------------------------------------------------------------------------
    
    USERID     SESSION_ID DATE       LOCATION   APPLICATION START_TIME END_TIME
    ---------- ---------- ---------- ---------- ----------- ---------- --------
    pgadekar   1234       2011-04-02 Pune       APP1        12:01:00   20:00:00
    pgadekar   1234       2011-04-02 Nagpur     APP1        03:02:00   07:00:00
    pavan      2145       2011-04-02 Pune       APP4        12:01:00   20:00:00
    pavan      2145       2011-04-02 Ludiyana   APP2        06:01:00   08:00:00
    pgadekar   1234       2011-04-03 Pune       APP1        04:01:00   05:00:00
    pgadekar   1234       2011-04-03 Delhi      APP3        06:01:00   06:40:00
    pgadekar   1234       2011-04-03 Nagpur     APP1        07:02:00   08:00:00
    
      7 record(s) selected.
    Note:
    location and application of 4th row are different from your sample result.
    I doubt your sample result.
    Or, please show the logic how to get 4th row values in your result.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I got SQL0104N error by your original CREATE TABLE statement
    (tab characters were removed and number of consecutive blanks were ajusted).

    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE SUMMARY (
     USERID      VARCHAR(50), 
     SESSION_ID  VARCHAR(50),
     DATE        DATE,
     FROMIP      VARCHAR(39),
     LOCATION    VARCHAR(100),
     START_TIME  TIME,
     END_TIME    TIME,
     APPLICATION VARCHAR(30),
    );
    ------------------------------------------------------------------------------
    CREATE TABLE SUMMARY ( USERID      VARCHAR(50), SESSION_ID  VARCHAR(50), DATE        DATE, FROMIP      VARCHAR(39), LOCATION    VARCHAR(100), START_TIME  TIME, END_TIME    TIME, APPLICATION VARCHAR(30), )
    DB21034E  The command was processed as an SQL statement because it was not a 
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0104N  An unexpected token ")" was found following "ICATION VARCHAR(30),".  
    Expected tokens may include:  "<table_element>".  SQLSTATE=42601

    I think that this is a good reason to position comma(",") at the first position of a line
    to find easily extra or lost comma,
    however it looks strange viwed from common sense of natural language.

    Here is an example:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE SUMMARY
    ( USERID      VARCHAR(50)
    , SESSION_ID  VARCHAR(50)
    , DATE        DATE
    , FROMIP      VARCHAR(39)
    , LOCATION    VARCHAR(100)
    , START_TIME  TIME
    , END_TIME    TIME
    , APPLICATION VARCHAR(30)
    )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Some other examples.

    Code:
    ----------------------------- Commands Entered ------------------------------
    SELECT VARCHAR(userid     , 10) AS userid
         , VARCHAR(session_id , 10) AS session_id
         , date
         , VARCHAR(location   , 10) AS location
         , VARCHAR(application, 10) AS application
         , start_time
         , end_time
     FROM  (SELECT s.*
                 , MIN(fromip)
                     OVER(PARTITION BY date
                                     , session_id
                         ) AS min_fromip
                 , MAX(fromip)
                     OVER(PARTITION BY date
                                     , session_id
                         ) AS max_fromip
                 , ROW_NUMBER()
                     OVER(PARTITION BY date
                                     , session_id
                                     , fromip
                         ) AS row_number
             FROM  summary s
           ) r
     WHERE min_fromip <> max_fromip
       AND row_number =  1
     ORDER BY
           date
         , session_id
    ;
    ------------------------------------------------------------------------------
    
    USERID     SESSION_ID DATE       LOCATION   APPLICATION START_TIME END_TIME
    ---------- ---------- ---------- ---------- ----------- ---------- --------
    pgadekar   1234       2011-04-02 Pune       APP1        12:01:00   20:00:00
    pgadekar   1234       2011-04-02 Nagpur     APP1        03:02:00   07:00:00
    pavan      2145       2011-04-02 Pune       APP4        12:01:00   20:00:00
    pavan      2145       2011-04-02 Ludiyana   APP2        06:01:00   08:00:00
    pgadekar   1234       2011-04-03 Pune       APP1        04:01:00   05:00:00
    pgadekar   1234       2011-04-03 Delhi      APP3        06:01:00   06:40:00
    pgadekar   1234       2011-04-03 Nagpur     APP1        07:02:00   08:00:00
    
      7 record(s) selected.

    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT VARCHAR(userid     , 10) AS userid
         , VARCHAR(session_id , 10) AS session_id
         , date
         , VARCHAR(location   , 10) AS location
         , VARCHAR(application, 10) AS application
         , start_time
         , end_time
     FROM  (SELECT s.*
                 , MIN(fromip)
                     OVER(PARTITION BY date
                                     , session_id
                         ) AS min_fromip
                 , MAX(fromip)
                     OVER(PARTITION BY date
                                     , session_id
                         ) AS max_fromip
                 , RANK()
                     OVER(PARTITION BY date
                                     , session_id
                              ORDER BY fromip
                         ) AS rank_fromip
                 , ROW_NUMBER()
                     OVER(PARTITION BY date
                                     , session_id
                              ORDER BY fromip
                         ) AS rownbr_fromip
             FROM  summary s
           ) r
     WHERE min_fromip <> max_fromip
       AND rank_fromip = rownbr_fromip
     ORDER BY
           date
         , session_id
    ;
    ------------------------------------------------------------------------------
    
    USERID     SESSION_ID DATE       LOCATION   APPLICATION START_TIME END_TIME
    ---------- ---------- ---------- ---------- ----------- ---------- --------
    pgadekar   1234       2011-04-02 Pune       APP1        12:01:00   20:00:00
    pgadekar   1234       2011-04-02 Nagpur     APP1        03:02:00   07:00:00
    pavan      2145       2011-04-02 Pune       APP4        12:01:00   20:00:00
    pavan      2145       2011-04-02 Ludiyana   APP2        06:01:00   08:00:00
    pgadekar   1234       2011-04-03 Pune       APP1        04:01:00   05:00:00
    pgadekar   1234       2011-04-03 Delhi      APP3        06:01:00   06:40:00
    pgadekar   1234       2011-04-03 Nagpur     APP1        07:02:00   08:00:00
    
      7 record(s) selected.

    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT MIN( VARCHAR(userid     , 10) ) AS userid
         , VARCHAR(session_id , 10)        AS session_id
         , MIN(date)                       AS date
         , MIN( VARCHAR(location   , 10) ) AS location
         , MIN( VARCHAR(application, 10) ) AS application
         , MIN(start_time)                 AS start_time
         , MIN(end_time)                   AS end_time
     FROM  (SELECT s.*
                 , MIN(fromip)
                     OVER(PARTITION BY date
                                     , session_id
                         ) AS min_fromip
                 , MAX(fromip)
                     OVER(PARTITION BY date
                                     , session_id
                         ) AS max_fromip
             FROM  summary s
           ) r
     WHERE min_fromip <> max_fromip
     GROUP BY
           date
         , session_id
         , fromip
     ORDER BY
           date
         , session_id
    ;
    ------------------------------------------------------------------------------
    
    USERID     SESSION_ID DATE       LOCATION   APPLICATION START_TIME END_TIME
    ---------- ---------- ---------- ---------- ----------- ---------- --------
    pgadekar   1234       2011-04-02 Pune       APP1        04:01:00   05:00:00
    pgadekar   1234       2011-04-02 Nagpur     APP1        03:02:00   07:00:00
    pavan      2145       2011-04-02 Pune       APP4        12:01:00   20:00:00
    pavan      2145       2011-04-02 Ludiyana   APP2        06:01:00   08:00:00
    pgadekar   1234       2011-04-03 Pune       APP1        04:01:00   05:00:00
    pgadekar   1234       2011-04-03 Delhi      APP3        06:01:00   06:40:00
    pgadekar   1234       2011-04-03 Nagpur     APP1        07:02:00   08:00:00
    
      7 record(s) selected.
    Note:
    start_time and end_time of 1st row are different from OP.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another example(more detailed result):

    I changed new column name of VARCHAR(session_id , 10) from session_id to "session_id"
    in order to avoid extra sort for ORDER BY.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT MIN( VARCHAR(userid     , 10) ) AS userid
         , VARCHAR(session_id , 10)        AS "session_id"
         , date
         , MIN( VARCHAR(location   , 10) ) AS location
         , MIN( VARCHAR(application, 10) ) AS application
         , VARCHAR(fromip     , 16)        AS fromip
         , SUBSTR(
              XMLCAST(
                 XMLGROUP(
                    ', ' || CHAR(start_time) || '-' || CHAR(end_time) AS t
                 )
                 AS VARCHAR(60)
              )
            , 3
           ) AS start_end_times
     FROM  (SELECT s.*
                 , COUNT( CASE ROW_NUMBER()
                                 OVER(PARTITION BY date
                                                 , session_id
                                                 , fromip
                                     )
                          WHEN 1 THEN fromip
                          END
                        )
                     OVER(PARTITION BY date
                                     , session_id
                         ) AS count_distinct
             FROM  summary s
           ) r
     WHERE count_distinct > 1
     GROUP BY
           date
         , session_id
         , fromip
     ORDER BY
           date
         , session_id
    ;
    ------------------------------------------------------------------------------
    
    USERID     session_id DATE       LOCATION   APPLICATION FROMIP           START_END_TIMES                                             
    ---------- ---------- ---------- ---------- ----------- ---------------- ------------------------------------------------------------
    pgadekar   1234       2011-04-02 Pune       APP1        115.240.51.181   12:01:00-20:00:00, 04:01:00-05:00:00, 06:01:00-08:00:00     
    pgadekar   1234       2011-04-02 Nagpur     APP1        217.110.110.30   03:02:00-07:00:00                                           
    pavan      2145       2011-04-02 Pune       APP4        115.240.51.181   12:01:00-20:00:00                                           
    pavan      2145       2011-04-02 Ludiyana   APP2        118.230.10.50    06:01:00-08:00:00                                           
    pgadekar   1234       2011-04-03 Pune       APP1        115.240.51.181   04:01:00-05:00:00, 05:01:00-05:30:00                        
    pgadekar   1234       2011-04-03 Delhi      APP3        117.210.10.50    06:01:00-06:40:00                                           
    pgadekar   1234       2011-04-03 Nagpur     APP1        217.110.110.30   07:02:00-08:00:00                                           
    
      7 record(s) selected.
    You may want to use LISTAGG function instead of XMLCAST( XMLGROUP(...) ...),
    if you have installed DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows.

    https://www-304.ibm.com/support/docv...id=swg24029745

    IBM DB2 9.7 for Linux, UNIX and Windows Information Center

    FYI: DB2 9.7.4 has been released with new SQL function Options
    Last edited by tonkuma; 04-30-11 at 09:34. Reason: CHange new column name of session_id to "session_id"

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I have installed DB2 Express-C 9.7.4 and tried to use LISTAGG function.

    Code:
    ------------------------------ Commands Entered ------------------------------
    connect to SAMPLE ;
    ------------------------------------------------------------------------------
    
       Database Connection Information
    
     Database server        = DB2/NT 9.7.4
     SQL authorization ID   = DB2ADMIN
     Local database alias   = SAMPLE
    
    
    A JDBC connection to the target has succeeded.
    ------------------------------ Commands Entered ------------------------------
    SELECT MIN( VARCHAR(userid     , 10) ) AS userid
         , VARCHAR(session_id , 10)        AS sessionid
         , date
         , MIN( VARCHAR(location   , 10) ) AS location
         , MIN( VARCHAR(application, 10) ) AS application
         , VARCHAR(fromip     , 16)        AS fromip
         , SUBSTR(
              LISTAGG( CHAR(start_time) || '-' || CHAR(end_time) , ', ' )
            , 1 , 60
           ) AS start_end_times
     FROM  (SELECT s.*
                 , COUNT( CASE ROW_NUMBER()
                                 OVER(PARTITION BY date
                                                 , session_id
                                                 , fromip
                                     )
                          WHEN 1 THEN fromip
                          END
                        )
                     OVER(PARTITION BY date
                                     , session_id
                         ) AS count_distinct
             FROM  summary s
           ) r
     WHERE count_distinct > 1
     GROUP BY
           date
         , session_id
         , fromip
     ORDER BY
           date
         , session_id
    ;
    ------------------------------------------------------------------------------
    
    USERID     SESSIONID  DATE       LOCATION   APPLICATION FROMIP           START_END_TIMES                                             
    ---------- ---------- ---------- ---------- ----------- ---------------- ------------------------------------------------------------
    pgadekar   1234       2011-04-02 Pune       APP1        115.240.51.181   12:01:00-20:00:00, 04:01:00-05:00:00, 06:01:00-08:00:00     
    pgadekar   1234       2011-04-02 Nagpur     APP1        217.110.110.30   03:02:00-07:00:00                                           
    pavan      2145       2011-04-02 Pune       APP4        115.240.51.181   12:01:00-20:00:00                                           
    pavan      2145       2011-04-02 Ludiyana   APP2        118.230.10.50    06:01:00-08:00:00                                           
    pgadekar   1234       2011-04-03 Pune       APP1        115.240.51.181   04:01:00-05:00:00, 05:01:00-05:30:00                        
    pgadekar   1234       2011-04-03 Delhi      APP3        117.210.10.50    06:01:00-06:40:00                                           
    pgadekar   1234       2011-04-03 Nagpur     APP1        217.110.110.30   07:02:00-08:00:00                                           
    
      7 record(s) selected.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •