PAYATHON 2026

PostgreSQL 如何按日期补齐 naim 并计算 pr 合计

数据小陈

结论:不要用 all_num × all_date 当补齐骨架。每个日期分组要补的是 naim 维度,应该用 date_pr × naim 的笛卡尔积左连接 test,再用 UNION ALL 追加每个日期的合计行。合计行的 num、naim、txt 直接置为 NULL,这样 naim_03 就不会出现在汇总行里。

原因

原查询用 CROSS JOIN all_num 和 all_date,补的是所有 num,并没有补所有 naim。当 ROLLUP(date_pr, num) 生成 num IS NULL 的汇总行时,如果选择列表里还带着未参与分组的 naim 或 txt,数据库只能取组内某一行或聚合后的任意值,于是就会出现类似 2025-02-01 | [null] | naim_03 | 38.2906 的行。明细行和合计行最好分开构造;如果继续用 ROLLUP,则要用 GROUPING() 把合计行里的这些列置空。

可用的 PostgreSQL 查询

下面的查询假设 test(date_pr, naim) 唯一。如果同一个 date_pr + naim 可能重复,需要先按 date_pr, naim 聚合,并明确重复时取哪个 num、txt。

WITH dates AS (
    SELECT DISTINCT date_pr
    FROM test
),
names AS (
    SELECT DISTINCT naim
    FROM test
),
grid AS (
    SELECT d.date_pr,
           n.naim,
           t.num,
           t.pr,
           t.txt,
           ROW_NUMBER() OVER (
               PARTITION BY d.date_pr
               ORDER BY (t.num IS NULL), t.num, n.naim
           ) AS rn
    FROM dates d
    CROSS JOIN names n
    LEFT JOIN test t
      ON t.date_pr = d.date_pr
     AND t.naim = n.naim
),
detail AS (
    SELECT date_pr,
           CASE WHEN num IS NOT NULL THEN num ELSE rn::smallint END AS num,
           naim,
           pr,
           txt
    FROM grid
),
total AS (
    SELECT date_pr,
           NULL::smallint AS num,
           NULL::text AS naim,
           SUM(pr) AS pr,
           NULL::text AS txt
    FROM test
    GROUP BY date_pr
),
all_rows AS (
    SELECT date_pr, num, naim, pr, txt FROM detail
    UNION ALL
    SELECT date_pr, num, naim, pr, txt FROM total
)
SELECT ROW_NUMBER() OVER (
           ORDER BY date_pr, num NULLS LAST, naim NULLS LAST
       ) AS rn,
       date_pr,
       num,
       naim,
       pr,
       txt
FROM all_rows
ORDER BY date_pr, num NULLS LAST, naim NULLS LAST;

这段 SQL 有几点需要说明:

  1. dates × names 保证每个日期都有一组完整的 naim,明细行数等于 SELECT COUNT(DISTINCT naim) FROM test。
  2. LEFT JOIN test 只把实际存在的 date_pr + naim 数据带进来,缺失的 pr、txt 会是 NULL。
  3. ROW_NUMBER() 在日期内按 (t.num IS NULL), t.num, n.naim 排序,为缺失的 naim 生成连续的 num;已经有 num 的行仍用原来的 num。所以样例中 2025-02-01 的 naim_n1、naim_n2 会得到 4、5。
  4. total 单独按日期汇总,合计行的 num、naim、txt 都是 NULL,不会把某条明细的 naim 带进合计行。
  5. 最外层 ORDER BY ... num NULLS LAST 会把合计行排在每个日期的明细行之后。

如果希望 pr 以逗号作为小数分隔符显示,可以在最外层用 replace(pr::text, '.', ',') AS pr_text,但计算 SUM(pr) 和排序时仍然要用数值型 pr。

注意事项

  • 不要在 ROLLUP 的选择列表里直接保留未分组的 naim、txt。合计行中的这些列应使用 GROUPING() 置空,或者像上面那样用独立的汇总分支。
  • 如果某个日期在 test 中完全没有记录,但仍要输出该日期,需要另外提供日期来源;当前 dates 来自 test,不会生成完全没有数据的日期。
  • 如果 date_pr + naim 不唯一,先聚合再连接,否则每个日期分组的行数会超过 DISTINCT naim 的数量。

备注:内容仅供参考。