PostgreSQL中按月统计多维度数据指标的自定义函数开发需求

嘿，我来帮你搞定这个PostgreSQL按月统计的需求！针对你提到的数值型和分类变量的不同统计要求，我整理了一套实用的方案，不管是单列统计还是批量处理20+列的场景都能hold住，适合你15000+条数据的规模。

1. 先搞定日期分组（按月统计的基础） #

首先你的Date字段格式是DD-MM-YYYY，得先转成PostgreSQL能识别的日期类型，再提取年月作为分组维度：

TO_CHAR(TO_DATE("Date", 'DD-MM-YYYY'), 'YYYY-MM') AS month

所有后续的统计都会基于这个month字段分组。

2. 数值型变量统计（均值、标准差、分位数+NULL频次） #

像score_n、score_p这类数值列，我们直接用PostgreSQL内置的聚合函数就能搞定所有统计项：

• 均值：AVG(col)
• 标准差：用STDDEV_SAMP（样本标准差）或者STDDEV_POP（总体标准差，根据你的需求选）
• 分位数：PERCENTILE_CONT(0.25)是连续型分位数，如果要离散的就用PERCENTILE_DISC
• NULL频次：要么用COUNT(*) - COUNT(col)，要么用SUM(CASE WHEN col IS NULL THEN 1 ELSE 0 END)，后者更直观

举个针对score_n的完整例子：

SELECT
  TO_CHAR(TO_DATE("Date", 'DD-MM-YYYY'), 'YYYY-MM') AS month,
  -- 核心数值统计
  AVG(score_n) AS score_n_avg,
  STDDEV_SAMP(score_n) AS score_n_stddev,
  PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY score_n) AS score_n_p25,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY score_n) AS score_n_p50,
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY score_n) AS score_n_p75,
  -- NULL值统计
  SUM(CASE WHEN score_n IS NULL THEN 1 ELSE 0 END) AS score_n_null_count
FROM your_table
GROUP BY month
ORDER BY month;

3. 分类变量统计（频次+NULL频次） #

比如Reason这类分类列，有两种常见的统计展示方式：

方式1：每个类别单独一行（适合类别多的情况） #

这样能清晰看到每个类别每月的出现次数，同时单独统计NULL的数量：

SELECT
  TO_CHAR(TO_DATE("Date", 'DD-MM-YYYY'), 'YYYY-MM') AS month,
  Reason AS category,
  COUNT(*) AS frequency,
  SUM(CASE WHEN Reason IS NULL THEN 1 ELSE 0 END) OVER (PARTITION BY month) AS reason_null_count
FROM your_table
WHERE Reason IS NOT NULL
GROUP BY month, Reason
UNION ALL
-- 单独加一行统计NULL的情况
SELECT
  TO_CHAR(TO_DATE("Date", 'DD-MM-YYYY'), 'YYYY-MM') AS month,
  'NULL' AS category,
  COUNT(*) AS frequency,
  COUNT(*) AS reason_null_count
FROM your_table
WHERE Reason IS NULL
GROUP BY month
ORDER BY month, category;

方式2：类别转成列（透视表，适合类别少的情况） #

如果你的分类类别不多，比如只有energy_drink和soft_drink，可以把它们转成列，看起来更紧凑：

SELECT
  TO_CHAR(TO_DATE("Date", 'DD-MM-YYYY'), 'YYYY-MM') AS month,
  COUNT(CASE WHEN Reason = 'energy_drink' THEN 1 END) AS energy_drink_count,
  COUNT(CASE WHEN Reason = 'soft_drink' THEN 1 END) AS soft_drink_count,
  SUM(CASE WHEN Reason IS NULL THEN 1 ELSE 0 END) AS reason_null_count
FROM your_table
GROUP BY month
ORDER BY month;

4. 批量处理20+列？用动态SQL省事儿！ #

手动给20+列写统计语句太麻烦了，我给你整个动态SQL的方案，它会自动识别你的表中哪些是数值型列、哪些是分类型列，然后自动生成对应的统计语句：

DO $$
DECLARE
  rec record;
  numeric_cols text[] := '{}';
  categorical_cols text[] := '{}';
  numeric_sql text;
  categorical_sql text;
BEGIN
  -- 先收集所有数值型列（排除Date、id这类非统计列）
  SELECT array_agg(column_name) INTO numeric_cols
  FROM information_schema.columns
  WHERE table_name = 'your_table'
    AND data_type IN ('integer', 'numeric', 'double precision')
    AND column_name NOT IN ('Date', 'id');

  -- 再收集所有分类型列
  SELECT array_agg(column_name) INTO categorical_cols
  FROM information_schema.columns
  WHERE table_name = 'your_table'
    AND data_type IN ('text', 'character varying')
    AND column_name NOT IN ('Date', 'id');

  -- 生成数值型列的统计SQL
  numeric_sql := 'SELECT TO_CHAR(TO_DATE("Date", ''DD-MM-YYYY''), ''YYYY-MM'') AS month';
  FOREACH rec IN ARRAY numeric_cols LOOP
    numeric_sql := numeric_sql || ',
      AVG(' || quote_ident(rec) || ') AS ' || quote_ident(rec || '_avg') || ',
      STDDEV_SAMP(' || quote_ident(rec) || ') AS ' || quote_ident(rec || '_stddev') || ',
      PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY ' || quote_ident(rec) || ') AS ' || quote_ident(rec || '_p25') || ',
      PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ' || quote_ident(rec) || ') AS ' || quote_ident(rec || '_p50') || ',
      PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY ' || quote_ident(rec) || ') AS ' || quote_ident(rec || '_p75') || ',
      SUM(CASE WHEN ' || quote_ident(rec) || ' IS NULL THEN 1 ELSE 0 END) AS ' || quote_ident(rec || '_null_count');
  END LOOP;
  numeric_sql := numeric_sql || ' FROM your_table GROUP BY month ORDER BY month';

  -- 执行数值型统计，先打印SQL看看（可以去掉RAISE NOTICE直接执行）
  RAISE NOTICE 'Numeric columns stats SQL: %', numeric_sql;
  EXECUTE numeric_sql;

  -- 生成分类型列的统计SQL（用每个类别一行的方式）
  categorical_sql := '';
  FOREACH rec IN ARRAY categorical_cols LOOP
    categorical_sql := categorical_sql || '
      SELECT
        TO_CHAR(TO_DATE("Date", ''DD-MM-YYYY''), ''YYYY-MM'') AS month,
        ' || quote_ident(rec) || ' AS category,
        COUNT(*) AS frequency,
        SUM(CASE WHEN ' || quote_ident(rec) || ' IS NULL THEN 1 ELSE 0 END) OVER (PARTITION BY month) AS ' || quote_ident(rec || '_null_count') || '
      FROM your_table
      WHERE ' || quote_ident(rec) || ' IS NOT NULL
      GROUP BY month, ' || quote_ident(rec) || '
      UNION ALL
      SELECT
        TO_CHAR(TO_DATE("Date", ''DD-MM-YYYY''), ''YYYY-MM'') AS month,
        ''NULL'' AS category,
        COUNT(*) AS frequency,
        COUNT(*) AS ' || quote_ident(rec || '_null_count') || '
      FROM your_table
      WHERE ' || quote_ident(rec) || ' IS NULL
      GROUP BY month
      ORDER BY month, category;';
  END LOOP;

  -- 执行分类型统计
  RAISE NOTICE 'Categorical columns stats SQL: %', categorical_sql;
  EXECUTE categorical_sql;
END $$;

记得把your_table换成你实际的表名就行，这个脚本会自动帮你处理所有列，省超多时间！

5. 性能小Tips（针对15000+条数据） #

虽然15000条数据不算大，但如果统计频繁的话，可以做这两个优化：

• 给Date字段建个索引，加速分组：CREATE INDEX idx_your_table_date ON your_table (TO_DATE("Date", 'DD-MM-YYYY'));
• 如果需要经常查统计结果，不如建个物化视图，按月预聚合数据，定期刷新就行，查询速度会快很多。

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