Give IDs to variation on gaps and islands problem
up vote
1
down vote
favorite
This dataset contains one ordered timestamp column (A) along with a pair of marker columns (B + C) that represent the start and end of a 'block', what I'm looking to produce is (D).
I've had a hard time of explaining this problem to colleagues, but essentially I need a way of giving an ID to these blocks of varying row count but note that on row 8 as an example a block can sometimes only occupy one row.
| A | B | C | D |
-----------------------------------------
| 06/10/2018 13:17:40 | 1 | 0 | 1 |
| 06/10/2018 13:17:56 | 0 | 0 | 1 |
| 06/10/2018 13:18:08 | 0 | 1 | 1 |
| 06/10/2018 13:18:21 | 1 | 0 | 2 |
| 06/10/2018 13:18:26 | 0 | 0 | 2 |
| 06/10/2018 13:18:26 | 0 | 0 | 2 |
| 06/10/2018 13:18:28 | 0 | 1 | 2 |
| 06/10/2018 13:18:28 | 1 | 1 | 3 |
| 06/10/2018 13:18:31 | 1 | 0 | 4 |
| 06/10/2018 19:49:26 | 0 | 0 | 4 |
| 06/10/2018 19:50:24 | 0 | 1 | 4 |
sql
azure ssms partition gaps-and-islands edited Nov 11 at 8:40
Mohammad Mohabbati
428312
asked Nov 11 at 4:54
Rob Ruizuki
153
add a comment |
up vote
1
down vote
favorite
This dataset contains one ordered timestamp column (A) along with a pair of marker columns (B + C) that represent the start and end of a 'block', what I'm looking to produce is (D).
I've had a hard time of explaining this problem to colleagues, but essentially I need a way of giving an ID to these blocks of varying row count but note that on row 8 as an example a block can sometimes only occupy one row.
| A | B | C | D |
-----------------------------------------
| 06/10/2018 13:17:40 | 1 | 0 | 1 |
| 06/10/2018 13:17:56 | 0 | 0 | 1 |
| 06/10/2018 13:18:08 | 0 | 1 | 1 |
| 06/10/2018 13:18:21 | 1 | 0 | 2 |
| 06/10/2018 13:18:26 | 0 | 0 | 2 |
| 06/10/2018 13:18:26 | 0 | 0 | 2 |
| 06/10/2018 13:18:28 | 0 | 1 | 2 |
| 06/10/2018 13:18:28 | 1 | 1 | 3 |
| 06/10/2018 13:18:31 | 1 | 0 | 4 |
| 06/10/2018 19:49:26 | 0 | 0 | 4 |
| 06/10/2018 19:50:24 | 0 | 1 | 4 |
sql
azure ssms partition gaps-and-islands edited Nov 11 at 8:40
Mohammad Mohabbati
428312
asked Nov 11 at 4:54
Rob Ruizuki
153
2
I don't understand what you want, could u please add "expected output" to your question
– Eray Balkanli
Nov 11 at 4:57
Hint for you:- LAG()
– Ajan Balakumaran
Nov 11 at 5:10
Sorry I meant to explain that column D here in the example table is what I want to output, I'm interested if there is an alternative method to just using partitioned lag/lead functions
– Rob Ruizuki
Nov 11 at 6:03
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
This dataset contains one ordered timestamp column (A) along with a pair of marker columns (B + C) that represent the start and end of a 'block', what I'm looking to produce is (D).
I've had a hard time of explaining this problem to colleagues, but essentially I need a way of giving an ID to these blocks of varying row count but note that on row 8 as an example a block can sometimes only occupy one row.
| A | B | C | D |
-----------------------------------------
| 06/10/2018 13:17:40 | 1 | 0 | 1 |
| 06/10/2018 13:17:56 | 0 | 0 | 1 |
| 06/10/2018 13:18:08 | 0 | 1 | 1 |
| 06/10/2018 13:18:21 | 1 | 0 | 2 |
| 06/10/2018 13:18:26 | 0 | 0 | 2 |
| 06/10/2018 13:18:26 | 0 | 0 | 2 |
| 06/10/2018 13:18:28 | 0 | 1 | 2 |
| 06/10/2018 13:18:28 | 1 | 1 | 3 |
| 06/10/2018 13:18:31 | 1 | 0 | 4 |
| 06/10/2018 19:49:26 | 0 | 0 | 4 |
| 06/10/2018 19:50:24 | 0 | 1 | 4 |
sql
azure ssms partition gaps-and-islands edited Nov 11 at 8:40
Mohammad Mohabbati
428312
asked Nov 11 at 4:54
Rob Ruizuki
153
This dataset contains one ordered timestamp column (A) along with a pair of marker columns (B + C) that represent the start and end of a 'block', what I'm looking to produce is (D).
I've had a hard time of explaining this problem to colleagues, but essentially I need a way of giving an ID to these blocks of varying row count but note that on row 8 as an example a block can sometimes only occupy one row.
| A | B | C | D |
-----------------------------------------
| 06/10/2018 13:17:40 | 1 | 0 | 1 |
| 06/10/2018 13:17:56 | 0 | 0 | 1 |
| 06/10/2018 13:18:08 | 0 | 1 | 1 |
| 06/10/2018 13:18:21 | 1 | 0 | 2 |
| 06/10/2018 13:18:26 | 0 | 0 | 2 |
| 06/10/2018 13:18:26 | 0 | 0 | 2 |
| 06/10/2018 13:18:28 | 0 | 1 | 2 |
| 06/10/2018 13:18:28 | 1 | 1 | 3 |
| 06/10/2018 13:18:31 | 1 | 0 | 4 |
| 06/10/2018 19:49:26 | 0 | 0 | 4 |
| 06/10/2018 19:50:24 | 0 | 1 | 4 |
sql
azure ssms partition gaps-and-islands sql
azure ssms partition gaps-and-islands edited Nov 11 at 8:40
Mohammad Mohabbati
428312
asked Nov 11 at 4:54
Rob Ruizuki
153
edited Nov 11 at 8:40
Mohammad Mohabbati
428312
asked Nov 11 at 4:54
Rob Ruizuki
153
edited Nov 11 at 8:40
Mohammad Mohabbati
428312
edited Nov 11 at 8:40
Mohammad Mohabbati
428312
edited Nov 11 at 8:40
Mohammad Mohabbati
428312
428312
asked Nov 11 at 4:54
Rob Ruizuki
153
asked Nov 11 at 4:54
Rob Ruizuki
153
asked Nov 11 at 4:54
Rob Ruizuki
153
153
2
I don't understand what you want, could u please add "expected output" to your question
– Eray Balkanli
Nov 11 at 4:57
Hint for you:- LAG()
– Ajan Balakumaran
Nov 11 at 5:10
Sorry I meant to explain that column D here in the example table is what I want to output, I'm interested if there is an alternative method to just using partitioned lag/lead functions
– Rob Ruizuki
Nov 11 at 6:03
add a comment |
2
I don't understand what you want, could u please add "expected output" to your question
– Eray Balkanli
Nov 11 at 4:57
Hint for you:- LAG()
– Ajan Balakumaran
Nov 11 at 5:10
Sorry I meant to explain that column D here in the example table is what I want to output, I'm interested if there is an alternative method to just using partitioned lag/lead functions
– Rob Ruizuki
Nov 11 at 6:03
2
2
I don't understand what you want, could u please add "expected output" to your question
– Eray Balkanli
Nov 11 at 4:57
I don't understand what you want, could u please add "expected output" to your question
– Eray Balkanli
Nov 11 at 4:57
Hint for you:- LAG()
– Ajan Balakumaran
Nov 11 at 5:10
Hint for you:- LAG()
– Ajan Balakumaran
Nov 11 at 5:10
Sorry I meant to explain that column D here in the example table is what I want to output, I'm interested if there is an alternative method to just using partitioned lag/lead functions
– Rob Ruizuki
Nov 11 at 6:03
Sorry I meant to explain that column D here in the example table is what I want to output, I'm interested if there is an alternative method to just using partitioned lag/lead functions
– Rob Ruizuki
Nov 11 at 6:03
add a comment |
2 Answers
2
active
oldest
votes
up vote
0
down vote
accepted
You can try to use LAG window function in subquery then use SUM window function with condition aggregate function.
SELECT A,B,C,SUM(CASE WHEN preC = 1 THEN 1 ELSE 0 END) OVER(ORDER BY A,preC) +1 'D'
FROM (
SELECT *,
LAG(C,1,C) OVER(ORDER BY A) preC
FROM T
) t1
sqlfiddle
Result
| A | B | C | D |
-----------------------------------------
| 06/10/2018 13:17:40 | 1 | 0 | 1 |
| 06/10/2018 13:17:56 | 0 | 0 | 1 |
| 06/10/2018 13:18:08 | 0 | 1 | 1 |
| 06/10/2018 13:18:21 | 1 | 0 | 2 |
| 06/10/2018 13:18:26 | 0 | 0 | 2 |
| 06/10/2018 13:18:26 | 0 | 0 | 2 |
| 06/10/2018 13:18:28 | 0 | 1 | 2 |
| 06/10/2018 13:18:28 | 1 | 1 | 3 |
| 06/10/2018 13:18:31 | 1 | 0 | 4 |
| 06/10/2018 19:49:26 | 0 | 0 | 4 |
| 06/10/2018 19:50:24 | 0 | 1 | 4 |
answered Nov 11 at 8:51
D-Shih
24.3k61431
Thanks very much this works fine
– Rob Ruizuki
Nov 12 at 13:12
No problem glad to help you can accept this question if that help you.
– D-Shih
Nov 12 at 14:30
add a comment |
up vote
0
down vote
I don't see what C has to do with the problem. This is just a cumulative sum on B:
select a, b, c,
sum(b) over (order by a) as d
from t;
answered Nov 11 at 12:18
Gordon Linoff
746k33285390
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
You can try to use LAG window function in subquery then use SUM window function with condition aggregate function.
SELECT A,B,C,SUM(CASE WHEN preC = 1 THEN 1 ELSE 0 END) OVER(ORDER BY A,preC) +1 'D'
FROM (
SELECT *,
LAG(C,1,C) OVER(ORDER BY A) preC
FROM T
) t1
sqlfiddle
Result
| A | B | C | D |
-----------------------------------------
| 06/10/2018 13:17:40 | 1 | 0 | 1 |
| 06/10/2018 13:17:56 | 0 | 0 | 1 |
| 06/10/2018 13:18:08 | 0 | 1 | 1 |
| 06/10/2018 13:18:21 | 1 | 0 | 2 |
| 06/10/2018 13:18:26 | 0 | 0 | 2 |
| 06/10/2018 13:18:26 | 0 | 0 | 2 |
| 06/10/2018 13:18:28 | 0 | 1 | 2 |
| 06/10/2018 13:18:28 | 1 | 1 | 3 |
| 06/10/2018 13:18:31 | 1 | 0 | 4 |
| 06/10/2018 19:49:26 | 0 | 0 | 4 |
| 06/10/2018 19:50:24 | 0 | 1 | 4 |
answered Nov 11 at 8:51
D-Shih
24.3k61431
Thanks very much this works fine
– Rob Ruizuki
Nov 12 at 13:12
No problem glad to help you can accept this question if that help you.
– D-Shih
Nov 12 at 14:30
add a comment |
up vote
0
down vote
accepted
You can try to use LAG window function in subquery then use SUM window function with condition aggregate function.
SELECT A,B,C,SUM(CASE WHEN preC = 1 THEN 1 ELSE 0 END) OVER(ORDER BY A,preC) +1 'D'
FROM (
SELECT *,
LAG(C,1,C) OVER(ORDER BY A) preC
FROM T
) t1
sqlfiddle
Result
| A | B | C | D |
-----------------------------------------
| 06/10/2018 13:17:40 | 1 | 0 | 1 |
| 06/10/2018 13:17:56 | 0 | 0 | 1 |
| 06/10/2018 13:18:08 | 0 | 1 | 1 |
| 06/10/2018 13:18:21 | 1 | 0 | 2 |
| 06/10/2018 13:18:26 | 0 | 0 | 2 |
| 06/10/2018 13:18:26 | 0 | 0 | 2 |
| 06/10/2018 13:18:28 | 0 | 1 | 2 |
| 06/10/2018 13:18:28 | 1 | 1 | 3 |
| 06/10/2018 13:18:31 | 1 | 0 | 4 |
| 06/10/2018 19:49:26 | 0 | 0 | 4 |
| 06/10/2018 19:50:24 | 0 | 1 | 4 |
answered Nov 11 at 8:51
D-Shih
24.3k61431
Thanks very much this works fine
– Rob Ruizuki
Nov 12 at 13:12
No problem glad to help you can accept this question if that help you.
– D-Shih
Nov 12 at 14:30
add a comment |
up vote
0
down vote
accepted
up vote
0
down vote
accepted
You can try to use LAG window function in subquery then use SUM window function with condition aggregate function.
SELECT A,B,C,SUM(CASE WHEN preC = 1 THEN 1 ELSE 0 END) OVER(ORDER BY A,preC) +1 'D'
FROM (
SELECT *,
LAG(C,1,C) OVER(ORDER BY A) preC
FROM T
) t1
sqlfiddle
Result
| A | B | C | D |
-----------------------------------------
| 06/10/2018 13:17:40 | 1 | 0 | 1 |
| 06/10/2018 13:17:56 | 0 | 0 | 1 |
| 06/10/2018 13:18:08 | 0 | 1 | 1 |
| 06/10/2018 13:18:21 | 1 | 0 | 2 |
| 06/10/2018 13:18:26 | 0 | 0 | 2 |
| 06/10/2018 13:18:26 | 0 | 0 | 2 |
| 06/10/2018 13:18:28 | 0 | 1 | 2 |
| 06/10/2018 13:18:28 | 1 | 1 | 3 |
| 06/10/2018 13:18:31 | 1 | 0 | 4 |
| 06/10/2018 19:49:26 | 0 | 0 | 4 |
| 06/10/2018 19:50:24 | 0 | 1 | 4 |
answered Nov 11 at 8:51
D-Shih
24.3k61431
You can try to use LAG window function in subquery then use SUM window function with condition aggregate function.
SELECT A,B,C,SUM(CASE WHEN preC = 1 THEN 1 ELSE 0 END) OVER(ORDER BY A,preC) +1 'D'
FROM (
SELECT *,
LAG(C,1,C) OVER(ORDER BY A) preC
FROM T
) t1
sqlfiddle
Result
| A | B | C | D |
-----------------------------------------
| 06/10/2018 13:17:40 | 1 | 0 | 1 |
| 06/10/2018 13:17:56 | 0 | 0 | 1 |
| 06/10/2018 13:18:08 | 0 | 1 | 1 |
| 06/10/2018 13:18:21 | 1 | 0 | 2 |
| 06/10/2018 13:18:26 | 0 | 0 | 2 |
| 06/10/2018 13:18:26 | 0 | 0 | 2 |
| 06/10/2018 13:18:28 | 0 | 1 | 2 |
| 06/10/2018 13:18:28 | 1 | 1 | 3 |
| 06/10/2018 13:18:31 | 1 | 0 | 4 |
| 06/10/2018 19:49:26 | 0 | 0 | 4 |
| 06/10/2018 19:50:24 | 0 | 1 | 4 |
answered Nov 11 at 8:51
D-Shih
24.3k61431
answered Nov 11 at 8:51
D-Shih
24.3k61431
answered Nov 11 at 8:51
D-Shih
24.3k61431
answered Nov 11 at 8:51
D-Shih
24.3k61431
24.3k61431
Thanks very much this works fine
– Rob Ruizuki
Nov 12 at 13:12
No problem glad to help you can accept this question if that help you.
– D-Shih
Nov 12 at 14:30
add a comment |
Thanks very much this works fine
– Rob Ruizuki
Nov 12 at 13:12
No problem glad to help you can accept this question if that help you.
– D-Shih
Nov 12 at 14:30
Thanks very much this works fine
– Rob Ruizuki
Nov 12 at 13:12
Thanks very much this works fine
– Rob Ruizuki
Nov 12 at 13:12
No problem glad to help you can accept this question if that help you.
– D-Shih
Nov 12 at 14:30
No problem glad to help you can accept this question if that help you.
– D-Shih
Nov 12 at 14:30
add a comment |
up vote
0
down vote
I don't see what C has to do with the problem. This is just a cumulative sum on B:
select a, b, c,
sum(b) over (order by a) as d
from t;
answered Nov 11 at 12:18
Gordon Linoff
746k33285390
add a comment |
up vote
0
down vote
I don't see what C has to do with the problem. This is just a cumulative sum on B:
select a, b, c,
sum(b) over (order by a) as d
from t;
answered Nov 11 at 12:18
Gordon Linoff
746k33285390
add a comment |
up vote
0
down vote
up vote
0
down vote
I don't see what C has to do with the problem. This is just a cumulative sum on B:
select a, b, c,
sum(b) over (order by a) as d
from t;
answered Nov 11 at 12:18
Gordon Linoff
746k33285390
I don't see what C has to do with the problem. This is just a cumulative sum on B:
select a, b, c,
sum(b) over (order by a) as d
from t;
answered Nov 11 at 12:18
Gordon Linoff
746k33285390
answered Nov 11 at 12:18
Gordon Linoff
746k33285390
answered Nov 11 at 12:18
Gordon Linoff
746k33285390
answered Nov 11 at 12:18
Gordon Linoff
746k33285390
746k33285390
add a comment |
add a comment |
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%2f53245960%2fgive-ids-to-variation-on-gaps-and-islands-problem%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
I don't understand what you want, could u please add "expected output" to your question
– Eray Balkanli
Nov 11 at 4:57
Hint for you:- LAG()
– Ajan Balakumaran
Nov 11 at 5:10
Sorry I meant to explain that column D here in the example table is what I want to output, I'm interested if there is an alternative method to just using partitioned lag/lead functions
– Rob Ruizuki
Nov 11 at 6:03