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 Sorting of hex values Saurabh_mi DFSORT/ICETOOL 11 Thu May 25, 2017 3:49 pm
No new posts Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
No new posts Compare two files and subtract values ameetmund DFSORT/ICETOOL 7 Fri Mar 31, 2017 3:35 pm
No new posts Check Binary Values for symbols gandikk CA Products 3 Tue Mar 21, 2017 5:11 am
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm


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