Небольшой вопрос по SQL

Question

[Ambrose]

Приветствую всех! Есть небольшой вопрос к понимающим SQL:

Предположим, есть у нас в базе компании и продукты. Компаний много, продуктов еще больше.
Каждый продукт сопровождается датой его занесения в базу, причём продукты «скоропортящиеся», т.е. по прошествии N дней продукт уже неактуален.

Требуется получить табличку вида (имя компании, кол-во актуальных продуктов).

Итак, представим, что у нас есть следующие таблички:

companies (id, name);
products (id,name,id_company,data)


Сначала всё работало через два запроса: один брал по очереди каждую компанию, другой проходил по таблице продуктов, находя актуальные и подсчитывая их.
Как известно, выполнение запроса в цикле — худшее из зол, к тому же обе таблички оказались весьма и весьма большими, поэтому такое решение не годится из соображений производительности.

Для повышения быстродействия попробовал использовать левое внешнее соединение таблиц,
дабы иметь в выводе и те компании, у которых есть товары, и те, у которых их нет.

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

select c.name, count( p.id ) as cnt
from company c left join products p on c.id = p.id_company
where to_days(now()) - to_days(p.data) <= 10
group by c.id


Запрос работает быстро, но неверно: из-за условия не выводит те компании, у которых продуктов нет вообще.

Дальше, в силу усталости и ограниченности знаний SQL, голова пока не думает, взываю к вашей помощи…
Реально ли вообще выкрутиться в такой ситуации одним запросом?

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

Answer 1

[ArtemS]

Так будет работать:

select c.name, count( p.id ) as cnt
from company c
left join products p on c.id = p.id_company and  to_days(now()) - to_days(p.data) <= 10 
group by c.id

Answer 2

[vadimbelyaev]

Если я правильно понял, достаточно добавить в WHERE:

OR p.data IS NULL

Comments

[Ambrose]

Честно говоря, не могу понять, но выводит ваш вариант неправильный результат…
Есть компании, которым соответствует некоторое множество записей из products и у них у всех есть data.
И есть компании, которым не соответствует ни одна запись из products

[lashtal]

У вас там ошибка в скобках внутри WHERE: должно быть TO_DAYS(NOW()) — TO_DAYS(p.data) <= 10
и слово OUTER удалите, оно ничего не делает в mysql (как и inner)

[Ambrose]

Спасибо за корректировки, поправил. Это у меня в псевдокоде ошибка со скобками была, я его ручками печатал.

Answer 3

[Владимир Чернышев]

where (to_days(now() — to_days(p.data)) <= 10 OR (to_days(now() — to_days(p.data)) IS NULL

Comments

[Владимир Чернышев]

хотя от предыдущего вариант не отличается, т. к. (to_days(now() — to_days(p.data)) IS NULL эквивалентено p.data IS NULL, а при LEFT JOIN как раз и будет p.data NULL когда нет записей products

Answer 4

[xdenser]

А так не работает?

select c.name, 
         (select count(*)  
          from products p
          where p.id_company = c.id
          and to_days(now()) - to_days(p.data) <= 10) as cnt
from company c 

Answer 5

[lavel]

Неплохо было бы СУБД указать, в принципе относительно универсальным должно быть нечто такое:

select r.name,SUM(r.cnt) from
(
	select c.name, count( p.id ) as cnt
	from company c left join products p on c.id = p.id_company 
	where to_days(now()) - to_days(p.data) <= 10
	group by c.name

	union

	select c.name, 0 as cnt
	from company c left join products p on c.id = p.id_company 
	where to_days(now()) - to_days(p.data) > 10
	group by c.name
) as r

group by r.name

Т.е. фактически тут выполняется два запроса с противоположными условиями. Если MSSQL — можно было бы сделать CROSS APPLY

Comments

[lavel]

Да, забыл про третий случай, когда у компании продуктов нет вовсе, в таком случае можно добавить ещё один UNION, что-то вроде

	union

	select c.name, 0 as cnt
	from company c 
	where c.id not in (select id_company from products)

Answer 6

[fStrange]

тут два приемлемых варианта один через сложный запрос

второй через 2 сравнительно простых запроса

сначала вытаскиваем данные для таблицы продуктов
из этих данных выбираем список ид_компаний через php(или чем там программируете)

по этому списку одним запросом получаем имена компаний

далее формируем из этих данных требуемый вывод.

Answer 7

[Ambrose]

Ответы от VolCh, xdenser и ArtemS похоже подходят, спасибо. Но проблема с производительностью остаётся, видимо придётся сделать, как планировал вначале…

Comments

[Владимир Чернышев]

Низкая скорость может быть из-за использования NOW() в запросе — такие запросы не кэшируются, попробуйте задавать дату в скрипте, если точность до миллисекунд вас не волнует :)

[xdenser]

Надо посмотреть на план. Можно еще попробовать переписать условие так, чтобы с одной стороны неравенства была p.data, а все остальное с другой.
Что то вроде этого:

p.data >= CAST( DATE((now() - INTERVAL 10 DAY )) as DATETIME)

Тогда ему легче будет использовать индекс. Кстати, индексы то в таблицах есть?
А now() выполняется один раз для запроса, так что нет смысла оптимизировать.

[lavel]

Как в этом случае будут выбираться компании у которых есть продукты, но они ВСЕ не актуальны? Или такие компании не должны участвовать в выборке?

[lavel]

Да, простите, туплю.

[Ambrose]

В общем, сделал пока таблицу с engine=memory, в которую пишется ид компании и количество актуальных на текущий момент продуктов. Табличку обновляю раз в день кроном + каждый раз принудительно, когда какая нибудь компания выгружает свои продукты (это тоже не чаще раза в день происходит). Генерация таблички занимает ~ 18 секунд. Для клиента выборка происходит за 0.002 сек. Может быть решение не особо красивое, но мне для моих нужд пока хватит.
p.s. в базе зачем то почти все поля проиндексированы, такой она мне от предшественников досталась :)

[Duke]

В случае с проблемами производительности вот таких вот выборок очень актуально в MySQL использовать подзапрос вместо products, т.е. from company c left join (select id_company from products where to_days(now()) — to_days(p.data) <= 10) as p on c.id = p.id_company

Answer 8

[Duke]

Вот так можно, скорее всего будет работать быстрее чем через обычный left join всей таблицы:
select c.name, count( p.id ) as cnt
from company c
left join (select id, id_company from products where to_days(now()) — to_days(p.data) <= 10) as p on c.id = p.id_company
group by c.id