SQL2003 窗口查询

来自 PostgreSQL 维基
跳转到导航跳转到搜索

此页面已失效或正在失效，请参考 SQL2008_windowing_queries.


旧内容

什么是 SQL:2003 窗口查询？

SQL 是一种功能强大的语言，它可以回答许多问题。然而，其中一些查询的性能并不理想，而且查询本身也不容易编写。一些在纯 SQL 中难以完成的操作，实际上是常见的需求，包括

  • 计算累计总计 - 按行显示部门内的累积薪资，每行包含之前各行薪资的总和。
  • 查找分组内的百分比 - 显示某个部门中个人所占薪资总额的百分比。将他们的薪资除以部门的薪资总额。
  • Top-N 查询 - 查找薪资最高的 N 个人或各区域的最高 N 笔销售额。
  • 计算移动平均值 - 将当前行的值与前 N 行的值一起平均。
  • 执行排名查询 - 显示个人在部门内的薪资排名。

“Top-N”和排名查询都可以通过简单地返回结果行号（排序后）来实现。然后，可以使用行号来计算基于位置的信息。

分析函数旨在解决这些问题。它们为 SQL 语言添加扩展，不仅使这些操作更易于编码，而且比纯 SQL 方法更快。这些扩展目前正在由 ANSI SQL 委员会进行审查，以期将其纳入 SQL 规范。

分析函数的语法看起来相当简单，但外表可能具有欺骗性。它以以下内容开头

        FUNCTION_NAME(<argument>,<argument>,...)
        OVER
        (<Partition-Clause> <Order-by-Clause> <Windowing Clause>)
    • PARTITION BY 子句根据分区表达式设置的标准，将单个结果集逻辑地划分为 N 个组。单词“分区”和“组”可互换使用。
    • ORDER BY 子句指定如何在每个组（分区）内对数据进行排序。
    • WINDOWING 子句为我们提供了一种方法，可以在组内定义一个滑动或锚定的数据窗口，分析函数将在该窗口上运行。此子句可用于让分析函数根据组内任何任意滑动或锚定的窗口来计算其值。

    示例

    此示例展示了如何使用分析函数 SUM 来执行累计求和。首先，我们在表中填充一些值。该表非常简单，仅包含 dt 和 xy 字段。请注意，对于给定的日期，可以插入多行，这正是我在这里所做的事情。我感兴趣的是提取表中每天的累计总和。也就是说，如果我对于同一个日期有三个条目，例如 3、4 和 5，我不希望每行的总和仅为 3+4+5，而是第一行为 3，第二行为 3+4，第三行为 3+4+5。您可以使用小型数据库 Oracle 10g Express Edition 进行测试。

        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）。

最近，在 2004 年之后，出现了一些数据库的 Express Edition 版本，例如 Oracle、Microsoft SQL Server，您可以在其中测试一些分析函数

  • 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。Offset 是一个正整数，默认为 1（前一行）。Default 是如果索引超出窗口范围（对于组中的第一行，将返回 Default）要返回的值
  • LAST_VALUE 这将简单地返回组中的最后一个值。
  • LEAD (expression, <offset>, <default>) LEAD 是 LAG 的反面。LAG 使您能够访问组中您前面的行 - LEAD 使您能够访问您后面的行。Offset 是一个正整数，默认为 1（下一行）。Default 是如果索引超出窗口范围（对于组中的最后一行，将返回 Default）要返回的值。
  • 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) 此函数计算组中 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) 此函数返回表达式的方差。

一些提高性能的常用函数列表

    SUM、RANK、DENSE_RANK、ROW_NUMBER、LAG、LEAD、FIRST_VALUE、LAST_VALUE
取自 "https://wiki.postgresql.ac.cn/index.php?title=SQL2003_windowing_queries&oldid=17247"