Azure Synapse中PySpark的BlockTime变量未被识别问题求助

我正尝试在Azure Synapse中使用PySpark运行基于临时视图的SQL查询，所有数据已加载完成，前两个查询运行正常。但最后这个查询需要使用Stage、BlockTime、FuelPerGallon和LoadFactor变量进行计算，不清楚需要做哪些调整才能让查询正常运行。

当前代码如下（注：此环境不支持DECLARE语句）：

%%sql

Set Stage=2500.0;
Set BlockTime = Stage/450.0 + .6;
Set FuelPerGallon = 3.07;
Set LoadFactor =.85;

With NarrowbodyMTOWShare as
(
    Select (Sum(T1_By_Year_Quarter_Carrier.Departures)-Sum(T2_BY_YEAR_QUARTER_CARRIER_NARROWBODY.DEPARTURES))*380000 as WidebodyMTOW ,
    Sum(T2_BY_YEAR_QUARTER_CARRIER_NARROWBODY.DEPARTURES) *183000 as NarrowBodyMTOW,
    (Sum(T2_BY_YEAR_QUARTER_CARRIER_NARROWBODY.DEPARTURES) *183000)/((Sum(T2_BY_YEAR_QUARTER_CARRIER_NARROWBODY.DEPARTURES) *183000)+(Sum(T1_By_Year_Quarter_Carrier.Departures)-Sum(T2_BY_YEAR_QUARTER_CARRIER_NARROWBODY.DEPARTURES))*380000) as PercentNarrowbody,
    T1_By_Year_Quarter_Carrier.Year,
    T1_By_Year_Quarter_Carrier.Quarter,T1_By_Year_Quarter_Carrier.Carrier
    From T1_By_Year_Quarter_Carrier
    Left Join T2_BY_YEAR_QUARTER_CARRIER_NARROWBODY ON
    T1_By_Year_Quarter_Carrier.Carrier=T2_BY_YEAR_QUARTER_CARRIER_NARROWBODY.Carrier
    and T1_By_Year_Quarter_Carrier.Year=T2_BY_YEAR_QUARTER_CARRIER_NARROWBODY.Year
    and T1_By_Year_Quarter_Carrier.Quarter = T2_BY_YEAR_QUARTER_CARRIER_NARROWBODY.Quarter
    Group by T1_By_Year_Quarter_Carrier.Year,T1_By_Year_Quarter_Carrier.Quarter,T1_By_Year_Quarter_Carrier.Carrier
),
Interest AS
(
    Select Sum(InterestExpense) as Interest, Sum(InterestExpense)*Max(PercentNarrowbody) as NarrowbodyInterest,
    (Sum(AircraftTrafficServicing)-Sum(LandingFees))*MAX(PercentNarrowbody) as NarrowbodyAircraftTrafficServicing,
    Sum(LandingFees)*Max(PercentNarrowbody) as NarrowbodyLandingFees,
    (Sum(CommissionsCargo)/Sum(REVTONMILES)) as CommissionsCargo,
    (Sum(CommissionsPax)/Sum(RPM)) as CommissionsPax,
    (Sum(Food)/(Sum(ONBOARD_PAX)*(Sum(BLOCKHOURS)/Sum(Departures)))) as Food,
    (Sum(PassengerServicing)-Sum(Food))/(Sum(ONBOARD_PAX)*(Sum(BLOCKHOURS)/Sum(Departures))) as Inflight,
    (Sum(ReservationsandSales)-sum(CommissionsPax)-sum(CommissionsCargo)-sum(Advertsising))/Sum(ONBOARD_PAX) as ResAndSales,
    Sum(Advertsising)/Sum(ONBOARD_PAX) as Advertising,
    Sum(GeneralAdmin)/Sum(ASM) as GeneralAdmin,
    ((Sum(Maintenance) - Sum(Mx))*Max(PercentNarrowbody))/Sum(Departures) as GroundMX,
    P12_By_Year_Quarter_Carrier.Year,P12_By_Year_Quarter_Carrier.Quarter,P12_By_Year_Quarter_Carrier.Carrier
    From P12_By_Year_Quarter_Carrier
    Left Join NarrowbodyMTOWShare on NarrowbodyMTOWShare.Carrier=P12_By_Year_Quarter_Carrier.Carrier and NarrowbodyMTOWShare.Year=P12_By_Year_Quarter_Carrier.Year
    and P12_By_Year_Quarter_Carrier.Quarter=NarrowbodyMTOWShare.Quarter
    Left Join P6_BY_YEAR_QUARTER_CARRIER on P12_BY_YEAR_QUARTER_CARRIER.Carrier=P6_BY_YEAR_QUARTER_CARRIER.Carrier
    and P12_BY_YEAR_QUARTER_CARRIER.Year=P6_BY_YEAR_QUARTER_CARRIER.Year
    and P12_BY_YEAR_QUARTER_CARRIER.Quarter=P6_BY_YEAR_QUARTER_CARRIER.Quarter
    Left Join P52_BY_YEAR_QUARTER_CARRIER on P52_BY_YEAR_QUARTER_CARRIER.Carrier=P12_BY_YEAR_QUARTER_CARRIER.Carrier
    and P52_BY_YEAR_QUARTER_CARRIER.Year=P12_BY_YEAR_QUARTER_CARRIER.Year
    and P52_BY_YEAR_QUARTER_CARRIER.Quarter=P12_BY_YEAR_QUARTER_CARRIER.Quarter
    Left JOIN T1_By_Year_Quarter_Carrier on T1_By_Year_Quarter_Carrier.Carrier=P12_BY_YEAR_QUARTER_CARRIER.Carrier
    and T1_By_Year_Quarter_Carrier.Year=P12_BY_YEAR_QUARTER_CARRIER.Year
    and T1_By_Year_Quarter_Carrier.Quarter=P12_BY_YEAR_QUARTER_CARRIER.Quarter
    Group By P12_By_Year_Quarter_Carrier.Year,P12_By_Year_Quarter_Carrier.Quarter,P12_By_Year_Quarter_Carrier.Carrier
)

SELECT (FlightCrewCost/BLOCKHOURS)*BlockTime as PilotCost,
((MX*.6)/DEPARTURES)+((MX*.4)/BLOCKHOURS)*BlockTime as MXCost,
(Insurance/BLOCKHOURS)*BlockTime as InsuranceCost,
(OtherDirectCost/BLOCKHOURS)*BlockTime as OtherDirectCost,
(Gallons/BLOCKHOURS)*FuelPerGallon as FuelPerCost,
(((Rent+DepAmort+NarrowbodyInterest)*.5)/Departures)+ (((Rent+DepAmort+NarrowbodyInterest)*.5)/BlockHours)*BlockTime as OwnershipCost,
NarrowbodyAircraftTrafficServicing/DEPARTURES as AircraftAndTrafficServicingCost,
NarrowbodyLandingFees/Departures as LandingFeesCost,
CommissionsCargo*(NonPaxRTMPerDeparture+((Stage*(SeatsPerDeparture*LoadFactor)*35.0)/2000.0)) as CargoCommissionCost,
CommissionsPax*(Stage*(SeatsPerDeparture*LoadFactor)) as PaxCommissionCost,
Food * (SeatsPerDeparture*LoadFactor*BlockTime) as FoodCost,
Inflight * (SeatsPerDeparture*LoadFactor*BlockTime) as InflightCost,
GroundMX as GroundMXCost,
ResAndSales*(SeatsPerDeparture*LoadFactor) as ResAndSalesCost,
Advertising*(SeatsPerDeparture*LoadFactor) as AdvertisingCost,
GeneralAdmin*(SeatsPerDeparture*Stage) as GeneralAdminCost,
P52_BY_YEAR_QUARTER_CARRIER_NARROWBODY.Year
,T2_BY_YEAR_QUARTER_CARRIER_NARROWBODY.Quarter
,P52_BY_YEAR_QUARTER_CARRIER_NARROWBODY.Carrier
,BlockTime as BlockTime,
Stage as StageLength,
SeatsPerDeparture
 FROM P52_BY_YEAR_QUARTER_CARRIER_NARROWBODY
 Left Join T2_BY_YEAR_QUARTER_CARRIER_NARROWBODY on T2_BY_YEAR_QUARTER_CARRIER_NARROWBODY.Carrier=P52_BY_YEAR_QUARTER_CARRIER_NARROWBODY.Carrier and 
P52_BY_YEAR_QUARTER_CARRIER_NARROWBODY.Year=T2_BY_YEAR_QUARTER_CARRIER_NARROWBODY.Year and 
P52_BY_YEAR_QUARTER_CARRIER_NARROWBODY.Quarter = T2_BY_YEAR_QUARTER_CARRIER_NARROWBODY.Quarter
Left Join Interest ON
Interest.Year= P52_BY_YEAR_QUARTER_CARRIER_NARROWBODY.Year and Interest.Quarter=P52_BY_YEAR_QUARTER_CARRIER_NARROWBODY.Quarter
and Interest.Carrier=P52_BY_YEAR_QUARTER_CARRIER_NARROWBODY.Carrier
Where P52_BY_YEAR_QUARTER_CARRIER_NARROWBODY.Year=2022   and P52_BY_YEAR_QUARTER_CARRIER_NARROWBODY.Quarter=1

报错信息 #

分析器错误：无法在给定的输入列中解析'BlockTime'；同理'Stage'、'FuelPerGallon'、'LoadFactor'也无法被识别。

调整方案 #

Azure Synapse PySpark SQL的SET语句仅用于配置会话参数，不能定义自定义计算变量，因此需要用以下方式替换变量定义：

方法1：用CTE定义常量并关联

将变量封装为单记录的CTE，通过笛卡尔积关联到主查询中，确保变量能被正常引用：

%%sql

WITH Constants AS (
    SELECT 2500.0 AS Stage,
           2500.0/450.0 + 0.6 AS BlockTime,
           3.07 AS FuelPerGallon,
           0.85 AS LoadFactor
),
NarrowbodyMTOWShare as
(
    -- 原NarrowbodyMTOWShare逻辑不变
    Select (Sum(T1_By_Year_Quarter_Carrier.Departures)-Sum(T2_BY_YEAR_QUARTER_CARRIER_NARROWBODY.DEPARTURES))*380000 as WidebodyMTOW ,
    Sum(T2_BY_YEAR_QUARTER_CARRIER_NARROWBODY.DEPARTURES) *183000 as NarrowBodyMTOW,
    (Sum(T2_BY_YEAR_QUARTER_CARRIER_NARROWBODY.DEPARTURES) *183000)/((Sum(T2_BY_YEAR_QUARTER_CARRIER_NARROWBODY.DEPARTURES) *183000)+(Sum(T1_By_Year_Quarter_Carrier.Departures)-Sum(T2_BY_YEAR_QUARTER_CARRIER_NARROWBODY.DEPARTURES))*380000) as PercentNarrowbody,
    T1_By_Year_Quarter_Carrier.Year,
    T1_By_Year_Quarter_Carrier.Quarter,T1_By_Year_Quarter_Carrier.Carrier
    From T1_By_Year_Quarter_Carrier
    Left Join T2_BY_YEAR_QUARTER_CARRIER_NARROWBODY ON
    T1_By_Year_Quarter_Carrier.Carrier=T2_BY_YEAR_QUARTER_CARRIER_NARROWBODY.Carrier
    and T1_By_Year_Quarter_Carrier.Year=T2_BY_YEAR_QUARTER_CARRIER_NARROWBODY.Year
    and T1_By_Year_Quarter_Carrier.Quarter = T2_BY_YEAR_QUARTER_CARRIER_NARROWBODY.Quarter
    Group by T1_By_Year_Quarter_Carrier.Year,T1_By_Year_Quarter_Carrier.Quarter,T1_By_Year_Quarter_Carrier.Carrier
),
Interest AS
(
    -- 原Interest逻辑不变
    Select Sum(InterestExpense) as Interest, Sum(InterestExpense)*Max(PercentNarrowbody) as NarrowbodyInterest,
    (Sum(AircraftTrafficServicing)-Sum(LandingFees))*MAX(PercentNarrowbody) as NarrowbodyAircraftTrafficServicing,
    Sum(LandingFees)*Max(PercentNarrowbody) as NarrowbodyLandingFees,
    (Sum(CommissionsCargo)/Sum(REVTONMILES)) as CommissionsCargo,
    (Sum(CommissionsPax)/Sum(RPM)) as CommissionsPax,
    (Sum(Food)/(Sum(ONBOARD_PAX)*(Sum(BLOCKHOURS)/Sum(Departures)))) as Food,
    (Sum(PassengerServicing)-Sum(Food))/(Sum(ONBOARD_PAX)*(Sum(BLOCKHOURS)/Sum(Departures))) as Inflight,
    (Sum(ReservationsandSales)-sum(CommissionsPax)-sum(CommissionsCargo)-sum(Advertsising))/Sum(ONBOARD_PAX) as ResAndSales,
    Sum(Advertsising)/Sum(ONBOARD_PAX) as Advertising,
    Sum(GeneralAdmin)/Sum(ASM) as GeneralAdmin,
    ((Sum(Maintenance) - Sum(Mx))*Max(PercentNarrowbody))/Sum(Departures) as GroundMX,
    P12_By_Year_Quarter_Carrier.Year,P12_By_Year_Quarter_Carrier.Quarter,P12_By_Year_Quarter_Carrier.Carrier
    From P12_By_Year_Quarter_Carrier
    Left Join NarrowbodyMTOWShare on NarrowbodyMTOWShare.Carrier=P12_By_Year_Quarter_Carrier.Carrier and NarrowbodyMTOWShare.Year=P12_By_Year_Quarter_Carrier.Year
    and P12_By_Year_Quarter_Carrier.Quarter=NarrowbodyMTOWShare.Quarter
    Left Join P6_BY_YEAR_QUARTER_CARRIER on P12_BY_YEAR_QUARTER_CARRIER.Carrier=P6_BY_YEAR_QUARTER_CARRIER.Carrier
    and P12_BY_YEAR_QUARTER_CARRIER.Year=P6_BY_YEAR_QUARTER_CARRIER.Year
    and P12_BY_YEAR_QUARTER_CARRIER.Quarter=P6_BY_YEAR_QUARTER_CARRIER.Quarter
    Left Join P52_BY_YEAR_QUARTER_CARRIER on P52_BY_YEAR_QUARTER_CARRIER.Carrier=P12_BY_YEAR_QUARTER_CARRIER.Carrier
    and P52_BY_YEAR_QUARTER_CARRIER.Year=P12_BY_YEAR_QUARTER_CARRIER.Year
    and P52_BY_YEAR_QUARTER_CARRIER.Quarter=P12_BY_YEAR_QUARTER_CARRIER.Quarter
    Left JOIN T1_By_Year_Quarter_Carrier on T1_By_Year_Quarter_Carrier.Carrier=P12_BY_YEAR_QUARTER_CARRIER.Carrier
    and T1_By_Year_Quarter_Carrier.Year=P12_BY_YEAR_QUARTER_CARRIER.Year
    and T1_By_Year_Quarter_Carrier.Quarter=P12_BY_YEAR_QUARTER_CARRIER.Quarter
    Group By P12_By_Year_Quarter_Carrier.Year,P12_By_Year_Quarter_Carrier.Quarter,P12_By_Year_Quarter_Carrier.Carrier
)

SELECT (FlightCrewCost/BLOCKHOURS)*c.BlockTime as PilotCost,
((MX*.6)/DEPARTURES)+((MX*.4)/BLOCKHOURS)*c.BlockTime as MXCost,
(Insurance/BLOCKHOURS)*c.BlockTime as InsuranceCost,
(OtherDirectCost/BLOCKHOURS)*c.BlockTime as OtherDirectCost,
(Gallons/BLOCKHOURS)*c.FuelPerGallon as FuelPerCost,
(((Rent+DepAmort+NarrowbodyInterest)*.5)/Departures)+ (((Rent+DepAmort+NarrowbodyInterest)*.5)/BlockHours)*c.BlockTime as OwnershipCost,
NarrowbodyAircraftTrafficServicing/DEPARTURES as AircraftAndTrafficServicingCost,
NarrowbodyLandingFees/Departures as LandingFeesCost,
CommissionsCargo*(NonPaxRTMPerDeparture+((c.Stage*(SeatsPerDeparture*c.LoadFactor)*35.0)/2000.0)) as CargoCommissionCost,
CommissionsPax*(c.Stage*(SeatsPerDeparture*c.LoadFactor)) as PaxCommissionCost,
Food * (SeatsPerDeparture*c.LoadFactor*c.BlockTime) as FoodCost,
Inflight * (SeatsPerDeparture*c.LoadFactor*c.BlockTime) as InflightCost,
GroundMX as