Использование Over Partition в sql по сравнению с group by

Учитывая приведенный ниже код создания таблицы, существуют ли альтернативные методы для отображения того же результата в

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

используя group by и count(*)? И какая разница в этом случае?

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;
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
1
0
351
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 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% одно и то же. Разница в том, что исходный код вернет количество colour, даже если значение равно NULL. Этот код возвращает 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;

Большое спасибо за усилия, но что означает b2?

Abbas Fadhil 20.12.2020 17:04

@АббасФадхил . . . Это *псевдоним таблицы*(.

Gordon Linoff 20.12.2020 17:49

Я имею в виду, я не мог понять, почему вы использовали его в [b2.colour = b.color], когда оба они ссылаются на одно и то же значение, нет ли способа обойти это без использования других псевдонимов? это как сказать [если 1=1 -> возвращаемое значение]

Abbas Fadhil 20.12.2020 18:32

@АббасФадхил . . . Вот как работает коррелированный подзапрос.

Gordon Linoff 20.12.2020 19:08

Другие вопросы по теме