Oracle“Partition by”;关键字

小开

最佳答案

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函数来获取部门中最低和最高的工资，然后将其用于计算这个记录工资没有 a子select时，这非常有用，这要快得多。

详细信息请阅读链接的AskTom文章。

小开

它是名为analytics的SQL扩展。select语句中的“over”告诉oracle该函数是一个分析函数，而不是按函数分组。使用分析的优势在于，您可以通过一次数据传递来收集总和、计数和更多信息，而不是使用子选择或更糟糕的PL/SQL来循环数据。

乍一看确实令人困惑，但很快就会成为你的第二天性。没人比汤姆·凯特更能解释了。所以上面的链接很棒。

当然，读取文档是必须的。

小开

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

这里我们正在获取各个部门的计数。至于第10部门，我们在表emp中有4条记录，与第20和30部门的结果相似

小开

公认的答案很好地解释了这个概念，但我发现一个人看到的例子越多，就越能理解。下面是一个增量的例子:

1) 老板说“让我按品牌分组的库存项目数量”

你说: "no problem"

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     |
+--------------+---------------+----------+

等等……

小开

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。