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 有几点需要说明:
dates × names保证每个日期都有一组完整的naim,明细行数等于SELECT COUNT(DISTINCT naim) FROM test。LEFT JOIN test只把实际存在的date_pr + naim数据带进来,缺失的pr、txt会是NULL。ROW_NUMBER()在日期内按(t.num IS NULL), t.num, n.naim排序,为缺失的naim生成连续的num;已经有num的行仍用原来的num。所以样例中 2025-02-01 的naim_n1、naim_n2会得到 4、5。total单独按日期汇总,合计行的num、naim、txt都是NULL,不会把某条明细的naim带进合计行。- 最外层
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的数量。
备注:内容仅供参考。
