Oracle“分区依据”关键字

PARTITION BY 子句设置将用于 OVER 子句中每个“GROUP”的记录范围。

在您的示例 SQL 中，DEPT_COUNT 将为每个员工记录返回该部门内的员工人数。 （就好像您正在对 emp 表进行去规范化；您仍然返回 emp 表中的每条记录。）

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），那么您可以计算该州有多少个部门。

这就像在不聚合结果集（即删除匹配记录）的情况下获取 GROUP BY（SUM、AVG 等）的结果。

例如，当您使用 LAST OVER 或 MIN OVER 函数获取部门中的最低和最高薪水，然后在计算此记录的薪水时使用它时很有用没有子选择，这要快得多。

阅读链接的 AskTom article 了解更多详情。

---

公认的答案很好地解释了这个概念，但我发现看到的例子越多，它就越好。这是一个增量示例：

1) 老板说“给我按品牌分组的库存商品数量”

你说：“没问题”

SELECT 
      BRAND
      ,COUNT(ITEM_ID) 
FROM 
      ITEMS
GROUP BY 
      BRAND;

结果：

+--------------+---------------+
|  Brand       |   Count       | 
+--------------+---------------+
| H&M          |     50        |
+--------------+---------------+
| Hugo Boss    |     100       |
+--------------+---------------+
| No brand     |     22        |
+--------------+---------------+

2）老板说“现在给我一份所有物品的清单，包括他们的品牌和各自品牌拥有的物品数量”

你可以试试：

 SELECT 
      ITEM_NR
      ,BRAND
      ,COUNT(ITEM_ID) 
 FROM 
      ITEMS
 GROUP BY 
      BRAND;

但是你得到：

ORA-00979: not a GROUP BY expression 

这就是 OVER (PARTITION BY BRAND) 的用武之地：

 SELECT 
      ITEM_NR
      ,BRAND
      ,COUNT(ITEM_ID) OVER (PARTITION BY BRAND) 
 FROM 
      ITEMS;

这意味着：

COUNT(ITEM_ID) - 获取项目数

OVER - 在行集上

(PARTITION BY BRAND) - 具有相同品牌的

结果是：

+--------------+---------------+----------+
|  Items       |  Brand        | Count()  |
+--------------+---------------+----------+
|  Item 1      |  Hugo Boss    |   100    | 
+--------------+---------------+----------+
|  Item 2      |  Hugo Boss    |   100    | 
+--------------+---------------+----------+
|  Item 3      |  No brand     |   22     | 
+--------------+---------------+----------+
|  Item 4      |  No brand     |   22     | 
+--------------+---------------+----------+
|  Item 5      |  H&M          |   50     | 
+--------------+---------------+----------+

ETC...

---

它是称为分析的 SQL 扩展。 select语句中的“over”告诉oracle该函数是解析函数，而不是按函数分组。使用分析的优势在于，您只需遍历一次数据就可以收集总和、计数等更多信息，而不是使用子选择或更糟的 PL/SQL 循环遍历数据。

乍一看确实令人困惑，但这很快就会成为第二天性。没有人能比 Tom Kyte 更好地解释它。所以上面的链接很棒。

当然，阅读 documentation 是必须的。

---

EMPNO     DEPTNO DEPT_COUNT

 7839         10          4
 5555         10          4
 7934         10          4
 7782         10          4 --- 4 records in table for dept 10
 7902         20          4
 7566         20          4
 7876         20          4
 7369         20          4 --- 4 records in table for dept 20
 7900         30          6
 7844         30          6
 7654         30          6
 7521         30          6
 7499         30          6
 7698         30          6 --- 6 records in table for dept 30

在这里，我们正在获取各个 deptno 的计数。至于 deptno 10，我们在表 emp 中有 4 条记录，对于 deptno 20 和 30 也有类似的结果。

---

over partition 关键字就像我们通过 client_id 对数据进行分区一样，创建每个客户端 id 的子集

select client_id, operation_date,
       row_number() count(*) over (partition by client_id order by client_id ) as operationctrbyclient
from client_operations e
order by e.client_id;

此查询将返回 client_id 完成的操作数

---

我认为，这个例子暗示了分区如何工作和分组如何工作的细微差别。我的示例来自 Oracle 12，如果我的示例恰好是一个编译错误。

我试过了 ：

SELECT t.data_key
,      SUM ( CASE when t.state = 'A' THEN 1 ELSE 0 END) 
OVER   (PARTITION BY t.data_key) count_a_rows
,      SUM ( CASE when t.state = 'B' THEN 1 ELSE 0 END) 
OVER   (PARTITION BY t.data_key) count_b_rows
,      SUM ( CASE when t.state = 'C' THEN 1 ELSE 0 END) 
OVER   (PARTITION BY t.data_key) count_c_rows
,      COUNT (1) total_rows
from mytable t
group by t.data_key  ---- This does not compile as the compiler feels that t.state isn't in the group by and doesn't recognize the aggregation I'm looking for

然而，这按预期工作：

SELECT distinct t.data_key
,      SUM ( CASE when t.state = 'A' THEN 1 ELSE 0 END) 
OVER   (PARTITION BY t.data_key) count_a_rows
,      SUM ( CASE when t.state = 'B' THEN 1 ELSE 0 END) 
OVER   (PARTITION BY t.data_key) count_b_rows
,      SUM ( CASE when t.state = 'C' THEN 1 ELSE 0 END) 
OVER   (PARTITION BY t.data_key) count_c_rows
,      COUNT (1) total_rows
from mytable t;

根据外部键“data_key”产生每个状态的元素数量。因此，如果 data_key = 'APPLE' 有 3 行状态为“A”，2 行状态为“B”，一行状态为“C”，“APPLE”的对应行将是“APPLE”, 3, 2 , 1, 6。

---