窗口函数Window Functions
窗口函数对与当前行相关的一组行数据执行计算,这与统计函数执行的计算类似。不同于一般统计函数的是:使用窗口函数不会使所有参与计算的行输出为一个单一行,所有的数据行都保持它们自己的标识。窗口函数在后台能够访问除了当前查询结果之外的的数据。
窗口函数调用一般包含一个直接跟着窗口函数名和参数的OVER定语,这是将它与一般函数或者更新函数进行区别的语法,OVER
定语决定了查询行被窗口函数具体的分割方式 。
在OVER之中的PARTITION BY明确了将查询行划分为组或者几部分,并且共享同一个PARTITION BY表达式的结果。对于每一行,窗口函数都根据已将分割后的同类行来进行计算。我们也可以通过在OVER中使用ORDER BY来控制所有行被处理的顺序
例如以下:
SELECT depname, empno, salary,
rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;
从中可以看出,rank函数在当前分区针对每一个ORDER BY的值产生了一个数字排序。
经由窗口函数处理的行是由诸如WHERE,GROUP BY 以及HAVING定于过滤得到的虚拟的表
有一个和窗口函数相关的概念:对于每一行,在分区中的一组行被称作窗口框架。许多(不是全部)只在窗体框架中执行,而不是在整个分区中执行。
SELECT salary, sum(salary) OVER () FROM empsalary
重要的一点是,因为在OVER定语中没有 ORDER BY,窗口框架和分区是一样大小的,所以处理的结果是所有的行,结果也就是一样的,但是如果我们添加一个ORDER BY定语,那么结果就完全不一样了
SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
窗口函数只被允许用在SELECT和ORDER BY查询中.,在其他定于查询中都是被禁止的。这是因为他们是在查询之后的逻辑执行,而且,窗口函数也在更新操作之后执行,这意味着可以将一个更新函数调用包含进去。
如果需要在窗口函数后对行进行过滤或者分组可以使用子查询:
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;
此查询只显示内查询having排列小于三的行
如果一个查询包含多个窗口函数,我们可以使用分开的OVER从句来实现。
继承(Inheritance)
继承是面向对象数据库的一个概念.它开启了数据库设计中有趣的新可能性。
例如我们要创建两张表:城市表和首都表,一般情况下可以这样来创建:
CREATE TABLE capitals (
name text,
population real,
altitude int, -- (in ft)
state char(2)
);
CREATE TABLE non_capitals (
name text,
population real,
altitude int -- (in ft)
);
CREATE VIEW cities AS
SELECT name, population, altitude FROM capitals
UNION
SELECT name, population, altitude FROM non_capitals;
上边的设计对于查询来说是没有问题的,但是当我们要进行多行数据的更新时就不友好了,所以,有了以下的设计方案:
CREATE TABLE cities (
name text,
population real,
altitude int -- (in ft)
);
CREATE TABLE capitals (
state char(2)
) INHERITS (cities);
在这种设计中,首都表从城市表中继承name,population,altitude
在PostgreSQL数据库中,一张表可以继承自一张或者多张数据表
例如以下查询获得了海拔在 500英尺以上的所有城市信息,包含了首都信息:
SELECT name, altitude
FROM cities
WHERE altitude
>
500;
而且我们还可以查询不是首都并且海拔在500英尺以上的城市:
SELECT name, altitude
FROM ONLY cities
WHERE altitude
>
500;
在cities之前的ONLY表示查询只在城市表中进行,对于向下继承的表不做查询,许多命令都支持ONLY查询,例如:SELECT,UPDATE和DELETE
注意:尽管继承非常的有用,但是目前还是有很多的限制。