在sql中使用Over Partition与group by进行比较

Question

给定下面的表创建代码，是否有其他方法可以向显示相同的结果

select b.*, count(*) over (partition by colour) bricks_total 
from bricks b;

使用CCD_ 1和CCD_？在这种情况下有什么不同？

create table bricks 
(
brick_id integer,
colour   varchar2(10),
shape    varchar2(10),
weight   integer
);
insert into bricks values (1, 'blue', 'cube', 1);
insert into bricks values (2, 'blue', 'pyramid', 2);
insert into bricks values (3, 'red', 'cube', 1);
insert into bricks values (4, 'red', 'cube', 2);
insert into bricks values (5, 'red', 'pyramid', 3);
insert into bricks values (6, 'green', 'pyramid', 1);
commit;

Answer 1

此查询将每种颜色的总数放在每行上：

select b.*, count(*) over (partition by colour) as bricks_total
from bricks b;

在使用窗口函数之前，典型的解决方案是关联子查询：

select b.*,
(select count(*) from bricks b2 where b2.colour = b.colour) as bricks_total
from bricks b;

您也可以使用join和聚合来表达这一点：

select b.*, bb.bricks_total
from bricks b join
(select bb.colour, count(*) as bricks_total
from bricks bb
group by bb.colour
) bb
using (colour);

这些并非100%相同。不同之处在于，即使值为NULL，原始代码也会返回colour的计数。此代码返回0。

因此，一个更精确的等价物是：

select b.*,
(select count(*)
from bricks b2
where b2.colour = b.colour or
b2.colour is null and b.colour is null
) as bricks_total
from bricks b;