分析

窗口分区决定哪些行属于同一组，窗口帧决定其中哪些行参与当前行的某个帧敏感计算。把两种边界混淆，可能让逐笔累计变成重复显示的小计，也可能让 last_value 返回当前值，而报表原本需要的是分区最后一行的值。应先说明计算要回答什么，再选择排序和帧范围。 默认范围取决于是否排序 PostgreSQL 的默认帧是 RANGE：从分区开头到当前行的最后一个并列行。窗口 ORDER BY 中全部表达式都相等的行，属于同一个并列组。因此，有排序时的默认累计和，会包含当前行的全部同值伙伴，即使其中某些行在最终展示结果里排在后面，也已经进入当前帧。 如果窗口没有 ORDER BY，所有行都是并列行，默认帧就覆盖整个分区。因此，sum(amount) OVER (PARTITION BY account) 会重复显示账户总额；添加排序后，计算就变成有顺序的累计结果。“默认总是整个组”和“默认每次恰好多加一行”都不能同时描述这两种情况，不能把其中一种当成通用规则。 窗口顺序与最终输出顺序是分开的。外层 ORDER BY 可以改变展示排列，而不改变帧。反过来，在 RANGE 窗口内部加入唯一标识，可能把原来的并列组拆开，改变计算结果；这不只是让页面看起来稳定，而是修改了语义，应当按需求审阅。 按行、按值域和按并列组 ROWS 计算窗口排序中的位置。从前两行到当前行，最多包含三行；靠近分区起点时还会更少。它不表示三天或三分钟。如果多个事件时间戳相同，而业务需要确定的逐事件顺序，就应加入合适的唯一标识，明确同一时刻的事件谁先谁后。 RANGE 使用排序值和并列边界。指定偏移量时，PostgreSQL 要求只有一个排序表达式，并使用兼容的偏移类型，例如时间戳可以配合 interval。基于时间值域的帧按时间差纳入事件，忙时可能包含很多行，闲时可能很少。GROUPS 则按并列组计数，让帧沿不同排序值对应的组移动，同时保留组内全部并列行。这三者表达的是不同的计量单位。 first_value、last_value、nth_value，以及作为窗口函数使用的聚合，都依赖帧范围。排名和 lag、lead 的定义则基于分区顺序，并非相同规则；缩小帧不会让 lag 忘记分区中的前一行。解释某个窗口函数前，应查看该函数的语义，不能把“只看到帧内的行”套用到所有函数上。 坏掉的样子 有序默认帧中的 last_value，通常取得当前帧末尾的值，并不是整个分区末尾的值。排序唯一时，这恰好就是当前行。若要在每一行重复显示分区最后一个值，需要把结束边界写为 UNBOUNDED FOLLOWING，并提供能够定义“最后”的顺序。按时间排序后的末行值，与该字段的数值最大值，也不是同一个问题。 ...

GROUPING SETS 让一条 SQL 同时生成多种聚合层级。销售报表可以一起返回地区与商品明细汇总、地区小计和总计。结果中混合了不同种类的行，因此，识别每行属于哪个层级，与算对金额同样重要；如果下游把所有结果行都当成普通明细，即使数据库计算正确，报表仍可能出错。 声明报表需要的层级 GROUPING SETS ((region,product),(region),()) 声明三种分组。第一种为每个实际出现的地区与商品组合输出一行，第二种为每个地区输出一行，空集合则汇总全部选中的输入行。这是在声明结果语义，具体执行方式由优化器选择。它可以在不同层级共享工作，但语法本身不保证固定的物理扫描次数，也不会消除排序和内存成本。 ROLLUP(region,product) 是相同前缀集合的简写，并包含空集合。交换两列顺序，会改变小计维度。CUBE(region,product) 还包括只按商品汇总的层级，覆盖维度的全部子集。六个独立维度对应六十四种分组集合，不是只输出六十四行；每种集合还可能包含很多不同分组，所以结果规模可能远大于层级数。 所有层级都汇总 FROM 和 WHERE 选出的总体。如果过滤条件只保留某个月的销售，总计也只代表该月。如果不同层级需要不同输入总体，直接对一个共享输入声明这些集合，可能并不符合需求。比较另一张报表的总额之前，要先确认日期、业务状态等筛选范围一致，不能只对照列名。 用 GROUPING 标记层级 某个分组集合没有使用的维度列，会在输出中填入 SQL NULL。但原始数据本来就可能含有 NULL，因此仅靠哪些列为空，无法可靠判断这是小计还是普通分组。GROUPING(region,product) 返回位掩码，说明当前集合缺少了哪些分组表达式；最右边的参数对应最低有效位。 在这里，零表示两个维度都参与分组，一表示省略商品，三表示两个维度都省略。真实空地区的小计与全表总计，可能同时显示空地区、空商品，但掩码不同。应把这个标记纳入输出契约，只有层级明确表示跨地区汇总时，才生成“所有地区”等展示标签。 表达式是否参与分组，与表达式的值是否非空，是不同问题。GROUPING 报告前者，其参数必须匹配查询中的分组表达式，并不是测试任意值是否为空的普通函数。向下游提供数值层级或其他无歧义标识，比把每个空值都替换为“小计”更可靠，也能保留真实缺失维度的含义。 坏掉的样子 把结果中所有 total 再相加，会让同一笔销售在每个层级重复计入。需要进一步汇总时，应先选择一个层级，或直接取已经算好的总计。平均值和去重计数更不能随意相加：没有按样本数加权的地区平均值，通常不能直接平均成整体平均值；重叠集合的去重数量也不能简单求和。聚合层级不是彼此独立的新增交易。 空分组集合在没有输入行时仍输出一行，其中 count 为零，sum 为 NULL。因此，金额为空不等于查询没有返回总计行。应明确报表如何展示空总体，而不是把空值自动当作执行失败。还要避免无意重复声明分组集合；重复层级可能产生重复聚合行，并不代表多了一份独立证据。 快速验证 把下面内容保存为 grouping.sql，在可丢弃的 PostgreSQL 18 数据库中执行 psql -X -v ON_ERROR_STOP=1 -f grouping.sql。数据特意包含一个真实空地区，脚本用事务包住临时对象，末尾回滚清理。 ...

窗口函数让报表保留每条读数，同时附加相关行的信息。一条传感器读数可以展示前值、变化量和在该传感器总量中的占比，而不必把整个传感器压缩成一行结果。能否正确解释这些附加列，取决于计算前是否明确了输入总体、分区和顺序，不能只看函数名称就认定答案符合业务。 给相邻关系明确的含义 PARTITION BY 用来划分相互独立的序列。按 sensor 分区的 lag，只比较同一传感器的值；漏掉分区条件，可能把某个传感器的第一条读数与另一个传感器的最后一条相比较。OVER 内部的 ORDER BY 决定谁是前一行。可以使用稳定的序列键；时间戳可能重复时，则应使用时间戳加唯一标识，明确同一时刻的先后关系。 lag 找的是这个有序总体中的前一行，不一定是前一分钟、前一天或上一个计划采集时刻。采集有缺口时，数据库不会自动补出缺失行。如果计算要求与前一个自然日比较，应明确建立日历关系，或先构造所需的时间序列，再做窗口计算。表里相邻的两行，并不能证明采样间隔固定。 在分区开头，lag 返回默认值，通常是 SQL NULL，用来表示没有前驱行。把它改成零，会改变首条变化量的含义，应有业务规则支持。PostgreSQL 的 lag、lead 也会保留空值语义：如果前一条读数本身为空，它不会自动越过这条记录，继续向前寻找更早的非空值。不存在前驱与前驱值为空，需要在业务解释中保持区别。 在明细旁保留总额与占比 聚合函数配合 OVER 使用时，会保留输入行，并为每行增加计算结果。sum(value) OVER (PARTITION BY sensor) 没有窗口排序，因此重复显示整个传感器分区的总额。如果未考虑默认帧就加上排序，总额可能变成累计额，随后每一行的占比分母都会改变，这不是无关紧要的展示调整。 需要小数占比时，应在除法之前转换为 numeric。整数相除可能截断小数，让较小贡献看起来全部为零。示例使用 NULLIF 把零分母转换成 SQL NULL，使无法定义的占比保持未定义状态。这个处理是一条明确规则；改为零、拒绝数据集或展示另一个状态，都是不同的业务选择，不能让算式偶然决定。 并非每个窗口函数都使用移动帧。聚合和 last_value 等函数依赖帧，而 lag、lead 在分区行之间移动。仅仅缩小 ROWS 范围，并不会重新定义 lag 所指向的前一条分区记录。应分别为每个问题选择函数和窗口，不能把一条帧公式机械地套在所有计算上。 ...