Radim Marek：PostgreSQL 窗口函数的简明介绍
内容提要
理解数据点之间的关系至关重要。窗口函数允许在保留每行细节的同时分析数据，与聚合函数不同。PostgreSQL支持SQL窗口函数，可在表中的相关行之间进行复杂计算。窗口函数特别适用于排名、计算累计总数、查找移动平均值和比较单个条目等任务。窗口函数的语法包括PARTITION BY、ORDER BY和ROWS BETWEEN等关键词。常用的窗口函数包括ROW_NUMBER()、RANK()、DENSE_RANK()、LAG()、LEAD()、FIRST_VALUE()和LAST_VALUE()等。窗口函数的使用可以方便地进行高级分析，如计算差异、比较当前和前一个值、获取未来值等。
延伸解读
窗口函数与聚合函数的核心差异
文章通过传感器读数示例清晰展示了窗口函数与聚合函数的根本区别：聚合函数将数据汇总为单行，而窗口函数在保留每行细节的同时计算聚合值。例如，使用AVG() OVER (PARTITION BY sensor_id)时，每行仍显示原始reading_value，同时附加该分区的平均值。这种特性使得窗口函数特别适合需要同时查看明细和汇总的场景，如计算每个客户的订单占比或比较个体与整体。
窗口定义三要素：分区、排序与框架
窗口函数的OVER子句包含三个关键部分：PARTITION BY用于划分数据组，类似聚合的GROUP BY；ORDER BY定义分区内行的顺序，对移动平均或行号至关重要；框架（ROWS/RANGE BETWEEN）进一步限制计算范围。文章强调只有分区是必需的，但排序和框架能实现更精细的控制。例如，ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING可计算包含当前行前后各一行的移动平均。
排名函数的选择：ROW_NUMBER、RANK与DENSE_RANK
文章对比了三种排名函数：ROW_NUMBER()为每行分配唯一序号，适合去重或取最新记录；RANK()在遇到相同值时赋予相同排名，但后续排名会跳跃（如1,1,3）；DENSE_RANK()同样处理并列，但排名连续（如1,1,2）。选择取决于业务需求：若需自然排名且允许空缺，用RANK；若需紧凑排名，用DENSE_RANK；若需唯一行号，用ROW_NUMBER。
LAG/LEAD与窗口定义复用
LAG和LEAD函数允许访问前一行或后一行的值，无需自连接，便于计算行间差异或趋势。文章示例中，通过LAG(reading_value,1)和LEAD(reading_value,1)获取前后读数。此外，当多个窗口函数使用相同定义时，可用WINDOW子句命名并复用，如WINDOW readings_window AS (PARTITION BY sensor_id ORDER BY reading_time)，这简化了复杂查询并确保一致性。
Q&A
什么是窗口函数，它与聚合函数有什么区别？
窗口函数允许在保留每行细节的同时分析数据，而聚合函数将数据汇总为单行。
PostgreSQL支持哪些常用的窗口函数？
常用的窗口函数包括ROW_NUMBER()、RANK()、DENSE_RANK()、LAG()、LEAD()、FIRST_VALUE()和LAST_VALUE()。
如何使用窗口函数计算移动平均值？
可以使用AVG函数结合OVER子句和ROWS BETWEEN来计算移动平均值。
窗口函数的基本语法是什么？
窗口函数的基本语法为：window_function ([expression...]) OVER window_definition。
RANK()和DENSE_RANK()函数有什么区别？
RANK()在处理平局时会留下排名间隙，而DENSE_RANK()则不会。
如何使用LAG和LEAD函数进行数据分析？
LAG函数用于访问前一行的值，LEAD函数用于访问后一行的值，便于计算行之间的差异。
