SELECT语句中的执行顺序 [英] Execution order in SELECT statement
问题描述
我正在阅读有关postgreSQL SELECT语句的文档>因为我想找到处理SELECT中SQL语句的顺序。
I'm reading a documentation for postgreSQL SELECT statement as I want to find the order in which SQL statements in SELECT are processed.
在文档中它说：
SELECT从零个或多个表中检索行。 SELECT的一般处理如下：（...）
SELECT retrieves rows from zero or more tables. The general processing of SELECT is as follows: (...)
- （...）SELECT DISTINCT ON消除与on匹配的行所有指定的表达式。 （...）
- 如果指定了ORDER BY子句，则返回的行将以指定的顺序排序。 （...）
它进一步说（在 DISTINCT子句描述）：
DISTINCT ON表达式的解释规则与ORDER BY的
相同（请参见上文）。注意，除非使用ORDER BY来确保所需的行
首先出现，否则每个集合的第一行都是
不可预测的。 （...）
The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. (...)
DISTINCT ON表达式必须与最左边的ORDER BY表达式匹配。 ORDER BY子句通常会包含其他表达式，这些表达式确定每个DISTINCT ON组中行的期望优先级。
The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s). The ORDER BY clause will normally contain additional expression(s) that determine the desired precedence of rows within each DISTINCT ON group.
期望ORDER BY应该修改DISTINCT ON的结果，并且由于DISTINCT ON可能（可能会）产生不可预测的结果，因此-简单地说-如果没有其他SELECT查询，它是无用的。
My natural expectation is that ORDER BY should modify the result of DISTINCT ON and since DISTINCT ON may (and probably will) give unpredictable results, it is - simply put - useless, if not preceded with another SELECT query.
SELECT语句中语句的实际执行顺序是什么？
尤其是：为什么在DISTINCT ON之前处理ORDER BY？ strong>
What is the actual execution order of statements in SELECT statement?
In particular: why is ORDER BY processed before DISTINCT ON?
我不是PostgreSQL和SQL的有经验的用户，所以我不知道我在问什么正确的问题（ XY问题）。如果您认为此问题应该是 DISTINCT ON的工作原理到底是什么？，或者甚至是 在SELECT语句中关键字的位置重要吗？或其他任何问题，请适当地编辑问题。
I'm not an experienced user of postgreSQL and SQL thus I don't know if I'm asking the right question (XY Problem). If you think this question should be "How exactly does DISTINCT ON work?" or maybe even "Does the placement of keywords in SELECT statement matter?" or anything else, please edit the question appropriately.
推荐答案
第一个处理顺序在SQL中有点误导。语句在执行前被编译。实际的执行看起来可能与SQL本身有很大的不同（实际上，执行以DAG表示（有向无环图），它看起来像 SELECT 语法）。
First "order of processing" is a bit misleading in SQL. The statements are compiled before execution. The actual execution might look very different from the SQL itself (in fact, the execution is represented as a DAG -- directed acyclic graph -- which looks nothing like SELECT syntax).
DISTINCT ON 是Postgres扩展名。这非常方便-括号中的每个值让您获得一行。并且，您可以控制哪一行。
DISTINCT ON is a Postgres extension. It is quite handy -- letting you get one row per value in the parentheses. And, you can control which row.
假设您有这样的数据：
a b c
A1 3 1
A1 2 2
A1 4 3
A2 6 4
A2 1 5
当您要指定要 one <时使用 distinct / em>每个值的行。并且，您可以指定所需的值。例如，对于每个 a ，要获得具有最低值 b 的行：
You use distinct on when you want to specify that you want one row per value. And, it let's you specify the value that you want. For instance, to get the row with the lowest value of b for each a:
select distinct on (a) a, b, c
from t
order by a, b asc;
获取具有最高值 b 每个 a ：
select distinct on (a) a, b, c
from t
order by a, b desc;
括号中的表达式必须与中的前导表达式匹配
The expressions in the parentheses need to match the expressions the leading expressions in the order by.
您不会通过阅读文档来了解其工作原理。您需要使用它。
You are not going to get a feel for how this works by reading the documentation. You need to use it.
此功能可以由带有 row_number（）和<$外部查询中的c $ c> where 子句。这可能就是为什么其他数据库没有采用类似功能的原因。
This functionality can be replaced by a subquery with row_number() and a where clause in the outer query. This is probably why other databases have not adapted similar functionality.
这篇关于SELECT语句中的执行顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
