SQL-使用CTE将案例语句结果传递到后续公式/案例语句

Question

我试图使用CTE将案例语句作为输入的结果传递到随后的案例语句，直到汇总到每日值之前。以下代码的目的是：

1. 第一个查询所有相关字段从相关表（Poidata，Interterdata和WeatherSerstationData）加入和分组的COMOON TIMESTAMP_UTC列加入和分组的
2. 所有相关字段中。 [mon]，[day]，[sum_meter_kwh]，[sum_expected_kwh]，[cortailed_energy_kwh]和[inv_count]）
3. 查询从cte_1到cte_2到cte_2中的所有字段，并使用cte_1（[Inv_count]，[sum_meter_kwh]，[[sum_meter_kwh]，[[sum_meter_kwh]，[sum_meter_kwh]，[sum_meth]，[sum_meter_kwh]，[sum_meter_kwh]，[sum_meter_kwh]，[sum_meter_kwh]，[ sum_expected_kwh]和[cortailed_energy_kwh]）要计算CTE_2中的新字段（[availability_invtime（％）]和[EnergeCIC_Avail（％）]）和汇总/组分钟级别的数据到年度，每月，每月，
4. 按CTE_2的所有字段，以及从CTE_2和CTE_2进行订单。年份，月份，日期

为6/7/2022 11:33 AM CST* '''

WITH
    CTE_1 AS
        (SELECT        
                POIData.Timestamp_UTC as [TimeDate],
                SUM(POIData.Meter_KW/60) as [Sum_Meter_KWh],
                SUM(POIData.Park_Potential_KW/60) as [Sum_Expected_KWh],
                SUM(CASE
                        WHEN (POIData.Park_Potential_KW > POIData.Meter_KW)
                            AND POIData.Meter_KW>=0
                            AND POIData.Park_Potential_KW>=0      
                            AND (WeatherStationData.Valid = 1)
                            AND (POIData.Valid = 1)
                            AND (WeatherStationData.GPOA_1 > 50)
                            AND (POIData.Power_Limit_SP < 120000)
                            AND (POIData.Park_Potential_KW > POIData.Power_Limit_SP)
                        THEN (POIData.Park_Potential_KW/60) - (POIData.Meter_KW/60)
                        WHEN (POIData.Park_Potential_KW <= POIData.Meter_KW) OR POIData.Park_Potential_KW<0
                        THEN 0
                        END 
                ) AS [Curtailed_Energy_KWh],
                
                COUNT(CASE
                        WHEN InverterData.Valid = 1
                            AND InverterData.Active_Power > 0
                            AND  WeatherStationData.GPOA_1 > 100
                        THEN InverterData.fkInverterID
                        END
                ) AS [Inv_Count]
                


        FROM        [Comanche].[dbo].POIData INNER JOIN
                    [Comanche].[dbo].InverterData ON POIData.Timestamp_UTC = InverterData.Timestamp_UTC INNER JOIN
                    [Comanche].[dbo].WeatherStationData ON POIData.Timestamp_UTC = WeatherStationData.Timestamp_UTC

        WHERE       DATEADD(HOUR,-6,POIData.Timestamp_UTC) >= '2022-05-01' and DATEADD(HOUR,-6,POIData.Timestamp_UTC) < '2022-06-01'
        GROUP BY    POIData.Timestamp_UTC
    ),
    
    CTE_2 AS
    (
        SELECT  *,
                datepart(YY,DATEADD(HOUR,-6,[TimeDate])) as [Year],
                datepart(MM,DATEADD(HOUR,-6,[TimeDate])) as [Mon],
                datepart(DD,DATEADD(HOUR,-6,[TimeDate])) as [Day],
                AVG(CASE
                        WHEN [Inv_Count]<300
                        THEN [Inv_Count]/300
                        ELSE 1
                    END) AS 'Availability_InvTime(%)',

                (SUM([Sum_Meter_KWh]) + SUM([Curtailed_Energy_KWh]))/SUM([Sum_Expected_KWh]) as 'Energetic_Avail(%)'

        FROM CTE_1
        GROUP BY    
                    datepart(YY,DATEADD(HOUR,-6,[TimeDate])),
                    datepart(MM,DATEADD(HOUR,-6,[TimeDate])),
                    datepart(DD,DATEADD(HOUR,-6,[TimeDate]))
    )


SELECT * FROM CTE_2
                
ORDER BY    --POIData.Timestamp_UTC,
            [Year], [Mon], [Day]

'''

上面的代码会导致以下错误：

msg 8120，级别16，状态1，第1行 列“ cte_1.timedate”在选择列表中无效，因为它不包含在聚合函数中或子句中的组中。

有什么建议吗？谢谢你！

Answer 1

怎么样？：

AVG(CASE WHEN Inv_Count < 300 THEN Inv_Count / 300 ELSE 1 END)
    OVER (PARTITION BY CAST(DATEADD(HOUR, -6, TimeDate]) AS DATE))
    AS "Availability_InvTime(%)",

(
    SUM([Sum_Meter_KWh])         OVER (PARTITION BY CAST(DATEADD(HOUR, -6, TimeDate]) AS DATE))
    +
    SUM([Curtailed_Energy_KWh])) OVER (PARTITION BY CAST(DATEADD(HOUR, -6, TimeDate]) AS DATE))
)   /
    SUM([Sum_Expected_KWh])      OVER (PARTITION BY CAST(DATEADD(HOUR, -6, TimeDate]) AS DATE))
    AS "Energetic_Avail(%)"

小心整数部门。您至少有一个正在进行的人。