Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

selecting max values with timestamp differences 5mins

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
ashish_k_sahoo

New User


Joined: 01 Mar 2005
Posts: 6
Location: Kolkata

PostPosted: Tue Nov 03, 2009 3:06 am    Post subject: selecting max values with timestamp differences 5mins
Reply with quote

Am having some data on my table as below which are on 1mins interval,
cols defn
TIME_TAKEN --timestamp
A INT
B INT
C INT
D INT
E INT

Code:

2009-11-01 00:05:17.0    23.5    1.5      150.1   12.8    6.6
2009-11-01 00:06:17.0    22.7    1.1        2.7   12.6     3.0
2009-11-01 00:07:17.0    16.0    1.0       38.9   12.4    2.6
2009-11-01 00:08:17.0    10.9    1.0       0.8   12.2    2.4
2009-11-01 00:09:17.0    35.3    1.7     157.9   13.8    4.9
2009-11-01 00:10:17.0     8.0    1.2       0.3   12.3    2.8
2009-11-01 00:11:17.0     5.0    1.3       0.0   19.5    3.9
2009-11-01 00:12:17.0    65.2    1.9     275.7    8.7    5.9
2009-11-01 00:13:17.0     5.5    0.6       0.0    9.8    1.8
2009-11-01 00:14:17.0    34.2    1.6     157.9   14.2    5.0
2009-11-01 00:15:17.0     5.3    1.2       0.0   13.1    3.0
2009-11-01 00:16:17.0    15.9    2.6      69.3   10.4    4.4
2009-11-01 00:17:17.0   253.4   36.2    8008.5    7.3   31.7
2009-11-01 00:18:17.0   253.8   53.8   12194.1     10.2   48.1
2009-11-01 00:19:17.0    37.9   18.5    4003.7   6226.5  604.4
2009-11-01 00:20:18.0    48.3   18.2    4942.7   5691.4  623.1

I need to have an query to get one of the row [having max value any one of the column] during 5mins intervals, say i need result of above as

2009-11-01 00:09:17.0 35.3 1.7 157.9 13.8 4.9
2009-11-01 00:12:17.0 65.2 1.9 275.7 8.7 5.9
2009-11-01 00:18:17.0 253.8 53.8 12194.1 10.2 48.1

is there anyway to achieve through DB2-SQL?[/img]
Back to top
View user's profile Send private message

ashish_k_sahoo

New User


Joined: 01 Mar 2005
Posts: 6
Location: Kolkata

PostPosted: Tue Nov 03, 2009 3:10 am    Post subject:
Reply with quote

got an sample query from web as below, but not able to standardize is it with my requirements.
WITH SOURCE(ID, NAME, TS) AS (
VALUES
(12179, 'CSR INQRY', '2008-05-09 16:12:46.946471'),
(12182, 'CSR INQRY', '2008-05-09 16:14:40.484615'),
(12193, 'CSR INQRY', '2008-05-09 16:41:15.513504'),
(12278, 'PURCHASE', '2008-05-10 08:58:48.528591'),
(12281, 'CSR INQRY', '2008-05-10 09:02:27.578658'),
(12292, 'CSR INQRY', '2008-05-10 09:10:18.026515'),
(12331, 'CSR INQRY', '2008-05-10 14:33:07.467567'),
(12377, 'CSR INQRY', '2008-05-12 09:08:40.549885'),
(12430, 'PURCHASE', '2008-05-12 11:07:34.635433'),
(12441, 'CSR INQRY', '2008-05-12 11:19:39.898771'),
(12452, 'PURCHASE', '2008-05-12 12:05:26.131342'),
(12476, 'PURCHASE', '2008-05-12 13:16:43.275927')
),
DATA AS (
SELECT ID, NAME, TIMESTAMP(TS) AS TS FROM SOURCE
),
RANGE AS (
-- Zero out the time so we wind up with nice 00,15,30,45
-- increments below; INTERVALS will generate rather more
-- intervals than needed with this, but that's okay
SELECT
TIMESTAMP(DATE(MIN(TS)), '00:00:00') AS MIN_TS,
TIMESTAMP(DATE(MAX(TS)) + 1 DAY, '00:00:00') AS MAX_TS
FROM DATA
),
INTERVALS(I, TS) AS (
-- I included to prevent infinite recursion warnings
SELECT 1, MIN_TS FROM RANGE
UNION ALL
SELECT I + 1, TS + 15 MINUTES FROM INTERVALS, RANGE
WHERE I < 100000 AND TS + 15 MINUTES < MAX_TS
)
SELECT
TIME(I.TS) AS TS,
S.NAME,
COUNT(*) AS COUNT
FROM
INTERVALS I
INNER JOIN DATA S
ON S.TS BETWEEN I.TS AND I.TS + 15 MINUTES - 1 MICROSECOND
GROUP BY
TIME(I.TS),
S.NAME;
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Nov 03, 2009 3:11 am    Post subject:
Reply with quote

Hello,

You mention 5 columns but only show 3 "max" rows.

What are the rules for determining "max" that retrieve only 3?
Back to top
View user's profile Send private message
ashish_k_sahoo

New User


Joined: 01 Mar 2005
Posts: 6
Location: Kolkata

PostPosted: Tue Nov 03, 2009 3:13 am    Post subject:
Reply with quote

any of the column having having max value during the 5mins interval.
Back to top
View user's profile Send private message
ashish_k_sahoo

New User


Joined: 01 Mar 2005
Posts: 6
Location: Kolkata

PostPosted: Tue Nov 03, 2009 3:17 am    Post subject:
Reply with quote

from 5 rows on 5mins, i need the one as result which having max value on A or B or C or D or E having it.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Nov 03, 2009 3:20 am    Post subject:
Reply with quote

Hello,

Quote:
any of the column having having max value during the 5mins interval.
What does this tell us?

If there are 5 columns, i'd expect 5 max values? You mentoin a 5mins interval?

It is up to you to provide a complete and clear explanation.

Suggest you re-post the "data" using the "Code" tag to improve readability. Use Preview to see how your post will appear to the forum and when you are satisfied with the way the post appears, Submit.
Back to top
View user's profile Send private message
ashish_k_sahoo

New User


Joined: 01 Mar 2005
Posts: 6
Location: Kolkata

PostPosted: Tue Nov 03, 2009 3:41 am    Post subject:
Reply with quote

the above table/view having 6 columns as described earlier,
one with time stamp and rest are with INT values.
table having data with 1min durations. Now i need to select the data within 5mins say from above
Code:

2009-11-01 00:06:17.0 22.7  1.1      2.7       12.6    3.0
2009-11-01 00:07:17.0 16.0  1.0    38.9       12.4    2.6
2009-11-01 00:08:17.0 10.9  1.0      0.8       12.2    2.4
2009-11-01 00:09:17.0 35.3  1.7  157.9       13.8     4.9
2009-11-01 00:10:17.0  8.0   1.2     0.3       12.3     2.8

i need the row
Code:

2009-11-01 00:09:17.0 35.3  1.7  157.9   13.8     4.9

as C having max value.

then from second 5min duration
Code:

2009-11-01 00:11:17.0  5.0      1.3  0.0      19.5   3.9
2009-11-01 00:12:17.0  65.2    1.9  275.7   8.7     5.9
2009-11-01 00:13:17.0  5.5      0.6  0.0      9.8     1.8
2009-11-01 00:14:17.0  34.2    1.6  157.9   14.2   5.0
2009-11-01 00:15:17.0  5.3      1.2  0.0      13.1    3.0

i need the row
Code:

2009-11-01 00:14:17.0  34.2    1.6  157.9   14.2   5.0

as C having max value.

then on the same way i need to retrive all the rows, which from all above result will be like
Code:

2009-11-01 00:09:17.0     35.3     1.7       157.9     13.8   4.9
2009-11-01 00:12:17.0     65.2     1.9       275.7     8.7    5.9
2009-11-01 00:18:17.0     253.8   53.8     12194.1  10.2  48.1
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Nov 03, 2009 9:57 am    Post subject:
Reply with quote

Hello,

I am still rather confused. . . icon_confused.gif

Why is this row
Code:
2009-11-01 00:14:17.0  34.2    1.6  157.9   14.2   5.0
selected from the second set when
Code:
2009-11-01 00:12:17.0  65.2    1.9  275.7   8.7     5.9
has a greater value?

What should happen for a time (minute) when there is no entry? Why does the "sampling" start at minute 6:17 rather than 0:00?

I believe what you want to do would be rather easily done in code with a cursor.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts Overlaying one set of charater values... Kevin Lindsley SYNCSORT 7 Sat Nov 05, 2016 3:21 am
No new posts Low values Results from VARCHAR FORMAT balaji81_k DB2 10 Thu Oct 20, 2016 1:18 am
No new posts How can we create a flat file in JAVA... rakesh.v18 Java & MQSeries 7 Fri Sep 23, 2016 10:46 pm
No new posts Using 'parm' to vary SORTOUT record v... Sysaron DFSORT/ICETOOL 13 Wed Sep 07, 2016 9:24 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us