Renumbering consecutive values
I have a table where I have the dates and results of the game (0 means "to lose" and "1 to win")
+------+--------------+--------+
| id | game_date | result |
+------+--------------+--------+
| 1 | '2016-09-01' | 1 |
| 2 | '2016-09-02' | 1 |
| 3 | '2016-09-03' | 0 |
| 4 | '2016-09-04' | 1 |
| 5 | '2016-09-04' | 1 |
| 6 | '2016-09-04' | 1 |
| 7 | '2016-09-05' | 1 |
| 8 | '2016-09-06' | 0 |
| 9 | '2016-09-07' | 1 |
I need to get all wins where wins are sequential (sorted by date) and renumbered from 1 to last win.
The result should be as follows:
+------+--------------+--------+------------
| id | game_date | result |
+------+--------------+--------+-------------
| 1 | '2016-09-01' | 1 | 1
| 2 | '2016-09-02' | 1 | 2
| 3 | '2016-09-03' | 0 |
| 4 | '2016-09-04' | 1 | 1
| 5 | '2016-09-04' | 1 | 2
| 6 | '2016-09-04' | 1 | 3
| 7 | '2016-09-05' | 1 | 4
| 8 | '2016-09-06' | 0 |
| 9 | '2016-09-07' | 1 | 1
+3
source to share
2 answers
You can do this by specifying groups of contiguous values. The easy way is to distinguish line numbers. Another method is to assign a maximum date for each value "0":
select id, game_date, result,
(case when result = 1
then row_number() over (partition by result, max_gamedate order by id)
end) as newcol
from (select t.*,
max(case when result = 0 then game_date end) over
(order by id) as max_gamedate
from t
) t
+3
source to share
DECLARE @games TABLE (
id INT,
game_date DATE,
result INT
);
INSERT INTO @games
VALUES
(1, '2016-09-01', 1),
(2, '2016-09-02', 1),
(3, '2016-09-03', 0),
(4, '2016-09-04', 1),
(5, '2016-09-04', 1),
(6, '2016-09-04', 1),
(7, '2016-09-05', 1),
(8, '2016-09-06', 0),
(9, '2016-09-07', 1);
WITH CTE AS(
select t3.*, COUNT(collect) OVER(PARTITION BY collect) as collect_count FROM (
select t.*, t2.rn, sum(result) over(order by game_date, t.id) as collect from @games t
left join (
SELECT id, row_number() over(order by game_date, id) as rn
FROM @games t where result = 1
) t2
on t.id = t2.id
)t3
)
select CTE.id, CTE.game_date, CTE.result, rn - COALESCE( (SELECT MAX(collect) FROM CTE innert WHERE collect < CTE.rn and collect_count > 1) , 0) sequence_number
FROM CTE
order by game_date, id
0
source to share