MySQL Group Nearest Timed Events
I have a MySQL table containing a log with 4 different events. Each event is logged every 15 or so minutes on its own row, but they're not consistently captured at the same time each time.
Table structure:
id datetime eventtype data
I need to perform some calculations from all 4 events captured in the same basic timeframe, typically within 5 minutes of each other. For example, events 1 and 2 may be captured at 10:41 and 10:42 respectively, while event 3 gets captured at 10:44, and event 4 arrives even later at 10:45.
The problem is that any event could be a minute or more ahead or behind others. Is there a way I can select and group the rows of each event type within the nearest timeframe?
Some inaccuracy is to be expected as it's possible for one event to not come in, or even get pushed a few minutes and become part of the next timeframe instead. I just need to ensure that any given grouping will have a valid event for each event type (which I can check at the time of the calculation).
I started with group by, but failed miserably since the datetime isn't the same for each event. This will be a large table so I don't think it makes sense to loop through rows and perform the math/grouping one by one, at least I hope it's not the best way. I'm not sure where to turn next. Any pointers in the right direction will be much appreciated!
Sample Data
id datetime eventtype data
1 10:41 1 67
2 10:45 4 14
3 10:42 2 12
4 10:44 3 87
5 10:57 1 63
6 10:59 4 13
7 10:59 2 19
8 11:01 3 01
Results from desired query:
datetime event1 event2 event3 event4
Result 1: 10:41 67 12 87 14
Result 2: 10:57 63 19 01 13
mysql select group-by
asked Nov 14 '18 at 16:35
Jimmyb
5131414
add a comment |
I have a MySQL table containing a log with 4 different events. Each event is logged every 15 or so minutes on its own row, but they're not consistently captured at the same time each time.
Table structure:
id datetime eventtype data
I need to perform some calculations from all 4 events captured in the same basic timeframe, typically within 5 minutes of each other. For example, events 1 and 2 may be captured at 10:41 and 10:42 respectively, while event 3 gets captured at 10:44, and event 4 arrives even later at 10:45.
The problem is that any event could be a minute or more ahead or behind others. Is there a way I can select and group the rows of each event type within the nearest timeframe?
Some inaccuracy is to be expected as it's possible for one event to not come in, or even get pushed a few minutes and become part of the next timeframe instead. I just need to ensure that any given grouping will have a valid event for each event type (which I can check at the time of the calculation).
I started with group by, but failed miserably since the datetime isn't the same for each event. This will be a large table so I don't think it makes sense to loop through rows and perform the math/grouping one by one, at least I hope it's not the best way. I'm not sure where to turn next. Any pointers in the right direction will be much appreciated!
Sample Data
id datetime eventtype data
1 10:41 1 67
2 10:45 4 14
3 10:42 2 12
4 10:44 3 87
5 10:57 1 63
6 10:59 4 13
7 10:59 2 19
8 11:01 3 01
Results from desired query:
datetime event1 event2 event3 event4
Result 1: 10:41 67 12 87 14
Result 2: 10:57 63 19 01 13
mysql select group-by
asked Nov 14 '18 at 16:35
Jimmyb
5131414
2
Consider handling issues of data display in application code
– Strawberry
Nov 14 '18 at 16:39
14 87 14!?!?
– Strawberry
Nov 14 '18 at 16:45
1
mysql and complex group by will lead you to sever performance issue
– Antony Gibbs
Nov 14 '18 at 17:06
I started down the path of performing the calcs in the application code, but felt it would drastically impact the web server performance when running calculations across days, weeks or even more. However, it sounds like this may be the best option due to the complexity.
– Jimmyb
Nov 14 '18 at 17:10
add a comment |
I have a MySQL table containing a log with 4 different events. Each event is logged every 15 or so minutes on its own row, but they're not consistently captured at the same time each time.
Table structure:
id datetime eventtype data
I need to perform some calculations from all 4 events captured in the same basic timeframe, typically within 5 minutes of each other. For example, events 1 and 2 may be captured at 10:41 and 10:42 respectively, while event 3 gets captured at 10:44, and event 4 arrives even later at 10:45.
The problem is that any event could be a minute or more ahead or behind others. Is there a way I can select and group the rows of each event type within the nearest timeframe?
Some inaccuracy is to be expected as it's possible for one event to not come in, or even get pushed a few minutes and become part of the next timeframe instead. I just need to ensure that any given grouping will have a valid event for each event type (which I can check at the time of the calculation).
I started with group by, but failed miserably since the datetime isn't the same for each event. This will be a large table so I don't think it makes sense to loop through rows and perform the math/grouping one by one, at least I hope it's not the best way. I'm not sure where to turn next. Any pointers in the right direction will be much appreciated!
Sample Data
id datetime eventtype data
1 10:41 1 67
2 10:45 4 14
3 10:42 2 12
4 10:44 3 87
5 10:57 1 63
6 10:59 4 13
7 10:59 2 19
8 11:01 3 01
Results from desired query:
datetime event1 event2 event3 event4
Result 1: 10:41 67 12 87 14
Result 2: 10:57 63 19 01 13
mysql select group-by
asked Nov 14 '18 at 16:35
Jimmyb
5131414
I have a MySQL table containing a log with 4 different events. Each event is logged every 15 or so minutes on its own row, but they're not consistently captured at the same time each time.
Table structure:
id datetime eventtype data
I need to perform some calculations from all 4 events captured in the same basic timeframe, typically within 5 minutes of each other. For example, events 1 and 2 may be captured at 10:41 and 10:42 respectively, while event 3 gets captured at 10:44, and event 4 arrives even later at 10:45.
The problem is that any event could be a minute or more ahead or behind others. Is there a way I can select and group the rows of each event type within the nearest timeframe?
Some inaccuracy is to be expected as it's possible for one event to not come in, or even get pushed a few minutes and become part of the next timeframe instead. I just need to ensure that any given grouping will have a valid event for each event type (which I can check at the time of the calculation).
I started with group by, but failed miserably since the datetime isn't the same for each event. This will be a large table so I don't think it makes sense to loop through rows and perform the math/grouping one by one, at least I hope it's not the best way. I'm not sure where to turn next. Any pointers in the right direction will be much appreciated!
Sample Data
id datetime eventtype data
1 10:41 1 67
2 10:45 4 14
3 10:42 2 12
4 10:44 3 87
5 10:57 1 63
6 10:59 4 13
7 10:59 2 19
8 11:01 3 01
Results from desired query:
datetime event1 event2 event3 event4
Result 1: 10:41 67 12 87 14
Result 2: 10:57 63 19 01 13
mysql select group-by
mysql select group-by
asked Nov 14 '18 at 16:35
Jimmyb
5131414
asked Nov 14 '18 at 16:35
Jimmyb
5131414
edited Nov 14 '18 at 17:03
asked Nov 14 '18 at 16:35
Jimmyb
5131414
asked Nov 14 '18 at 16:35
Jimmyb
5131414
asked Nov 14 '18 at 16:35
Jimmyb
5131414
5131414
2
Consider handling issues of data display in application code
– Strawberry
Nov 14 '18 at 16:39
14 87 14!?!?
– Strawberry
Nov 14 '18 at 16:45
1
mysql and complex group by will lead you to sever performance issue
– Antony Gibbs
Nov 14 '18 at 17:06
I started down the path of performing the calcs in the application code, but felt it would drastically impact the web server performance when running calculations across days, weeks or even more. However, it sounds like this may be the best option due to the complexity.
– Jimmyb
Nov 14 '18 at 17:10
add a comment |
2
Consider handling issues of data display in application code
– Strawberry
Nov 14 '18 at 16:39
14 87 14!?!?
– Strawberry
Nov 14 '18 at 16:45
1
mysql and complex group by will lead you to sever performance issue
– Antony Gibbs
Nov 14 '18 at 17:06
I started down the path of performing the calcs in the application code, but felt it would drastically impact the web server performance when running calculations across days, weeks or even more. However, it sounds like this may be the best option due to the complexity.
– Jimmyb
Nov 14 '18 at 17:10
2
2
Consider handling issues of data display in application code
– Strawberry
Nov 14 '18 at 16:39
Consider handling issues of data display in application code
– Strawberry
Nov 14 '18 at 16:39
14 87 14 !?!?– Strawberry
Nov 14 '18 at 16:45
14 87 14 !?!?– Strawberry
Nov 14 '18 at 16:45
1
1
mysql and complex group by will lead you to sever performance issue
– Antony Gibbs
Nov 14 '18 at 17:06
mysql and complex group by will lead you to sever performance issue
– Antony Gibbs
Nov 14 '18 at 17:06
I started down the path of performing the calcs in the application code, but felt it would drastically impact the web server performance when running calculations across days, weeks or even more. However, it sounds like this may be the best option due to the complexity.
– Jimmyb
Nov 14 '18 at 17:10
I started down the path of performing the calcs in the application code, but felt it would drastically impact the web server performance when running calculations across days, weeks or even more. However, it sounds like this may be the best option due to the complexity.
– Jimmyb
Nov 14 '18 at 17:10
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53304865%2fmysql-group-nearest-timed-events%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53304865%2fmysql-group-nearest-timed-events%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Consider handling issues of data display in application code
– Strawberry
Nov 14 '18 at 16:39
14 87 14!?!?– Strawberry
Nov 14 '18 at 16:45
1
mysql and complex group by will lead you to sever performance issue
– Antony Gibbs
Nov 14 '18 at 17:06
I started down the path of performing the calcs in the application code, but felt it would drastically impact the web server performance when running calculations across days, weeks or even more. However, it sounds like this may be the best option due to the complexity.
– Jimmyb
Nov 14 '18 at 17:10