MariaDB计算偏度与峰度报错：GROUP函数无效问题修复咨询

错误根源 #

报错Invalid use of group function的核心是聚合函数嵌套调用：在计算偏度、峰度的表达式中，AVG(Amount)和STDDEV_POP(Amount)是分组聚合后的结果，无法直接在另一层聚合函数SUM()内部使用。MariaDB要求同一层级的聚合函数不能互相嵌套，必须先计算出分组的基础统计值，再基于这些值进行二次计算。

另外原查询中PERCENTILE_CONT的OVER ()未指定分区，会返回全局分位数而非每个MainClass分组的分位数，这是逻辑错误，需修正。

修复后的查询（含补充统计指标） #

采用子查询预计算分组基础统计量，再关联原表计算高阶统计值，同时补充常用描述性指标：

SELECT
    gs.MainClass,
    gs.count_value,
    gs.min_value,
    gs.max_value,
    gs.mean_value,
    gs.median_value,
    gs.max_value - gs.min_value AS range_value,
    gs.standard_deviation,
    gs.variance,
    gs.percentile_25,
    gs.percentile_75,
    gs.mode_value,
    gs.mean_absolute_deviation,
    gs.percentile_75 - gs.percentile_25 AS iqr, -- 四分位距
    CASE WHEN gs.mean_value <> 0 THEN gs.standard_deviation / gs.mean_value ELSE NULL END AS coefficient_of_variation, -- 变异系数（避免除零）
    -- 总体偏度计算
    (SUM(POWER(cme.Amount - gs.mean_value, 3)) / (gs.count_value * POWER(gs.standard_deviation, 3))) AS skewness,
    -- 总体峰度计算（原始峰度，未减3）
    (SUM(POWER(cme.Amount - gs.mean_value, 4)) / (gs.count_value * POWER(gs.standard_deviation, 4))) AS kurtosis
FROM (
    SELECT
        MainClass,
        COUNT(*) AS count_value,
        MIN(Amount) AS min_value,
        MAX(Amount) AS max_value,
        AVG(Amount) AS mean_value,
        STDDEV_POP(Amount) AS standard_deviation,
        VAR_POP(Amount) AS variance,
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY Amount) OVER (PARTITION BY MainClass) AS percentile_25,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Amount) OVER (PARTITION BY MainClass) AS median_value,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY Amount) OVER (PARTITION BY MainClass) AS percentile_75,
        MODE() WITHIN GROUP (ORDER BY Amount) AS mode_value,
        AVG(ABS(Amount - AVG(Amount))) OVER (PARTITION BY MainClass) AS mean_absolute_deviation
    FROM Expenditure
    WHERE DATE_FORMAT(DATE_ADD(Created, INTERVAL 9 HOUR), '%Y-%m') = '2023-03'
      AND AccountEmail = 'example@meow.com'
    GROUP BY MainClass
) AS gs
JOIN Expenditure AS cme 
    ON gs.MainClass = cme.MainClass
    AND DATE_FORMAT(DATE_ADD(cme.Created, INTERVAL 9 HOUR), '%Y-%m') = '2023-03'
    AND cme.AccountEmail = 'example@meow.com'
GROUP BY gs.MainClass;

补充指标说明 #

• 四分位距（IQR）：反映数据中间50%的离散程度，不受极端值干扰
• 变异系数（CV）：相对离散程度，适合对比不同量级数据集的波动情况
• 众数（Mode）：数据集中出现频率最高的数值
• 均值绝对偏差（MAD）：数据与均值的绝对距离的平均值，比标准差更稳健

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