2018-09-14PostgreSQL高级特性

2018-09-14 本文已影响0人 huxiaofeng

事务

在PostgreSQL中，开启一个事务需要将SQL命令用BEGIN和COMMIT命令包围起来。因此我
们的银行事务看起来会是这样：

BEGIN;
UPDATE accounts SET balance = balance - 100.00
 WHERE name = 'Alice';
-- etc etc
COMMIT;

如果，在事务执行中我们并不想提交，我们可以发出ROLLBACK命令而不是COMMIT命令，这样所有目前的更新将会被取消。

PostgreSQL实际上将每一个SQL语句都作为一个事务来执行。如果我们没有发出BEGIN命令，则每个独立的语句都会被加上一个隐式的BEGIN以及（如果成功）COMMIT来包围它。一组被BEGIN和COMMIT包围的语句也被称为一个事务块。

也可以利用保存点来以更细的粒度来控制一个事务中的语句。

保存点允许我们有选择性地放弃事务的一部分而提交剩下的部分。在使用SAVEPOINT定义一个保存点后，我们可以在必要时利用ROLLBACK TO回滚到该保存点。该事务中位于保存点和回滚点之间的数据库修改都会被放弃，但是早于该保存点的修改则会被保存

窗口函数

一个窗口函数在一系列与当前行有某种关联的表行上执行一种计算。这与一个聚集函数所完成的计算有可比之处。但是窗口函数并不会使多行被聚集成一个单独的输出行，这与通常的非窗口聚集函数不同。取而代之，行保留它们独立的标识。在这些现象背后，窗口函数可以访问的不仅仅是查询结果的当前行。
一个窗口函数调用总是包含一个直接跟在窗口函数名及其参数之后的OVER子句。这使得它从句法上和一个普通函数或非窗口函数区分开来。OVER子句决定究竟查询中的哪些行被分离出来由窗口函数处理。OVER子句中的PARTITION BY子句指定了将具有相同PARTITION BY表达式值的行分到组或者分区。对于每一行，窗口函数都会在当前行同一分区的行上进行计算。

group by是对检索结果的保留行进行单纯分组，一般和聚合函数一起使用例如max、min、sum、avg、count等一块用。partition by虽然也具有分组功能，但同时也具有其他的高级功能。

SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;
 depname | empno | salary | rank
-----------+-------+--------+------
 develop | 8 | 6000 | 1
 develop | 10 | 5200 | 2
 develop | 11 | 5200 | 2
 develop | 9 | 4500 | 4
 develop | 7 | 4200 | 5
 personnel | 2 | 3900 | 1
 personnel | 5 | 3500 | 2
 sales | 1 | 5000 | 1
 sales | 4 | 4800 | 2
 sales | 3 | 4800 | 2
(10 rows)

一个窗口函数所考虑的行属于那些通过查询的FROM子句产生并通过WHERE、GROUP BY、HAVING过滤的“虚拟表”。在一个查询中可以包含多个窗口函数，每个窗口函数都可以用不同的OVER子句来按不同方式划分数据，但是它们都作用在由虚拟表定义的同一个行集上。
我们已经看到如果行的顺序不重要时ORDER BY可以忽略。PARTITION BY同样也可以被忽略，在这种情况下会产生一个包含所有行的分区。

对于每一行，在它的分区中的行集被称为它的窗口帧。一些窗口函数只作用在窗口帧中的行上，而不是整个分区。默认情况下，如果使用ORDER BY，则帧包括从分区开始到当前行的所有行，以及后续任何与当前行在ORDER BY子句上相等的行。如果ORDER BY被忽略，则默认帧包含整个分区中所有的行。

SELECT salary, sum(salary) OVER () FROM empsalary;
 salary | sum
--------+-------
5200 | 47100
 5000 | 47100
 3500 | 47100
 4800 | 47100
 3900 | 47100
 4200 | 47100
 4500 | 47100
 4800 | 47100
 6000 | 47100
 5200 | 47100
(10 rows)

如上所示，由于在OVER子句中没有ORDER BY，窗口帧和分区一样，而如果缺少PARTITION BY则和整个表一样。换句话说，每个合计都会在整个表上进行，这样我们为每一个输出行得到的都是相同的结果。但是如果我们加上一个ORDER BY子句，我们会得到非常不同的结果：

SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
 salary | sum
--------+-------
 3500 | 3500
 3900 | 7400
 4200 | 11600
 4500 | 16100
 4800 | 25700
 4800 | 25700
 5000 | 30700
 5200 | 41100
 5200 | 41100
 6000 | 47100
(10 rows)

这里的合计是从第一个（最低的）薪水一直到当前行，包括任何与当前行相同的行（注意相同薪水行的结果）。

窗口函数只允许出现在查询的SELECT列表和ORDER BY子句中。它们不允许出现在其他地方，例如GROUP BY、HAVING和WHERE子句中。这是因为窗口函数的执行逻辑是在处理完这些子句之后。另外，窗口函数在非窗口聚集函数之后执行。这意味着可以在窗口函数的参数中包括一个聚集函数，但反过来不行。如果需要在窗口计算执行后进行过滤或者分组，我们可以使用子查询。例如：

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;

上述查询仅仅显示了内层查询中rank低于3的结果。
当一个查询涉及到多个窗口函数时，可以将每一个分别写在一个独立的OVER子句中。但如果多个函数要求同一个窗口行为时，这种做法是冗余的而且容易出错的。替代方案是，每一个窗口行为可以被放在一个命名的WINDOW子句中，然后在OVER中引用它。例如：

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

继承

继承是面向对象数据库中的概念。它展示了数据库设计的新的可能性。
让我们创建两个表：表cities和表capitals。自然地，首都也是城市，所以我们需要在列举所有城市的时候也隐式地包含首都。

CREATE TABLE cities (
 name text,
 population real,
 altitude int -- (in ft)
);
CREATE TABLE capitals (
 state char(2)
) INHERITS (cities);

在PostgreSQL中，一个表可以从0个或者多个表继承。

例如，如下查询可以寻找所有海拔500尺以上的城市名称，包括州首都：

SELECT name, altitude
 FROM cities
 WHERE altitude > 500;

查找所有海拔高于500尺且不是州首府的城市：

SELECT name, altitude
 FROM ONLY cities
 WHERE altitude > 500;

其中cities之前的ONLY用于指示查询只在cities表上进行而不会涉及到继承层次中位于cities之下的其他表。很多我们已经讨论过的命令 — SELECT、UPDATE 和DELETE — 都支持这个ONLY记号。

尽管继承很有用，但是它还未与唯一约束或外键集成，这也限制了它的可用性。

2018-09-14PostgreSQL高级特性

事务

窗口函数

继承

猜你喜欢

热点阅读