Как составить запрос?

Question

[Krechet]

Приветствую Вас, уважаемые хаброжители.
Однажды я задавал вопрос http://habrahabr.ru/qa/20390/ по оптимизации SQL-запроса (спасибо всем кто откликнулся).
Там необходимо было выбрать все объявления для устройства показа в которых нет категорий, отсутствующих в категориях устройства показа.

Теперь мне необходимо выбрать все устройства показа для конкретного объявления по такому же условию.
есть табличка devices_categories (deviceId, catId)
object_categories (objectId,categoryId)

Нужно для конкретного objectId выбрать те deviceId которые удовлетворяют условию «у object нет категорий, отсутствующих у device». Но нужно показывать те deviceId у которых набор категорий шире набора у objectId.

Заранее огромное спасибо за помощь. уже голову сломал.

Comments

[Krechet]

СУБД PostgreSQL

Answer 1

[Krechet]

Вот пока только это на ум пришло… при первичном тестировании вроде работает.
SELECT dc."deviceId" FROM "devices_categories" dc, "adv_objects_categories" oc WHERE dc."catId" = oc."categoryId" AND oc."objectId"=16 group by dc."deviceId" having count(dc."catId")=(select count(*) from "adv_objects_categories" WHERE "objectId"=16)

Comments

[RedQuark]

Меня всегда настораживали конструкции

from a,b,c,d where ...

вместо явного join. Среде выполнения остается много возможностей по не оптимальному выполнению кода. Например: слепить cross join и только затем проверять where.

[Krechet]

Если перепишете этот же запрос на join-ы — буду оч признателен)

[RedQuark]

Думаю разочарую вас.

select  Q.* from devices Q where Q.devices_id not in
(
select B.devices_id 
from    object_categories C 
inner join devices_categories B  on B.categoryId=C.categoryId and C.objectId=@objectId
GROUP BY B.devices_id Having COUNT(*)=(select count(*) from devices_categories Z where Z.objectId=@objectId)
)

P.S. интересно кто минус поставил, вроде у вас все верно

[RedQuark]

Опечатка:
Q.devices_id in

[Krechet]

Спасибо, попробую Ваш вариант. Правда что в Вашем, что в моем случае смущает конструкция IN (NOT IN) и SELECT COUNT()… но за неимением лучшего пользуемся тем что есть.
P.S. да, за минус интересно… хотябы конструктивной критики послушать.

[RedQuark]

Ага, запрос не отличается красотой. Думал написать второй вариант через справочник категорий:

... devices_id not in (
select devices_categories.devices_id
from categories
left join devices_categories on ...
left join object_categories on ...
where devices_categories.categories_id is null  and object_categories.categories_id is not null
)

но как он будет соотноситься по времени производительности с первым вариантом не знаю.

[tonyzorin]

Вот мой вариант (чуть подкорректированный вариант Krechet-а):

select deviceId,objectId 
from devices_categories d, (select objectId,categoryId,count(*) over (partition by objectId) cnt from object_categories) o
where d.catId=o.categoryId
--and objectId = ?
group by deviceId,objectId
having count(d.catId)=max(o.cnt)

Кстати, RedQuark написал, что «Среде выполнения остается много возможностей по не оптимальному выполнению кода», хотя это не совсем так. Планировщику выполнения запроса может потребоваться больше времени для выбора оптимального соединения таблиц и возможно, что запрос выполнится не оптимальным образом.
Если указать порядок соединений самому с помощью join-ов, то это сократит время для Планировщика (так как ему не надо думать о выборе лучшего варианта), но сам такой порядок может быть не оптимальным.

Так как статистики по тем таблицам нет, то лучше даже не указывать порядок соединения — по двум таблицам СУБД сама сможет найти оптимальный вариант соединения.

Answer 2

[youlose]

1. Не очень понятное описание задания, можно привести пример данных этих таблиц и желаемый результат?
2. «набор категорий шире» — это там где количество категорий устройства больше чем у объекта?

Comments

[Krechet]

у девайса 1 есть категории 1,2,5,6
у девайса 2 есть категории 1,3,5
у девайса 3 есть категории 1,2,3,4,5,6,7,8
у объекта 1 есть категории 1,2
у объекта 2 есть категории 1,2,3,4,5

при выборке для объекта 1 должен показаться только девайс 1 и 3, для объекта 2 — девайс 3

[Анатолий]

В вашем случае нужно взглянуть на тип данных set он содержит битовую маску и одно поле может содержать до 64 значений. В итоге вы просто битовыми операциями сможете выбирать данные именно как вам нужно

[Анатолий]

Еще как вариант группировка + group_concat + substr (как бы это убого ни звучало, оно поможет :)

[Krechet]

Ваверное вы меня не поняли.
Есть табличка devices_categories с полями deviceId | catId
Есть табличка object_categories с полями objectId | catId
Вот из табличке devices_categories нужно выбрать айдишники устройств, по условию описанному выше.
Либо я не понимаю причем тут substr…

[Анатолий]

да, с substr я прозевал, айдишники не попорядку, а вот на поле set можете взглянуть если связей < 64

[Krechet]

связей больше будет…

Answer 3

[Анатолий]

Вот вам псевдокод:

FROM devices_categories
LEFT JOIN object_categories on ...
GROUP BY devices_categories... HAVING count(object_categories...) = 0

Answer 4

[Александр Коротков]

Можно через массивы сделать:

SELECT
	"deviceId"
FROM
	devices_categories
GROUP BY
	"deviceId"
HAVING
	array_agg("catId") @> (
		SELECT
			array_agg("categoryId")
		FROM
			object_categories
		WHERE "objectId" = $1);

Кроме этого, если сделать вместо (или в дополнение к) devices_categories массив catIds в таблице device'ов, то можно будет построить по нему GIN индекс и такой запрос будет очень быстро выполняться.

Comments

[Krechet]

Интересный вариант. Надо будет попробовать