I am looking for SQL code to do this:
tab_input
| ID | Dat | A |
|---|---|---|
| a | 7 | 3 |
| a | 6 | 4 |
| a | 5 | 1 |
| b | 2 | 5 |
| b | 3 | 9 |
| b | 1 | 2 |
I want to calculate a new column B[i] = A[i] B[i-1], but field B is reset when the ID changes
tab_output
| ID | Dat | A | B |
|---|---|---|---|
| a | 5 | 1 | 1 |
| a | 6 | 4 | 5 |
| a | 7 | 3 | 8 |
| b | 1 | 2 | 2 |
| b | 2 | 5 | 7 |
| b | 3 | 9 | 16 |
I tried the following:
select
A
, A lag( B, 1) OVER( partition by id order by dat ) as B
FROM(
select
ID, A, 0 as B
FROM tab_input
) as base
;
But it doesn't work, the result is
| ID | Dat | A | B |
|---|---|---|---|
| a | 5 | 1 | 1 |
| a | 6 | 4 | 4 |
| a | 7 | 3 | 3 |
| b | 1 | 2 | 2 |
| b | 2 | 5 | 5 |
| b | 3 | 9 | 9 |
I think that it is adding to all the value of 0 and does not update the value of B
I am thank full your help!
CodePudding user response：
LAG looks at one row (per default the one preceding the current row). You create a B that is 0 for every row, then you look at LAG(b), but b is 0 in the previous row, too; it is 0 in all rows. You expect some recursion to kick in, but this is not the case.
What you are looking for is a cumulative sum (SUM OVER):
SELECT id, dat, a, SUM(a) OVER (PARTITION BY id ORDER BY dat) AS b
FROM tab_input
ORDER BY id, dat;