SQL Server中CTE计算有效月份平均利用率及低利用率客户列表求助

问题1：能否用CTE计算客户有使用活动月份的平均利用率？ #

完全可以。CTE（公共表表达式）非常适合这类先处理数据、再分组聚合的场景，能让逻辑更清晰，避免嵌套子查询的混乱。

问题2：生成2022年12月往前6个月的低利用率客户列表 #

原代码核心问题 #

1. 日期处理错误：Month是float类型的yyyymm格式，直接转datetime会失败，需先转字符串补全日部分再转换；Customer_type错误用业务月份判断，实际应依据客户开户日期Contact_date。
2. 利用率转换失效：Utilization_Rate带%符号，直接用TRY_CONVERT无法转成decimal，必须先去除%再转换。
3. 分组逻辑错误：AVERAGE CTE按Cust_ID和Utilization_Rate分组，无法计算客户整体平均利用率；且未过滤无活动的月份（如Null值）。
4. 关联逻辑混乱：多表关联未做有效过滤，会产生重复数据。

修正后的SQL代码 #

WITH ProcessedData AS (
    -- 数据清洗与转换，过滤有效记录
    SELECT
        Cust_ID,
        -- 转换开户日期为标准日期格式
        TRY_CONVERT(DATE, CONTACT_DATE, 103) AS Contact_Date,
        -- 转换利用率：去除%符号，转成小数
        CASE 
            WHEN Utilization_Rate IS NOT NULL AND Utilization_Rate <> '' 
            THEN TRY_CONVERT(DECIMAL(5,2), REPLACE(Utilization_Rate, '%', '')) / 100 
            ELSE NULL 
        END AS Monthly_Utilization,
        -- 保留原始业务月份数值
        Month AS Business_Month,
        -- 转换业务月份为日期用于验证
        TRY_CONVERT(DATE, CAST(Month AS VARCHAR(6)) + '01', 112) AS Business_Month_Date
    FROM [dbo].['Raw Data$']
    -- 过滤2022年12月往前6个月的范围（202206-202212）
    WHERE Month BETWEEN 202206 AND 202212
    -- 仅保留有使用活动的月份：利用率非空且转换有效
    AND TRY_CONVERT(DECIMAL(5,2), REPLACE(Utilization_Rate, '%', '')) IS NOT NULL
),
CustomerAvgUtil AS (
    -- 计算客户有活动月份的平均利用率，筛选低利用率客户
    SELECT
        Cust_ID,
        AVG(Monthly_Utilization) AS Avg_Utilization
    FROM ProcessedData
    GROUP BY Cust_ID
    HAVING AVG(Monthly_Utilization) < 0.5
)
-- 关联数据输出最终结果
SELECT
    pd.Cust_ID,
    FORMAT(pd.Contact_Date, 'dd/MM/yyyy') AS Contact_Date,
    -- 判断客户类型：2023年开户为NTB，其余为ETB
    CASE
        WHEN YEAR(pd.Contact_Date) = 2023 THEN 'NTB'
        ELSE 'ETB'
    END AS Customer_Type,
    -- 格式化显示每月利用率为百分比
    FORMAT(pd.Monthly_Utilization, 'P1') AS Monthly_Utilization_Rate,
    -- 格式化显示平均利用率为百分比
    FORMAT(cau.Avg_Utilization, 'P1') AS Avg_Utilization_Rate
FROM ProcessedData pd
INNER JOIN CustomerAvgUtil cau ON pd.Cust_ID = cau.Cust_ID
ORDER BY pd.Cust_ID, pd.Business_Month;

代码说明 #

1. ProcessedData CTE：完成数据清洗，转换日期、利用率格式，过滤出指定日期范围内的有效活动记录。
2. CustomerAvgUtil CTE：按客户分组计算平均利用率，筛选出平均利用率低于50%的客户。
3. 最终查询：关联两个CTE，输出所有需求字段，同时将利用率格式化为百分比便于阅读。

内容的提问来源于stack exchange，提问作者minh tue