I have this table
| ID | Date | Time | 1 | 2 | 3 |
|---|---|---|---|---|---|
| 1 | 1/1/2021 | 10:00 | 3.15 | 1.45 | 0.77 |
| 2 | 1/1/2021 | 11:00 | 0.00 | -0.95 | -0.89 |
| 3 | 1/2/2021 | 05:00 | -0.44 | 1.15 | -0.73 |
| 4 | 1/2/2021 | 06:00 | 0.85 | -1.00 | 0.00 |
| 5 | 1/3/2021 | 14:00 | -1.07 | 0.00 | 2.09 |
| 6 | 1/3/2021 | 15:00 | 2.05 | 1.25 | 0.00 |
I'm trying to create a new table in which I keep the columns ID, Date and Time and for each cell from 1, 2, 3 columns to use the following formula:
Current cell value minus maximum value from the range formed by the first value in the same column and the current value from the same column. So, the desired table looks like this:
| ID | Date | Time | 1.1 | 2.1 |
|---|---|---|---|---|
| 1 | 1/1/2021 | 10:00 | 3.15-Max(3.15) | 1.45-Max(1.45) |
| 2 | 1/1/2021 | 11:00 | 0.00-Max(3.15,0.00) | -0.95-Max(1.45,-0.95) |
| 3 | 1/2/2021 | 05:00 | -0.44-Max(3.15,0.00,-0.44) | 1.15-Max(1.45,-0.95,1.15) |
| 4 | 1/2/2021 | 06:00 | 0.85-Max(3.15,0.00,-0.44,0.85) | -1.00-Max(1.45,-0.95,1.15,-1.00) |
| 5 | 1/3/2021 | 14:00 | -1.07-Max(3.15,0.00,-0.44,0.85,-1.07) | 0.00-Max(1.45,-0.95,1.15,-1.00,0.00) |
| 6 | 1/3/2021 | 15:00 | 2.05-Max(3.15,0.00,-0.44,0.85,-1.07,2.05) | 1.25-Max(1.45,-0.95,1.15,-1.00,0.00,1.25) |
I didn't write the 3rd column from the desired table because of the lack of space, but it's the same format for the 3rd column, too.
My problem is that I can't find the proper syntax for the formula mentioned earlier. I searched for it in all the ways that I could think about, but I couldn't find anything helpful.
Can anyone help me, please?
Any help will be highly appreciated!
Thank you!
P.S. I am new to stackoverflow.com and MySQL, so, please, be kind.
CodePudding user response：
Use the MAX as an analytic function:
SELECT ID, Date, Time, `1`, `2`, `3`,
`1` - MAX(`1`) OVER (ORDER BY ID) AS `1.1`,
`2` - MAX(`2`) OVER (ORDER BY ID) AS `2.1`,
`2` - MAX(`3`) OVER (ORDER BY ID) AS `3.1`
FROM yourTable
ORDER BY ID;
On earlier versions of MySQL which do not support analytic functions, we can use a correlated subquery here:
SELECT ID, Date, Time, `1`, `2`, `3`,
`1` - (SELECT SUM(t2.`1`) FROM yourTable t2 WHERE t2.ID <= t1.ID) AS `1.1`,
`2` - (SELECT SUM(t2.`2`) FROM yourTable t2 WHERE t2.ID <= t1.ID) AS `2.1`,
`3` - (SELECT SUM(t2.`3`) FROM yourTable t2 WHERE t2.ID <= t1.ID) AS `3.1`
FROM yourTable t1
ORDER BY ID;