| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
04-28-11, 02:18
|
|
Registered User
|
|
Join Date: Mar 2011
Posts: 8
|
|
|
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..
|
|
04-28-11, 10:58
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
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.
|
|
04-28-11, 12:00
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
|
|
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.
|
|
04-29-11, 05:19
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
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.
|
|
04-29-11, 07:32
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
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 08:34.
Reason: CHange new column name of session_id to "session_id"
|
05-01-11, 14:43
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,195
|
|
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.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
How to get change of IP in middle of session
