Aggregate & Window Functions

GROUP BY and OVER patterns for complex analyses.

GROUP BY Pattern

SELECT
    group_id,
    result.coefficient[2] as effect
FROM data
GROUP BY group_id
APPLY anofox_statistics_ols_agg(y, ARRAY[x1, x2]);

All models available with _agg suffix:

• anofox_statistics_ols_agg
• anofox_statistics_ridge_agg
• anofox_statistics_wls_agg
• anofox_statistics_rls_agg
• anofox_statistics_elastic_net_agg

---

Window Functions

SELECT
    date,
    result.coefficient[2] as rolling_effect,
    result.r_squared
FROM data
ORDER BY date
WINDOW w AS (ROWS BETWEEN 60 PRECEDING AND CURRENT ROW)
APPLY anofox_statistics_ols_agg(y, ARRAY[x1, x2]) OVER w;

Window Types:

• ROWS BETWEEN N PRECEDING AND CURRENT ROW - Rolling
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW - Expanding

---

Diagnostic Aggregates

All diagnostic functions available with _agg:

• anofox_statistics_vif_agg
• anofox_statistics_normality_test_agg
• anofox_statistics_residual_diagnostics_agg
• anofox_statistics_information_criteria_agg

---

Next Steps

• Utilities — Helper functions
• Grouped Analysis Guide — Examples