SQL技术问询：筛选4-9月支出总额超平均的费用类型

核心思路 #

1. 筛选4-9月的支出数据，按Expense_Type分组计算每个类型的总支出
2. 计算所有类型总支出的平均值
3. 筛选出总支出高于该平均值的费用类型

正确SQL语句（CTE写法） #

WITH expense_totals AS (
    SELECT 
        Expense_Type,
        SUM(Amount) AS total_spend
    FROM mydatatable
    WHERE EXTRACT(MONTH FROM Date_Of_Payment) BETWEEN 4 AND 9
    GROUP BY Expense_Type
),
average_total AS (
    SELECT AVG(total_spend) AS avg_total
    FROM expense_totals
)
SELECT et.Expense_Type, et.total_spend
FROM expense_totals et
CROSS JOIN average_total at
WHERE et.total_spend > at.avg_total;

语句说明 #

• expense_totals：提取4-9月数据，按费用类型分组求和，得到每个类型的半年总支出
• average_total：基于上述结果，计算所有类型总支出的平均值
• 主查询：关联两个结果集，筛选出总支出高于平均值的费用类型，同时展示对应的总支出

简洁替代写法（窗口函数） #

如果支持窗口函数，可直接在分组时计算全局平均值，无需额外子查询：

SELECT Expense_Type, total_spend
FROM (
    SELECT 
        Expense_Type,
        SUM(Amount) AS total_spend,
        AVG(SUM(Amount)) OVER () AS avg_total
    FROM mydatatable
    WHERE EXTRACT(MONTH FROM Date_Of_Payment) BETWEEN 4 AND 9
    GROUP BY Expense_Type
) sub
WHERE total_spend > avg_total;

内容的提问来源于stack exchange，提问作者Ammara Rasheed