使用 Big Query SQL 我可以实现以下减法（减去两行值并将其替换为该值）吗？

发布于 2025-01-19 03:02:17 字数 3048 浏览 1 评论 0原文

应用程序	BuildingID	Dashboarding_tag	值
Abacus	BLD01	Heating_System_Energy_Conspiration	3000.00
Abacus	BLD02	Heating_System_Energy_Conspiration	1000.00
Abacus	BLD04	Heating_System_Energy_Conspiration	1000.00
Abacus	BLD05	Heating_System_Energy_Conspiration	500.00
Abacus	BLD02	MVHR_Energy_Conspiration	300.00
Abacus	BLD03	MVHR_Energy_Conspiration	900.00
Abacus	BLD04	MVHR_Energy_Conspiration	50.00
Abacus	BLD05	MVHR_Energy_Conspiration	80.00
Abacus	BLD06	MVHR_Energy_Conspiration	200.00
Forell	BLD07	Heating_System_Energy_Conspiration	0.00
Forell	BLD08	Heating_System_Energy_Conspiration	50.00
Forell	BLD09	Heating_System_Energy_Conspiration	-5.00
Forell	BLD10	Heating_System_Energy_Conspiration	-30.00

至

应用程序	buildingid	仪表板_标签	值
Abacus	BLD01	Heating_System_Energy_Conspiration	3000.00
Abacus	BLD02	加热_系统_	能源_消耗 700.00
算盘	BLD04	加热_系统_能源_消耗	950.00
算盘	BLD05	加热_系统_能源_消耗	420.00
算盘	BLD02	MVHR_能源_	消耗300.00
算盘	BLD03	MVHR_能源_消耗	900.00
算盘	BLD04	MVHR_Energy_Conspiration	50.00
Abacus	BLD05	MVHR_Energy_Conspiration	80.00
Abacus	BLD06	MVHR_Energy_Conspiration	200.00
Forell	BLD07	Heating_System_Energy_Conspiration	0.00
Forell	BLD08	Heating_System_Energy_Conspiration	50.00
Forell	BLD09	Heating_System_Energy_Conspiration	-5.00
Forell	BLD10	Heating_System_Energy_Conspiration	-30.00

请注意表 2 中第 2、3 和 4 行中的值如何根据第 5、7、8 行中的值进行更改。

逻辑是，如果应用程序是 Abacus ，则始终替换 Heating_System_Energy_Conspiration 的值，如下所示：

Heating_System_Energy_Conspiration=Heating_System_Energy_Conspiration-MVHR_Energy_Conspiration 每栋建筑

原文

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

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

愛上了 2025-01-26 03:02:17

请考虑以下方法

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

如果应用于问题中的示例数据，

- 输出为

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