谁能解释一下分区的关键字是做什么的,并给出一个简单的例子,以及为什么要使用它?我有一个由别人写的SQL查询,我试图弄清楚它是做什么的。
划分的例子:
SELECT empno, deptno, COUNT(*)
OVER (PARTITION BY deptno) DEPT_COUNT
FROM emp
我在网上看到的例子似乎有点太深入了。
谁能解释一下分区的关键字是做什么的,并给出一个简单的例子,以及为什么要使用它?我有一个由别人写的SQL查询,我试图弄清楚它是做什么的。
划分的例子:
SELECT empno, deptno, COUNT(*)
OVER (PARTITION BY deptno) DEPT_COUNT
FROM emp
我在网上看到的例子似乎有点太深入了。
当前回答
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所执行的操作数
其他回答
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。
它是名为analytics的SQL扩展。select语句中的“over”告诉oracle该函数是一个分析函数,而不是按函数分组。使用分析的优势在于,您可以通过一次数据传递来收集总和、计数和更多信息,而不是使用子选择或更糟糕的PL/SQL来循环数据。
乍一看确实令人困惑,但很快就会成为你的第二天性。没人比汤姆·凯特更能解释了。所以上面的链接很棒。
当然,阅读文档是必须的。
公认的答案很好地解释了这个概念,但我发现一个人看到的例子越多,就越能理解。下面是一个增量的例子:
老板说:“给我按品牌分组的库存数量”
你说:“没问题”
SELECT
BRAND
,COUNT(ITEM_ID)
FROM
ITEMS
GROUP BY
BRAND;
结果:
+--------------+---------------+
| Brand | Count |
+--------------+---------------+
| H&M | 50 |
+--------------+---------------+
| Hugo Boss | 100 |
+--------------+---------------+
| No brand | 22 |
+--------------+---------------+
老板说:“现在给我一份所有物品的清单,包括它们的品牌和各自品牌的物品数量。”
你可以试试:
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 |
+--------------+---------------+----------+
等等……
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部门也有类似的结果。