| application | buildingid | dashboarding_tag | value |
|---|---|---|---|
| Abacus | BLD01 | Heating_System_Energy_Consumption | 3000.00 |
| Abacus | BLD02 | Heating_System_Energy_Consumption | 1000.00 |
| Abacus | BLD04 | Heating_System_Energy_Consumption | 1000.00 |
| Abacus | BLD05 | Heating_System_Energy_Consumption | 500.00 |
| Abacus | BLD02 | MVHR_Energy_Consumption | 300.00 |
| Abacus | BLD03 | MVHR_Energy_Consumption | 900.00 |
| Abacus | BLD04 | MVHR_Energy_Consumption | 50.00 |
| Abacus | BLD05 | MVHR_Energy_Consumption | 80.00 |
| Abacus | BLD06 | MVHR_Energy_Consumption | 200.00 |
| Forell | BLD07 | Heating_System_Energy_Consumption | 0.00 |
| Forell | BLD08 | Heating_System_Energy_Consumption | 50.00 |
| Forell | BLD09 | Heating_System_Energy_Consumption | -5.00 |
| Forell | BLD10 | Heating_System_Energy_Consumption | -30.00 |
to
| application | buildingid | dashboarding_tag | value |
|---|---|---|---|
| Abacus | BLD01 | Heating_System_Energy_Consumption | 3000.00 |
| Abacus | BLD02 | Heating_System_Energy_Consumption | 700.00 |
| Abacus | BLD04 | Heating_System_Energy_Consumption | 950.00 |
| Abacus | BLD05 | Heating_System_Energy_Consumption | 420.00 |
| Abacus | BLD02 | MVHR_Energy_Consumption | 300.00 |
| Abacus | BLD03 | MVHR_Energy_Consumption | 900.00 |
| Abacus | BLD04 | MVHR_Energy_Consumption | 50.00 |
| Abacus | BLD05 | MVHR_Energy_Consumption | 80.00 |
| Abacus | BLD06 | MVHR_Energy_Consumption | 200.00 |
| Forell | BLD07 | Heating_System_Energy_Consumption | 0.00 |
| Forell | BLD08 | Heating_System_Energy_Consumption | 50.00 |
| Forell | BLD09 | Heating_System_Energy_Consumption | -5.00 |
| Forell | BLD10 | Heating_System_Energy_Consumption | -30.00 |
Notice how the values in row 2 ,3 and 4 changed on table 2 based on values in row 5,7,8.
The logic is , if the application is Abacus , then always replace the value of the Heating_System_Energy_Consumption such that:
Heating_System_Energy_Consumption=Heating_System_Energy_Consumption-MVHR_Energy_Consumption for each building
CodePudding user response：
Consider below approach
select * except(value),
if(dashboarding_tag = 'Heating_System_Energy_Consumption',
value - sum(if(dashboarding_tag = 'MVHR_Energy_Consumption', value, 0)) over(partition by application, buildingid),
value) as value
from your_table
if applied to sample data in your question - output is
