oracle PARTITION BY 1 是什么意思？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3338415/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What does PARTITION BY 1 mean?
提问by Everyone
For a pair of cursors where the total number of rows in the resultset is required immediately after the first FETCH, ( after some trial-and-error ) I came up with the query below
对于在第一次 FETCH 之后立即需要结果集中总行数的一对游标，（经过一些反复试验）我想出了下面的查询
SELECT
col_a,
col_b,
col_c,
COUNT(*) OVER( PARTITION BY 1 ) AS rows_in_result
FROM
myTable JOIN theirTable ON
myTable.col_a = theirTable.col_z
GROUP BY
col_a, col_b, col_c
ORDER BY
col_b
Now when the output of the query is X rows, rows_in_result reflects this accurately.
现在，当查询的输出为 X 行时，rows_in_result 会准确反映这一点。
- What does PARTITION BY 1 mean?
- I thinkit probably tells the database to partition the results into pieces of 1-row each
- PARTITION BY 1 是什么意思？
- 我认为它可能会告诉数据库将结果分成每个 1 行的部分
回答by Tony Andrews
It is an unusual use of PARTITION BY. What it does is put everything into the same partition so that if the query returns 123 rows altogether, then the value of rows_in_result on each row will be 123 (as its alias implies).
这是 PARTITION BY 的不寻常用法。它的作用是将所有内容放入同一个分区，这样如果查询总共返回 123 行，那么每行上的 rows_in_result 值将为 123（正如其别名所暗示的那样）。
It is therefore equivalent to the more concise:
因此它等价于更简洁的：
COUNT(*) OVER ()
回答by Lukas Eder
Databases are quite free to add restrictions to the OVER()clause. Sometimes, either PARTITION BY [...]and/or ORDER BY [...]are mandatory clauses, depending on the aggregate function. PARTITION BY 1may just be a dummy clause used for syntax integrity. The following two are usually equivalent:
数据库可以很自由地向OVER()子句添加限制。有时，两者之一PARTITION BY [...]和/或是ORDER BY [...]强制性子句，具体取决于聚合函数。PARTITION BY 1可能只是一个用于语法完整性的虚拟子句。以下两个通常是等效的：
[aggregate function] OVER ()
[aggregate function] OVER (PARTITION BY 1)
Note, though, that Sybase SQL Anywhere and CUBRIDinterpret this 1as being a column index reference, similar to what is possible in the ORDER BY [...]clause. This might appear to be a bit surprising as it imposes an evaluation order to the query's projection. In your case, this would thenmean that the following are equivalent
但请注意，Sybase SQL Anywhere 和CUBRID将此解释1为列索引引用，类似于ORDER BY [...]子句中可能出现的情况。这可能看起来有点令人惊讶，因为它对查询的投影施加了评估顺序。在您的情况下，这将意味着以下内容是等效的
COUNT(*) OVER (PARTITION BY 1)
COUNT(*) OVER (PARTITION BY col_a)
This curious deviation from other databases' interpretation allows for referencing more complex grouping expressions.
这种与其他数据库解释的奇怪偏差允许引用更复杂的分组表达式。
