Oracle“分区依据"关键词 [英] Oracle "Partition By" Keyword

问题描述

有人可以解释partition by关键字的作用，并给出一个简单的示例，以及为什么要使用它吗?我有一个别人编写的SQL查询，我试图弄清楚它的作用.

Can someone please explain what the partition by keyword does and give a simple example of it in action, as well as why one would want to use it? I have a SQL query written by someone else and I'm trying to figure out what it does.

按以下方式进行分区的示例:

An example of partition by:

SELECT empno, deptno, COUNT(*) 
OVER (PARTITION BY deptno) DEPT_COUNT
FROM emp

我在网上看到的示例似乎过于深入.

The examples I've seen online seem a bit too in-depth.

推荐答案

PARTITION BY子句设置将在OVER子句中用于每个组"的记录范围.

The PARTITION BY clause sets the range of records that will be used for each "GROUP" within the OVER clause.

在您的示例SQL中，DEPT_COUNT将为每个员工记录返回该部门内的员工人数. (就好像您要对emp表进行非标称化；您仍然会返回emp表中的每个记录.)

In your example SQL, DEPT_COUNT will return the number of employees within that department for every employee record. (It is as if you're de-nomalising the emp table; you still return every record in the emp table.)

emp_no  dept_no  DEPT_COUNT
1       10       3
2       10       3
3       10       3 <- three because there are three "dept_no = 10" records
4       20       2
5       20       2 <- two because there are two "dept_no = 20" records

如果还有另一列(例如state)，那么您可以计算该州有多少个部门.

If there was another column (e.g., state) then you could count how many departments in that State.

这就像获取GROUP BY(SUM，AVG等)的结果而无需汇总结果集(即删除匹配的记录)一样.

It is like getting the results of a GROUP BY (SUM, AVG, etc.) without the aggregating the result set (i.e. removing matching records).

当您使用LAST OVER或MIN OVER函数获取例如部门的最低和最高薪水，然后将其用于对此记录中的薪水无子选择，速度更快.

It is useful when you use the LAST OVER or MIN OVER functions to get, for example, the lowest and highest salary in the department and then use that in a calculation against this records salary without a sub select, which is much faster.

阅读链接的 AskTom文章了解更多信息.

Read the linked AskTom article for further details.

这篇关于Oracle“分区依据"关键词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！