Проблемы с группировкой. Как сделать тоже самое в PostgreSQL?

Question

[VanZan]

Есть запрос на получение данных по жилым комплексам (ЖК).

В условия запроса динамически могут подставляться параметры, относящиеся не только к жилым комплексам, но и к квартирам в них, а также корпусам и секциям.

На MySQL сразу выяснилось, что джоинить квартиры (а также корпуса и секции) к жилым комплексам астрономически долго, просто потому, что квартир много, а комплексов мало. Гораздо быстрее делать наоборот - брать квартиры и присоединять к ним жилые комплексы, а также все остальные сущности по наличию. Пока у ЖК есть квартиры, всё в порядке. Опустим этот недостаток.

И, поскольку я использую фреймворк YII2 + ActiveRecord, то получаемый объект квартиры просто служит прослойкой через Relation для доступа к модели и данным жилого комплекса.

В результате работы Query Builder получается такой запрос, который отлично работает в MySQL. Дополнительные условия убрал, чтобы не запутать.

SELECT "flat".* FROM "flat" 
LEFT JOIN "complex" ON "flat"."complex_id" = "complex"."id"
LEFT JOIN "building" ON "flat"."building_id" = "building"."id"
LEFT JOIN "section" ON "flat"."section_id" = "section"."id"
GROUP BY "complex"."id" 
ORDER BY "complex"."rate" DESC, "flat"."price_call", "complex"."room_price_min" 
LIMIT 30;

Важно заметить - чтобы получить по одной квартире для каждого ЖК, нужно сгруппировать данные по идентификатору GROUP BY "complex"."id".

Всё прекрасно работает.

Но в PostgreSQL 12 GROUP BY так делать не умеет:

Grouping error: 7 ERROR: column "flat.id" must appear in the GROUP BY clause or be used in an aggregate function

PostgreSQL просит добавить flat.id в условия группировки. Мне не жалко, но это приводит к тому, что SELECT снова возвращает все квартиры и на выходе у меня десятки тысяч одинаковых ЖК.

Как аналогичную задачу можно решить в постгресе?

Comments

[Akina]

На MySQL сразу выяснилось, что джоинить квартиры (а также корпуса и секции) к жилым комплексам астрономически долго, просто потому, что квартир много, а комплексов мало. Гораздо быстрее делать наоборот - брать квартиры и присоединять к ним жилые комплексы, а также все остальные сущности по наличию.

Комментарий к этой фразе один-единственный - бред сивой кобылы. Если в запросе, что в тексте вопроса, изменить порядок связывания - получится запрос с совершенно иной логикой, дающий совершенно другие результаты.

Но в PostgreSQL 12 GROUP BY так делать не умеет

Постгресс делает правильно. А вот MySQL - "какой бы бред не получился, лишь бы работало". Распечатайте крупно, повесьте на стену и заучите - Звезда используется только в COUNT(*), вол всех остальных случаях поля перечисляются все, которые нужны, поштучно, по одному. Отступать от этого правила можно лишь тем, кто чётко понимает последствия.

получается такой запрос

А какой великий смысл тут в именно LEFT JOIN?

Answer 1

[galaxy]

SELECT * FROM (
	SELECT DISTINCT ON ("complex"."id") "complex".*, "flat".*
	FROM "flat" 
	LEFT JOIN "complex" ON "flat"."complex_id" = "complex"."id"
	LEFT JOIN "building" ON "flat"."building_id" = "building"."id"
	LEFT JOIN "section" ON "flat"."section_id" = "section"."id" 
	ORDER BY "complex"."id", "flat"."price_call"
) q
 ORDER BY rate DESC, room_price_min
 LIMIT 30

Comments

[VanZan]

Почти получилось, но проблема с сортировкой:

ERROR:  column reference "rate" is ambiguous
LINE 9:  ORDER BY "complex"."rate" DESC, "complex"."room_price_min"

[galaxy]

VanZan, я исправил запрос.
На всякий случай, если rate есть также и в таблице flat:

SELECT * FROM (
  SELECT DISTINCT ON ("complex"."id") "complex".rate as c_rate, "complex".room_price_min, "flat".*
  FROM "flat" 
  LEFT JOIN "complex" ON "flat"."complex_id" = "complex"."id"
  LEFT JOIN "building" ON "flat"."building_id" = "building"."id"
  LEFT JOIN "section" ON "flat"."section_id" = "section"."id" 
  ORDER BY "complex"."id", "flat"."price_call"
) q
 ORDER BY c_rate DESC, room_price_min
 LIMIT 30

[VanZan]

galaxy, отлично, спасибо!

Answer 2

[Slava Rozhnev]

Ваш запрос не корректен, группируя по вы не можете выбирать "flat".* вам следует выбирать "complex"."id", "complex"."rate" , В случае если нужо - использовать аггрегатные функции с данными квартир MIN("flat"."price_call")

SELECT "complex"."id",  "complex"."rate", MIN("flat"."price_call") "min_flat_price_call" 
FROM "flat" 
LEFT JOIN "complex" ON "flat"."complex_id" = "complex"."id"
LEFT JOIN "building" ON "flat"."building_id" = "building"."id"
LEFT JOIN "section" ON "flat"."section_id" = "section"."id"
GROUP BY "complex"."id",  "complex"."rate" 
ORDER BY 
    "complex"."rate"  DESC, 
    "min_flat_price_call",  
    "complex"."room_price_min" 
LIMIT 30;

Answer 3

[CherAlexV]

Попробуйте ранжирование.

select 
*
from
(
  SELECT 
  flat.*,
  row_number() over (partition by complex.id order by  complex.rate DESC, flat.price_call, complex.room_price_min) as nn
  FROM flat 
  LEFT JOIN complex ON flat.complex_id = complex.id
  LEFT JOIN building ON flat.building_id = building.id
  LEFT JOIN section ON flat.section_id = section.id
) as x
where x.nn = 1
LIMIT 30;