Finding Count of Occurrences & Time Gap between a Timeline in MySQL
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
0
I have a Table, like the below, which has a timeline and values (like YES/NO) that are coming from different sensors. How do I do the following:
How many times there has a been a flip-flops (for YES to NO or NO to YES), in the below example it has happened 2 times.
1 , A0001, NO, 2017-06-14 02:03:26.0
2 , A0001, NO, 2017-06-14 02:03:26.0
3 , A0001, YES, 2017-06-14 02:03:26.0
4 , A0001, NO, 2017-06-14 02:03:26.0
5 , A0001, YES, 2017-06-14 02:03:26.0
6 , A0001, NO , 2017-06-14 02:03:26.0
mysql datetime
edited Jun 14 '17 at 6:47
Marco
3,74031724
asked Jun 14 '17 at 2:09
Shan K SShan K S
1
bumped to the homepage by Community♦ 11 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
0
I have a Table, like the below, which has a timeline and values (like YES/NO) that are coming from different sensors. How do I do the following:
How many times there has a been a flip-flops (for YES to NO or NO to YES), in the below example it has happened 2 times.
1 , A0001, NO, 2017-06-14 02:03:26.0
2 , A0001, NO, 2017-06-14 02:03:26.0
3 , A0001, YES, 2017-06-14 02:03:26.0
4 , A0001, NO, 2017-06-14 02:03:26.0
5 , A0001, YES, 2017-06-14 02:03:26.0
6 , A0001, NO , 2017-06-14 02:03:26.0
mysql datetime
edited Jun 14 '17 at 6:47
Marco
3,74031724
asked Jun 14 '17 at 2:09
Shan K SShan K S
1
bumped to the homepage by Community♦ 11 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
To clarify: There have been two changes from 'NO' to 'YES', and two changes from 'YES' to 'NO'. That's a total of 4 changes. Are you only interested in changes in 1 direction, or perhaps only interested in round trips ('NO' => 'YES' => 'NO'). Also, are all sensors binary, or could some changes involve more than two possible values? Sample desired output, showing handling for more than two possible values if relevant, would help some.
– RDFozz
Jun 14 '17 at 16:48
add a comment |
0
0
0
I have a Table, like the below, which has a timeline and values (like YES/NO) that are coming from different sensors. How do I do the following:
How many times there has a been a flip-flops (for YES to NO or NO to YES), in the below example it has happened 2 times.
1 , A0001, NO, 2017-06-14 02:03:26.0
2 , A0001, NO, 2017-06-14 02:03:26.0
3 , A0001, YES, 2017-06-14 02:03:26.0
4 , A0001, NO, 2017-06-14 02:03:26.0
5 , A0001, YES, 2017-06-14 02:03:26.0
6 , A0001, NO , 2017-06-14 02:03:26.0
mysql datetime
edited Jun 14 '17 at 6:47
Marco
3,74031724
asked Jun 14 '17 at 2:09
Shan K SShan K S
1
I have a Table, like the below, which has a timeline and values (like YES/NO) that are coming from different sensors. How do I do the following:
How many times there has a been a flip-flops (for YES to NO or NO to YES), in the below example it has happened 2 times.
1 , A0001, NO, 2017-06-14 02:03:26.0
2 , A0001, NO, 2017-06-14 02:03:26.0
3 , A0001, YES, 2017-06-14 02:03:26.0
4 , A0001, NO, 2017-06-14 02:03:26.0
5 , A0001, YES, 2017-06-14 02:03:26.0
6 , A0001, NO , 2017-06-14 02:03:26.0
mysql datetime
mysql datetime
edited Jun 14 '17 at 6:47
Marco
3,74031724
asked Jun 14 '17 at 2:09
Shan K SShan K S
1
edited Jun 14 '17 at 6:47
Marco
3,74031724
asked Jun 14 '17 at 2:09
Shan K SShan K S
1
edited Jun 14 '17 at 6:47
Marco
3,74031724
edited Jun 14 '17 at 6:47
Marco
3,74031724
edited Jun 14 '17 at 6:47
Marco
3,74031724
3,74031724
asked Jun 14 '17 at 2:09
Shan K SShan K S
1
asked Jun 14 '17 at 2:09
Shan K SShan K S
1
asked Jun 14 '17 at 2:09
Shan K SShan K S
1
1
bumped to the homepage by Community♦ 11 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 11 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
To clarify: There have been two changes from 'NO' to 'YES', and two changes from 'YES' to 'NO'. That's a total of 4 changes. Are you only interested in changes in 1 direction, or perhaps only interested in round trips ('NO' => 'YES' => 'NO'). Also, are all sensors binary, or could some changes involve more than two possible values? Sample desired output, showing handling for more than two possible values if relevant, would help some.
– RDFozz
Jun 14 '17 at 16:48
add a comment |
To clarify: There have been two changes from 'NO' to 'YES', and two changes from 'YES' to 'NO'. That's a total of 4 changes. Are you only interested in changes in 1 direction, or perhaps only interested in round trips ('NO' => 'YES' => 'NO'). Also, are all sensors binary, or could some changes involve more than two possible values? Sample desired output, showing handling for more than two possible values if relevant, would help some.
– RDFozz
Jun 14 '17 at 16:48
To clarify: There have been two changes from 'NO' to 'YES', and two changes from 'YES' to 'NO'. That's a total of 4 changes. Are you only interested in changes in 1 direction, or perhaps only interested in round trips ('NO' => 'YES' => 'NO'). Also, are all sensors binary, or could some changes involve more than two possible values? Sample desired output, showing handling for more than two possible values if relevant, would help some.
– RDFozz
Jun 14 '17 at 16:48
To clarify: There have been two changes from 'NO' to 'YES', and two changes from 'YES' to 'NO'. That's a total of 4 changes. Are you only interested in changes in 1 direction, or perhaps only interested in round trips ('NO' => 'YES' => 'NO'). Also, are all sensors binary, or could some changes involve more than two possible values? Sample desired output, showing handling for more than two possible values if relevant, would help some.
– RDFozz
Jun 14 '17 at 16:48
add a comment |
1 Answer
1
active
oldest
votes
0
Using Sql Server,
declare @t table(id int,col1 varchar(30), col2 varchar(30),col3 datetime)
insert into @t (id,col1,col2,col3) VALUES
(1 , 'A0001' , 'NO', '2017-06-14 02:03:26.0')
,(2 , 'A0001' , 'NO', '2017-06-15 02:03:26.0')
,(3 , 'A0001' , 'YES','2017-06-16 02:03:26.0')
,(4 , 'A0001' , 'NO', '2017-06-17 02:03:26.0')
,(5 , 'A0001' , 'YES','2017-06-18 02:03:26.0')
,(6 , 'A0001', 'NO' ,'2017-06-19 02:03:26.0')
;With CTE1 as
(
select *
,ROW_NUMBER()over(PARTITION by col1 order by col3)rn from @t
)
, cte
AS (
SELECT
rn
,col1
,col2
,col3
,0 changecount
FROM cte1
where rn=1
UNION ALL
SELECT
t.rn
,t.col1
,t.col2
,t.col3
,CASE
WHEN t.col2 <> t1.col2
THEN changecount + 1
ELSE changecount
END
FROM cte1 t
INNER JOIN cte t1 ON t.col1 = t1.col1
AND t.rn = t1.rn + 1
)
SELECT *
FROM cte
answered Jun 14 '17 at 5:48
KumarHarshKumarHarsh
95859
How does this answer OP if you're usingsql serverand the question is tagged forMySQL?
– Prabhat G
Jun 14 '17 at 8:39
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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
});
}
});
draft saved
draft discarded
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%2fdba.stackexchange.com%2fquestions%2f176227%2ffinding-count-of-occurrences-time-gap-between-a-timeline-in-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Using Sql Server,
declare @t table(id int,col1 varchar(30), col2 varchar(30),col3 datetime)
insert into @t (id,col1,col2,col3) VALUES
(1 , 'A0001' , 'NO', '2017-06-14 02:03:26.0')
,(2 , 'A0001' , 'NO', '2017-06-15 02:03:26.0')
,(3 , 'A0001' , 'YES','2017-06-16 02:03:26.0')
,(4 , 'A0001' , 'NO', '2017-06-17 02:03:26.0')
,(5 , 'A0001' , 'YES','2017-06-18 02:03:26.0')
,(6 , 'A0001', 'NO' ,'2017-06-19 02:03:26.0')
;With CTE1 as
(
select *
,ROW_NUMBER()over(PARTITION by col1 order by col3)rn from @t
)
, cte
AS (
SELECT
rn
,col1
,col2
,col3
,0 changecount
FROM cte1
where rn=1
UNION ALL
SELECT
t.rn
,t.col1
,t.col2
,t.col3
,CASE
WHEN t.col2 <> t1.col2
THEN changecount + 1
ELSE changecount
END
FROM cte1 t
INNER JOIN cte t1 ON t.col1 = t1.col1
AND t.rn = t1.rn + 1
)
SELECT *
FROM cte
answered Jun 14 '17 at 5:48
KumarHarshKumarHarsh
95859
How does this answer OP if you're usingsql serverand the question is tagged forMySQL?
– Prabhat G
Jun 14 '17 at 8:39
add a comment |
0
Using Sql Server,
declare @t table(id int,col1 varchar(30), col2 varchar(30),col3 datetime)
insert into @t (id,col1,col2,col3) VALUES
(1 , 'A0001' , 'NO', '2017-06-14 02:03:26.0')
,(2 , 'A0001' , 'NO', '2017-06-15 02:03:26.0')
,(3 , 'A0001' , 'YES','2017-06-16 02:03:26.0')
,(4 , 'A0001' , 'NO', '2017-06-17 02:03:26.0')
,(5 , 'A0001' , 'YES','2017-06-18 02:03:26.0')
,(6 , 'A0001', 'NO' ,'2017-06-19 02:03:26.0')
;With CTE1 as
(
select *
,ROW_NUMBER()over(PARTITION by col1 order by col3)rn from @t
)
, cte
AS (
SELECT
rn
,col1
,col2
,col3
,0 changecount
FROM cte1
where rn=1
UNION ALL
SELECT
t.rn
,t.col1
,t.col2
,t.col3
,CASE
WHEN t.col2 <> t1.col2
THEN changecount + 1
ELSE changecount
END
FROM cte1 t
INNER JOIN cte t1 ON t.col1 = t1.col1
AND t.rn = t1.rn + 1
)
SELECT *
FROM cte
answered Jun 14 '17 at 5:48
KumarHarshKumarHarsh
95859
How does this answer OP if you're usingsql serverand the question is tagged forMySQL?
– Prabhat G
Jun 14 '17 at 8:39
add a comment |
0
0
0
Using Sql Server,
declare @t table(id int,col1 varchar(30), col2 varchar(30),col3 datetime)
insert into @t (id,col1,col2,col3) VALUES
(1 , 'A0001' , 'NO', '2017-06-14 02:03:26.0')
,(2 , 'A0001' , 'NO', '2017-06-15 02:03:26.0')
,(3 , 'A0001' , 'YES','2017-06-16 02:03:26.0')
,(4 , 'A0001' , 'NO', '2017-06-17 02:03:26.0')
,(5 , 'A0001' , 'YES','2017-06-18 02:03:26.0')
,(6 , 'A0001', 'NO' ,'2017-06-19 02:03:26.0')
;With CTE1 as
(
select *
,ROW_NUMBER()over(PARTITION by col1 order by col3)rn from @t
)
, cte
AS (
SELECT
rn
,col1
,col2
,col3
,0 changecount
FROM cte1
where rn=1
UNION ALL
SELECT
t.rn
,t.col1
,t.col2
,t.col3
,CASE
WHEN t.col2 <> t1.col2
THEN changecount + 1
ELSE changecount
END
FROM cte1 t
INNER JOIN cte t1 ON t.col1 = t1.col1
AND t.rn = t1.rn + 1
)
SELECT *
FROM cte
answered Jun 14 '17 at 5:48
KumarHarshKumarHarsh
95859
Using Sql Server,
declare @t table(id int,col1 varchar(30), col2 varchar(30),col3 datetime)
insert into @t (id,col1,col2,col3) VALUES
(1 , 'A0001' , 'NO', '2017-06-14 02:03:26.0')
,(2 , 'A0001' , 'NO', '2017-06-15 02:03:26.0')
,(3 , 'A0001' , 'YES','2017-06-16 02:03:26.0')
,(4 , 'A0001' , 'NO', '2017-06-17 02:03:26.0')
,(5 , 'A0001' , 'YES','2017-06-18 02:03:26.0')
,(6 , 'A0001', 'NO' ,'2017-06-19 02:03:26.0')
;With CTE1 as
(
select *
,ROW_NUMBER()over(PARTITION by col1 order by col3)rn from @t
)
, cte
AS (
SELECT
rn
,col1
,col2
,col3
,0 changecount
FROM cte1
where rn=1
UNION ALL
SELECT
t.rn
,t.col1
,t.col2
,t.col3
,CASE
WHEN t.col2 <> t1.col2
THEN changecount + 1
ELSE changecount
END
FROM cte1 t
INNER JOIN cte t1 ON t.col1 = t1.col1
AND t.rn = t1.rn + 1
)
SELECT *
FROM cte
answered Jun 14 '17 at 5:48
KumarHarshKumarHarsh
95859
answered Jun 14 '17 at 5:48
KumarHarshKumarHarsh
95859
answered Jun 14 '17 at 5:48
KumarHarshKumarHarsh
95859
answered Jun 14 '17 at 5:48
KumarHarshKumarHarsh
95859
95859
How does this answer OP if you're usingsql serverand the question is tagged forMySQL?
– Prabhat G
Jun 14 '17 at 8:39
add a comment |
How does this answer OP if you're usingsql serverand the question is tagged forMySQL?
– Prabhat G
Jun 14 '17 at 8:39
How does this answer OP if you're using
sql server and the question is tagged for MySQL ?– Prabhat G
Jun 14 '17 at 8:39
How does this answer OP if you're using
sql server and the question is tagged for MySQL ?– Prabhat G
Jun 14 '17 at 8:39
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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.
draft saved
draft discarded
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%2fdba.stackexchange.com%2fquestions%2f176227%2ffinding-count-of-occurrences-time-gap-between-a-timeline-in-mysql%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
To clarify: There have been two changes from 'NO' to 'YES', and two changes from 'YES' to 'NO'. That's a total of 4 changes. Are you only interested in changes in 1 direction, or perhaps only interested in round trips ('NO' => 'YES' => 'NO'). Also, are all sensors binary, or could some changes involve more than two possible values? Sample desired output, showing handling for more than two possible values if relevant, would help some.
– RDFozz
Jun 14 '17 at 16:48