from the below table:
newID | year | ID | newValore |
---|---|---|---|
1 | 2020 | 111 | 50 |
1 | 2020 | 111 | 60 |
1 | 2021 | 111 | 70 |
1 | 2021 | 112 | 20 |
1 | 2021 | 112 | 40 |
1 | 2022 | 113 | 30 |
1 | 2022 | 113 | 80 |
2 | 2020 | 222 | 20 |
2 | 2020 | 223 | 10 |
2 | 2021 | 223 | 40 |
2 | 2021 | 224 | 10 |
2 | 2021 | 224 | 90 |
2 | 2021 | 224 | 99 |
2 | 2022 | 225 | 10 |
2 | 2023 | 225 | 50 |
given the example table above i need a single query in mysql which creates a new table which will have in the first column the list of newID values and in the second column it will have the different years present in the table for each newID and in the third column i will have a value which is called diff_cum_year given by this rule:
the output table should be like this one:
newID | year | diff_cum_year |
---|---|---|
1 | 2020 | 60 [rule 1 max(50,60)] |
1 | 2021 | 50 [rule 4 max(70)+max(20,40) - 60 (previous value for diff_cum_year)] |
1 | 2022 | 80 [rule 3 max(30,80)] |
2 | 2020 | 30 [rule 1 max(20) + max(10)] |
2 | 2021 | 109 [rule 4 max(40) + max(10,90,99) - 30 (previous value for diff_cum_year)] |
2 | 2022 | 10 |
2 | 2023 | 40 |
There's one tricky way of carrying out this problem. These are the steps followed by this solution:
Each of these operations is done within a separate subquery:
WITH max_vals AS (
SELECT DISTINCT newId,
year_,
ID,
MAX(newValore) OVER(PARTITION BY newID, year_, ID) AS max_value
FROM tab
), sum_max_vals AS (
SELECT *, SUM(max_value) OVER(PARTITION BY newId, year_) AS sum_max_value
FROM max_vals
), sum_max_vals_with_subs AS(
SELECT newID,
year_,
sum_max_value -
CASE WHEN LAG(year_) OVER(PARTITION BY ID ORDER BY year_) = year_-1
THEN LAG(sum_max_value) OVER(PARTITION BY ID ORDER BY year_)
ELSE 0
END AS diff_cum_year
FROM sum_max_vals
)
SELECT newID,
year_,
MIN(diff_cum_year) AS diff_cum_year
FROM sum_max_vals_with_subs
GROUP BY newID, year_
Check the demo here.