SQL2008 窗口查询
来自 PostgreSQL Wiki
跳转到导航跳转到搜索什么是 SQL:2008 窗口查询？
SQL 是一种功能强大的语言，它可以解答大多数问题。然而，其中一些查询的性能并不理想，编写起来也不容易。一些在纯 SQL 中难以实现的操作实际上是经常需要的，包括
- 计算累计总和
- 按行显示部门内每行的累计薪资，每行都包含先前行的薪资总和。
- 查找分组内的百分比
- 显示某部门个人在总薪资中的百分比。将他们的薪资除以部门的总薪资。
- Top-N 查询
- 查找收入最高的 N 个人或每个地区的 Top N 销售额。
- 计算移动平均值
- 将当前行的值与前 N 行的值一起求平均值。
- 执行排名查询
- 显示个人薪资在部门内的相对排名。
Top-N 和排名查询都可以通过简单地返回结果行的编号（排序后）来实现。然后，可以使用行号来计算基于位置的信息。分析函数旨在解决这些问题。它们为 SQL 语言添加了扩展，不仅使这些操作更容易编码，而且比纯 SQL 方法更快。分析函数的语法乍一看很简单，但表象可能具有欺骗性。它从以下部分开始：
FUNCTION_NAME(<argument>,<argument>,...)
OVER
(<Partition-Clause> <Order-by-Clause> <Frame-Clause>)
- PARTITION BY 子句根据分区表达式设置的条件，将单个结果集逻辑地分成 N 个组。'分区' 和 '组' 术语可以互换使用。
- ORDER BY 子句指定每个组（分区）内数据的排序方式。
- FRAME 子句为我们提供了一种方法，可以在组内定义一个滑动或锚定的数据窗口，分析函数将在该窗口上运行。可以使用该子句让分析函数根据组内任何任意滑动或锚定的窗口计算其值。
示例
此示例演示如何使用分析函数 SUM 来执行累计求和。首先，我们在表中填充一些值。该表非常简单，仅包含字段 dt 和 xy。请注意，对于给定日期，可以插入多行，这正是我在此处执行的操作。我感兴趣的是提取表中每一天的累计总和。也就是说，如果我为同一天插入三行，例如 3、4 和 5，我并不希望每行的总和仅为 3+4+5，而是第一行为 3，第二行为 3+4，第三行为 3+4+5。
create table sum_example (
dt date,
xy number
);
insert into sum_example values (to_date('27.08.2001','DD.MM.YYYY'),4);
insert into sum_example values (to_date('02.09.2001','DD.MM.YYYY'),1);
insert into sum_example values (to_date('09.09.2001','DD.MM.YYYY'),5);
insert into sum_example values (to_date('26.08.2001','DD.MM.YYYY'),3);
insert into sum_example values (to_date('28.08.2001','DD.MM.YYYY'),4);
insert into sum_example values (to_date('26.08.2001','DD.MM.YYYY'),6);
insert into sum_example values (to_date('29.08.2001','DD.MM.YYYY'),9);
insert into sum_example values (to_date('30.08.2001','DD.MM.YYYY'),2);
insert into sum_example values (to_date('12.09.2001','DD.MM.YYYY'),7);
insert into sum_example values (to_date('23.08.2001','DD.MM.YYYY'),2);
insert into sum_example values (to_date('27.08.2001','DD.MM.YYYY'),5);
insert into sum_example values (to_date('09.09.2001','DD.MM.YYYY'),9);
insert into sum_example values (to_date('01.09.2001','DD.MM.YYYY'),3);
insert into sum_example values (to_date('07.09.2001','DD.MM.YYYY'),1);
insert into sum_example values (to_date('12.09.2001','DD.MM.YYYY'),4);
insert into sum_example values (to_date('03.09.2001','DD.MM.YYYY'),5);
insert into sum_example values (to_date('03.09.2001','DD.MM.YYYY'),8);
insert into sum_example values (to_date('07.09.2001','DD.MM.YYYY'),7);
insert into sum_example values (to_date('04.09.2001','DD.MM.YYYY'),8);
insert into sum_example values (to_date('09.09.2001','DD.MM.YYYY'),1);
insert into sum_example values (to_date('29.08.2001','DD.MM.YYYY'),3);
insert into sum_example values (to_date('30.08.2001','DD.MM.YYYY'),7);
insert into sum_example values (to_date('24.08.2001','DD.MM.YYYY'),7);
insert into sum_example values (to_date('07.09.2001','DD.MM.YYYY'),9);
insert into sum_example values (to_date('26.08.2001','DD.MM.YYYY'),2);
insert into sum_example values (to_date('09.09.2001','DD.MM.YYYY'),8);
select dt,
sum(xy) over (partition by trunc(dt) order by dt rows between
unbounded preceding and current row) s,
xy
from sum_example;
- 分析函数
sum(xy) over (partition by trunc(dt) order by dt rows between unbounded preceding and current row)
- select 语句将返回
23/08/01 2 2 24/08/01 7 7 26/08/01 3 3 26/08/01 5 2 26/08/01 11 6 27/08/01 4 4 27/08/01 9 5 28/08/01 4 4 29/08/01 3 3 29/08/01 12 9 30/08/01 2 2 30/08/01 9 7 01/09/01 3 3 02/09/01 1 1 03/09/01 5 5 03/09/01 13 8 04/09/01 8 8 07/09/01 9 9 07/09/01 16 7 07/09/01 17 1 09/09/01 5 5 09/09/01 14 9 09/09/01 22 8 09/09/01 23 1 12/09/01 4 4 12/09/01 11 7
- 第三列对应于 xy（使用 insert into ... 在上面插入的值）。有趣的是第二列。例如，在 2001 年 8 月 26 日，该日期的第一行是 3（等于 xy），第二行是 5（等于 xy+3），第三行是 11（等于 xy+3+5）。
窗口查询中使用的分析函数列表
- AVG (<distinct|all> expression ) 用于计算组和窗口内表达式的平均值。Distinct 可用于查找删除重复项后组内值的平均值。
- CORR (expression, expression) 返回一对返回数字的表达式的相关系数。它是以下内容的简写
- COVAR_POP(expr1, expr2) /
- STDDEV_POP(expr1) * STDDEV_POP(expr2)). 从统计学上讲，相关性是指变量之间关联的强度。变量之间的关联意味着可以通过另一个变量的值在一定程度上预测一个变量的值。相关系数通过返回 -1（强反相关）和 1（强相关）之间的数字来给出关联的强度。值为 0 表示没有相关性。
- COUNT (<distinct> <*> <expression>) 将计算组内的出现次数。如果指定 * 或某个非空常量，count 将计算所有行。如果指定表达式，count 将返回表达式的非空求值的次数。可以使用 DISTINCT 修饰符在删除重复项后计算组中行的出现次数。
- COVAR_POP (expression, expression) 返回一对返回数字的表达式的总体协方差。
- COVAR_SAMP (expression, expression) 返回一对返回数字的表达式的样本协方差。
- CUME_DIST 此函数计算组中行的相对位置。CUME_DIST 将始终返回大于 0 且小于或等于 1 的数字。该数字表示该行在 N 行组中的'位置'。例如，在三行组中，返回的累积分布值将分别为 1/3、2/3 和 3/3。
- DENSE_RANK 此函数计算查询返回的每行相对于其他行的相对排名，基于 ORDER BY 子句中表达式的值。组内的数据按 ORDER BY 子句排序，然后依次为每行分配一个数字排名，从 1 开始，依次递增。每当 ORDER BY 表达式的值发生变化时，排名都会递增。具有相同值的行会收到相同的排名（空值在此比较中被视为相等）。密集排名会返回没有间隙的排名编号。这与下面的 RANK 相比。
- FIRST_VALUE 此函数仅返回组中的第一个值。
- LAG (expression, <offset>, <default>) LAG 使您能够访问结果集中的其他行，而无需进行自连接。它允许您将游标视为数组。您可以引用组中当前行之前的行。这将允许您选择组中的'前一行'以及当前行。有关如何获取'下一行'，请参见 LEAD。偏移量是一个正整数，默认为 1（前一行）。默认值是在索引超出窗口范围时返回的值（对于组中的第一行，将返回默认值）。
- LAST_VALUE 此函数仅返回组中的最后一个值。
- LEAD (expression, <offset>, <default>) LEAD 是 LAG 的反面。LAG 使您能够访问组中您之前的一行，而 LEAD 使您能够访问您之后的一行。偏移量是一个正整数，默认为 1（下一行）。默认值是在索引超出窗口范围时返回的值（对于组中的最后一行，将返回默认值）。
- MAX(expression) 在组的窗口内查找表达式的最大值。
- MIN(expression) 在组的窗口内查找表达式的最小值。
- NTILE (expression) 将组划分为'表达式值'个桶。例如；如果表达式 = 4，则组中的每行都将被分配一个从 1 到 4 的数字，将其放入百分位数。如果组中有 20 行，则前 5 行将被分配 1，接下来的 5 行将被分配 2，依此类推。如果组的基数不能被表达式整除，则行将被分布，以便没有百分位数的行数比该组中的任何其他百分位数多 1 行，而最低的百分位数将包含'额外'行。例如，再次使用表达式 = 4 且行数 = 21，百分位数 = 1 将有 6 行，百分位数 = 2 将有 5 行，依此类推。
- PERCENT_RANK 这类似于 CUME_DIST（累积分布）函数。对于组中的给定行，它计算该行的排名减 1，除以组中正在评估的行数减 1。该函数始终返回 0 到 1（包括）之间的值。
- RANK 此函数计算查询返回的每行相对于其他行的相对排名，基于 ORDER BY 子句中表达式的值。组内的数据按 ORDER BY 子句排序，然后依次为每行分配一个数字排名，从 1 开始，依次递增。具有相同 ORDER BY 表达式值的行会收到相同的排名；但是，如果两行收到相同的排名，则排名编号将随后'跳过'。如果两行都是 1 号，则不会有 2 号 - 排名将为组中的下一行分配值 3。这与 DENSE_RANK 相反，DENSE_RANK 不会跳过值。
- RATIO_TO_REPORT (expression) 此函数计算表达式 / (sum(expression)) 在组上的值。这将为您提供当前行对 sum(expression) 的总和的贡献百分比。
- REGR_ xxxxxxx (expression, expression) 这些线性回归函数将普通最小二乘回归线拟合到一对表达式。可以使用不同的回归函数。
- ROW_NUMBER 返回有序组中行的偏移量。可用于按特定条件顺序对行进行编号。
- STDDEV (expression) 计算当前行相对于组的标准差。
- STDDEV_POP (expression) 此函数计算总体标准差并返回总体方差的平方根。其返回值与 VAR_POP 函数的平方根相同。
- STDDEV_SAMP (expression) 此函数计算累积样本标准差并返回样本方差的平方根。此函数返回的值与 VAR_SAMP 函数的平方根相同。
- SUM(expression) 此函数计算组中表达式的累积总和。
- VAR_POP (expression) 此函数返回一组非空数字（忽略空值）的总体方差。VAR_POP 函数为我们执行以下计算：(SUM(expr*expr) - SUM(expr)*SUM(expr) / COUNT(expr)) / COUNT(expr)
- VAR_SAMP (expression) 此函数返回一组非空数字（忽略集合中的空值）的样本方差。此函数为我们执行以下计算：(SUM(expr*expr) - SUM(expr)*SUM(expr) / COUNT(expr)) / (COUNT(expr) - 1)
- VARIANCE (expression) 此函数返回表达式的方差。
Most used functions to improve some performance :
- SUM、RANK、DENSE_RANK、ROW_NUMBER、LAG、LEAD、FIRST_VALUE、LAST_VALUE、NTH_VALUE
