I have a Demo table
CREATE TABLE `Demo` (
`id` int(11) NOT NULL,
`seq` int(11) NOT NULL,
`old_status` int(11) NOT NULL,
`new_status` int(11) NOT NULL
)
... and the demo data
INSERT INTO `Demo` (`id`, `seq`, `old_status`, `new_status`) VALUES
(1, 1, 1, 2),
(1, 2, 2, 3),
(1, 3, 3, 9),
(1, 4, 9, 2),
(1, 5, 2, 3),
(2, 1, 1, 2),
(2, 2, 2, 3);
Demo table look like
| id | seq | old_status | new_status |
|---|---|---|---|
| 1 | 1 | 1 | 2 |
| 1 | 2 | 2 | 3 |
| 1 | 3 | 3 | 9 |
| 1 | 4 | 9 | 2 |
| 1 | 5 | 2 | 3 |
| 2 | 1 | 1 | 2 |
| 2 | 2 | 2 | 3 |
I want to add a column that hold the version by group with condition that if we meet the new_status = 9 then increase the previous by one by each group of id.
The expected results:
| id | seq | old_status | new_status | _version |
|---|---|---|---|---|
| 1 | 1 | 1 | 2 | 1 |
| 1 | 2 | 2 | 3 | 1 |
| 1 | 3 | 3 | 9 | 2 |
| 1 | 4 | 9 | 2 | 2 |
| 1 | 5 | 2 | 3 | 2 |
| 2 | 1 | 1 | 2 | 1 |
| 2 | 2 | 2 | 3 | 1 |
I have tried to use LAG function to get my result but something went wrong!
SELECT id, old_status, new_status,
case
when new_status <> 9 then Lag(_version, 1) Over(PARTITION by id ORDER by seq)
else Lag(_version, 1) Over(PARTITION by id ORDER by seq) 1
end _version
from
(select id, old_status, new_status, 1 as _version, seq
from Demo
order by id, seq) result
How can I calculate _version directly on exists _version column or are there any another approaches.
CodePudding user response：
I don't think you can do this from a computed column, but you can use SUM() here as an analytic function:
SELECT *, SUM(new_status = 9) OVER (PARTITION BY id ORDER BY seq) 1 AS _version
FROM Demo
ORDER BY id, seq;