Блог

Главная - Вопросы по программированию - Обнаруживает ли PostgreSQL автоматически уникальные записи в предложении group by?

Обнаруживает ли PostgreSQL автоматически уникальные записи в предложении group by?

#postgresql #postgresql-9.5

#postgresql #postgresql-9.5

Вопрос:

Разве в PostgreSQL не обязательно добавлять уникальный столбец в предложение group by?

Обычно я использую Microsoft SQL Server, в котором мы должны добавить все необходимые столбцы в предложение group by.

Запрос PostgreSQL :

 DROP TABLE IF EXISTS temp_invoice_detail;
CREATE TEMP TABLE temp_invoice_detail(
    invoice_id integer,
    item_id integer,
    qty integer,
    warehouse_id integer    
);

ALTER TABLE temp_invoice_detail ADD CONSTRAINT temp_invoice_detail_result_pkey PRIMARY KEY (invoice_id, item_id);

insert into temp_invoice_detail (invoice_id, item_id, qty, warehouse_id) values (1, 1, 100, 1);
insert into temp_invoice_detail (invoice_id, item_id, qty, warehouse_id) values (1, 2, 200, 1);
insert into temp_invoice_detail (invoice_id, item_id, qty, warehouse_id) values (2, 1, 100, 1);

select invoice_id, item_id, sum(qty) as total_qty, warehouse_id
from temp_invoice_detail
group by invoice_id, item_id --should I add "warehouse_id" in group by clause?
order by invoice_id, item_id;

DROP TABLE IF EXISTS temp_invoice_detail;

Я ожидаю, что PostgreSQL отобразит сообщение об ошибке:

столбец «temp_invoice_detail.warehouse_id» должен отображаться в предложении GROUP BY или использоваться в агрегатной функции

Но запрос выполняется успешно и возвращает 3 записи (все 3 записи в инструкции insert выше).

 invoice_id  item_id  total_qty  warehouse_id
1           1        100        1
1           2        200        1
2           1        100        1

В MS-SQL, чтобы этот запрос мог выполняться без ошибок, предложение group by должно быть:

группируйте по invoice_id, item_id, warehouse_id

Комментарии:

1. Какое значение warehouse_id вы ожидаете для каждой (invoice_id, item_id) группы? Лучше всего ответить на этот вопрос и написать соответствующий ANSI запрос, совместимый здесь.

2. Это всего лишь пример запроса. Я только путаю, почему этот запрос не выдает сообщение об ошибке, что «Я должен добавить warehouse_id в предложении group by, потому что оно не включено в функцию aggretate (т. е. SUM)». Я отредактировал вопрос так, чтобы у warehouse_id было только 1 значение, чтобы быть более очевидным. В любом случае, под запросом, совместимым с ANSI, вы имеете в виду, что мне «лучше» добавить warehouse_id в предложение group by?

Ответ №1:

Это потому, что вы определили invoice_id, item_id как первичный ключ таблицы, и группировки по уникальному ключу достаточно, поскольку добавление большего количества столбцов в группу не изменит результат group by .

Цитата из руководства

Когда присутствует GROUP BY или присутствуют какие-либо агрегатные функции, недопустимо, чтобы выражения списка ВЫБОРА ссылались на негруппированные столбцы, кроме как в рамках агрегатных функций или когда негруппированный столбец функционально зависит от сгруппированных столбцов, поскольку в противном случае для негруппированного столбца возвращалось бы более одного возможного значения. Функциональная зависимость существует, если сгруппированные столбцы (или их подмножество) являются первичным ключом таблицы, содержащей негруппированный столбец

(выделено мной)

Это фактически разрешено стандартом SQL. Postgres даже заходит так далеко, что удаляет ненужные столбцы (на основе существования первичных или уникальных ключей) из group by, потому что это повышает производительность.