窗口函数以及SQL中的各种聚合结构

通用聚合函数

聚合函数可以用于计算整个表的数据或在“GROUP BY”子句中定义的分组。它们可以在“HAVING”、“SELECT”和“ORDER BY”子句中使用。

在PostgreSQL中，SQL聚合函数包括：“SUM()”（求和）、“AVG()”（平均值）、“MIN()”（最小值）、“MAX()”（最大值）、“BOOL_OR()”（布尔或）、“BOOL_AND()”（布尔与）、“JSON_AGG()”（JSON聚合）和“XMLAGG()”（XML聚合）。

• SUM()：计算某列的总和。
• AVG()：计算某列的平均值。
• MIN()：找出某列的最小值。
• MAX()：找出某列的最大值。
• BOOL_OR()：对布尔类型的列进行逻辑或运算。如果列中至少有一个TRUE，则返回TRUE。
• BOOL_AND()：对布尔类型的列进行逻辑与运算。如果列中所有值都是TRUE，则返回TRUE。
• JSON_AGG()：将一组记录聚合成一个JSON数组。
• XMLAGG()：将一组记录聚合成一个XML格式的数据。

这些聚合函数在处理数据分析和报告时非常有用，能够提供数据的汇总视图，帮助进行决策支持和洞察发现。例如，在财务报告中使用SUM()计算总销售额，在用户行为分析中使用AVG()计算平均会话时长，在系统日志分析中使用BOOL_AND()判断一系列事件是否全部成功，或者使用JSON_AGG()和XMLAGG()在生成数据聚合报告时格式化输出。

统计聚合函数

在PostgreSQL中，SQL统计聚合函数包括：“VARIANCE()”（方差）、“STDDEV()”（标准差）、“VAR_POP()”（总体方差）、“COVAR_POP()”（总体协方差）、“COVAR_SAMP()”（样本协方差）、“REGR_AVGX()”（线性回归的X平均值）、“REGR_SLOPE()”（线性回归斜率）、“REGR_INTERCEPT()”（线性回归截距）和“REGR_R2()”（线性回归的决定系数R平方）。

• VARIANCE() 或 VAR_SAMP(): 计算一组数值的样本方差。
• STDDEV() 或 STDDEV_SAMP(): 计算一组数值的样本标准差。
• VAR_POP(): 计算一组数值的总体方差。
• COVAR_POP(): 计算两个数值集的总体协方差，反映这两个数值集变动时是否同向变化以及变化程度的大小。
• COVAR_SAMP(): 计算两个数值集的样本协方差。
• REGR_AVGX(): 在线性回归分析中，计算自变量X的平均值。
• REGR_SLOPE(): 在线性回归分析中，计算回归线的斜率，即因变量Y对自变量X的敏感度。
• REGR_INTERCEPT(): 在线性回归分析中，计算回归线的Y轴截距，即当X=0时Y的估计值。
• REGR_R2(): 在线性回归分析中，计算决定系数R平方，衡量自变量X对因变量Y的解释程度，值范围从0到1。

这些统计聚合函数在数据分析中非常重要，它们可以用来分析数据集的分布、波动和关系强度，对于进行统计分析、预测建模以及理解数据之间的关联性至关重要。例如，使用VARIANCE()和STDDEV()帮助理解数据的波动性，COVAR_POP()和COVAR_SAMP()用于评估两个变量之间的协变性，而REGR_*函数系列则支持执行线性回归分析，以便理解变量之间的线性关系。

有序集聚合函数

有序集合聚合函数是逆分布函数，它们在处理数据时考虑了数据的顺序。在PostgreSQL中，有序集合聚合函数包括：“MODE()”（众数）、“PERCENTILE_CONT()”（连续百分位数）和“PERCENTILE_DISC()”（离散百分位数）。

• MODE(): 计算一组数据中出现次数最多的值。在有多个众数的情况下，PostgreSQL的MODE()函数可能会根据具体实现返回其中一个。

• PERCENTILE_CONT(fraction) WITHIN GROUP (ORDER BY sort_expression): 计算连续百分位数，fraction是一个介于0和1之间的数，表示百分位等级。如果计算的百分位数落在两个数值之间，PERCENTILE_CONT()会计算这两个数值的线性插值。这个函数需要使用WITHIN GROUP (ORDER BY sort_expression)子句来指定排序的基础。

• PERCENTILE_DISC(fraction) WITHIN GROUP (ORDER BY sort_expression): 计算离散百分位数，fraction同样是一个介于0和1之间的数。与PERCENTILE_CONT()不同的是，如果计算的百分位数落在两个数值之间，PERCENTILE_DISC()会直接返回这两个数值中较小的那一个（即更接近百分位数的下界）。这个函数也需要使用WITHIN GROUP (ORDER BY sort_expression)子句来确定排序顺序。

这些有序集合聚合函数在数据分析中非常有用，尤其是在需要根据数据的分布特性进行分析时。例如，PERCENTILE_CONT()和PERCENTILE_DISC()可以用来评估数据的分布情况，找出中位数（50%的百分位数）或其他任何百分位数，以此来理解数据集中的趋势和异常值。这些函数在财务分析、市场研究、科学研究等领域有广泛应用。

● 有序集合聚合函数的行为与其他聚合函数类似。

假设集聚合函数

● 假设集合聚合函数包括排名函数。在PostgreSQL中，SQL假设集合聚合函数包括“RANK()”（排名）、“DENSE_RANK()”（密集排名）、“PERCENT_RANK()”（百分比排名）和“CUME_DIST()”（累计分布）。

• RANK(): 在使用ORDER BY子句排序的结果集中为每行分配一个排名。相同值的行会有相同的排名，但是下一个排名会跳过中间的数字。例如，如果两行并列第一，则下一行的排名为第三。

• DENSE_RANK(): 类似于RANK()，但是它不会跳过任何排名。如果两行并列第一，则下一行的排名仍然是第二。

• PERCENT_RANK(): 计算行在结果集中的相对排名，返回值介于0和1之间，计算公式为：(排名 - 1) / (总行数 - 1)。如果结果集只有一行，PERCENT_RANK()返回0。

• CUME_DIST(): 计算累计分布，即结果集中小于等于当前行值的行所占的比例，返回值介于0和1之间。

● 这些假设集合聚合函数主要用于数据分析中的排名和百分比计算。它们可以帮助分析数据的相对位置和分布情况，非常适用于竞赛排名、考试成绩排名、销售数据的性能评估等场景。

● 例如，使用RANK()和DENSE_RANK()可以对销售员根据销售额进行排名，而PERCENT_RANK()和CUME_DIST()则可以评估一个销售员的销售额在整个销售团队中的相对位置或累计比例，从而更深入地理解个体和团队的表现。

窗口函数

● 但你不应该这么做!
● 我们可以使用嵌套查询，也可以使用窗口函数。

● 窗口函数是一种特殊的函数，它在与该函数在“OVER”子句中本地关联的窗口内聚合多行数据，与此相对的是聚合函数的窗口，后者与整个查询中的“GROUP BY”子句关联。

聚合窗口函数

• AVG(), MIN(), MAX(), SUM(), COUNT(): 这些聚合函数在作为窗口函数使用时，可以计算定义在特定窗口上的平均值、最小值、最大值、总和和计数。这意味着你可以对分组内的每一行进行聚合计算，而不是对整个查询结果集返回一个单一值。

排名窗口函数

• ROW_NUMBER(): 为每个窗口内的行分配一个唯一的连续整数，通常用于排序。每个分组的编号从1开始。
• NTILE(n): 将窗口内的行分为n个大致相等的等级组，并为每一行分配一个表示其等级组的数字。这个函数对于分析分布或将数据集分为等级很有用。
• CUME_DIST(): 计算当前行在其分区中的相对位置，具体来说，是窗口中小于等于当前行的行数占总行数的比例。这可以用于确定某个值在数据分布中的位置。

值窗口函数

• LAG(value, offset, default): 允许你访问当前行之前的行中的数据。offset参数指定了向上查看的行数，如果指定的行不存在，则返回default值。这对于比较当前行与之前行的数据很有用。
• LEAD(value, offset, default): 与LAG函数相似，但LEAD允许你访问当前行之后的行中的数据。

<窗口函数> OVER (
    PARTITION BY <分区依据的列>
    ORDER BY <排序依据的列>
    [ROWS|RANGE <窗口帧>]
)

• PARTITION BY 子句（可选）：定义窗口内的分区，即根据指定的列值将数据分成不同的组。
• ORDER BY 子句（可选）：定义窗口内数据的排序方式。
• ROWS|RANGE 子句（可选）：定义窗口帧，即窗口内考虑的行的范围。

● 窗口函数通过在OVER()子句中指定分区（PARTITION BY）和排序（ORDER BY）规则来定义窗口。这使得窗口函数特别适用于需要对数据集中的每一行或一组行执行计算的场景，例如计算滑动平均值、排名或与前一行比较的值等。

● 例如，使用ROW_NUMBER()可以给按某种顺序排序的数据集中的每行分配一个序号，LAG()函数可以用来比较当前行与前一行的数据差异，而NTILE()可以用于将数据集分成几个等级或分位，便于进行分段分析。

● 窗口函数有一个由关键字“OVER”决定的窗口。

● 窗口函数可以在“SELECT”和“ORDER BY”子句中的表达式中使用。

● 窗口函数只能用于“SELECT”和“ORDER BY”条款。

● “OVER”子句可以定义窗口为分区，并在其中对行进行排序。

● 将所有窗口用“WINDOW”子句定义并命名是一个好习惯。

● 窗口函数的帧子句定义了当前分区中的一部分行子集，窗口函数将应用于这个子集。这个行子集被称为帧。

● 默认的帧子句是“RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”（范围从无界前行到当前行），这适用于有“ORDER BY”子句的情况。

● 如果没有“ORDER BY”子句，则默认的帧子句是“RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING”（范围从无界前行到无界后行）。

结论

● 使用上述所有或部分构造在数据库中而非应用程序代码中处理数据具有许多软件工程的优点。这种方法可能会更高效、更具适应性和可扩展性。

● 还有更多此类工具，如存储函数和触发器。

● SQL标准及其实现仍有很大的改进空间。