当前位置:   article > 正文

一、教程 3. 高级功能 3.5. 窗口函数 3.6. 继承(Inheritance)

一、教程 3. 高级功能 3.5. 窗口函数 3.6. 继承(Inheritance)

3.5窗口函数

  1. 窗口函数调用总是包含OVER直接跟在窗口函数名称和参数之后的子句。这就是它在语法上与普通函数或非窗口聚合的区别。该OVER子句确切地确定了查询的行如何被窗口函数分割以进行处理。
SELECT depname, empno, salary, avg(salary) FROM empsalary GROUP BY depname;--错误
报错信息如下:
SQL 错误 [42803]: ERROR: column "empsalary .empno" must appear in the GROUP BY clause or be used in an aggregate function
  • 1
  • 2
  • 3

与聚合函数类似,窗口函数对与当前行有某种关联的一组表行执行计算。但是,窗口函数可以保留非分组的列。

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
  • 1

PARTITION BY子句OVER将行分为组或分区,这些组或分区共享相同的PARTITION BY表达式值。对于每一行,窗口函数是在与当前行落入同一分区的行中计算的。OVER子句导致它被视为窗口函数并跨窗口框架计算。

2.可以使用order by 控制窗口函数处理行的顺序。例如:

SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;
  • 1

窗口函数考虑的行是由查询子句生成的“虚拟表”FROM中​​的行,这些行由其WHERE、GROUP BY和HAVING子句(如果有)过滤。例如,由于不满足WHERE条件而被删除的行不会被任何窗口函数看到。

一个查询可以包含多个窗口函数,它们使用不同的子句以不同的方式分割数据OVER,但它们都作用于由该虚拟表定义的同一行集合。

3.省略ORDER BY、PARTITION BY,默认框架由分区中的所有行组成。因此每个输出行的sum得到相同结果。

SELECT salary, sum(salary) OVER () FROM empsalary;
  • 1

4.但是如果我们添加一个ORDER BY子句,我们会得到非常不同的结果,这里的总和是从第一个(最低)工资到当前工资,包括当前工资的任何重复(注意重复工资的结果)。

SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
  • 1

5.窗口函数仅允许在SELECT和ORDER BY子句中使用,在其他地方被禁止。例如GROUP BY, HAVING 和 WHERE子句。这是因为它们在处理这些子句之后执行。此外,窗口函数在非窗口聚合函数之后执行。这意味着在窗口函数的参数中包含聚合函数调用是有效的,但反之则不然。

6.如果在执行窗口计算后需要过滤或分组行,您可以使用子选择。例如:

SELECT depname, empno, salary, enroll_date
FROM
  (SELECT depname, empno, salary, enroll_date,
          rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
     FROM empsalary
  ) AS ss
WHERE pos < 3;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

7.当查询涉及多个窗口函数时,可以用单独的子句写出每个函数,但如果多个函数需要相同的窗口行为,则这是重复的并且容易出错。每个窗口行为都可以在一个WINDOW子句中命名。

SELECT sum(salary) OVER w, avg(salary) OVER w
  FROM empsalary
  WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
  • 1
  • 2
  • 3

3.6继承

1.继承(Inheritance) 用法

CREATE TABLE cities (
  name       text,
  population real,
  elevation  int     -- (in ft)
);

CREATE TABLE capitals (
  state      char(2) UNIQUE NOT NULL
) INHERITS (cities);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

capitals 继承其父级的所有列(name、population和elevation)。在PostgreSQL中,一个表可以从零个或多个其他表继承。查询父类cities表会查询出继承层次结构中的下面的表及cities表的记录。父类cities表前加ONLY表示查询应该只在cities表上运行。

SELECT、UPDATE和DELETE——都支持这种ONLY表示法。尽管继承经常有用,但它没有与唯一约束或外键集成,这限制了它的有用性。

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/在线问答5/article/detail/765335
推荐阅读
相关标签
  

闽ICP备14008679号