Как написать подзапрос в запросе с группировкой?

Question

[Sergey Pashkevich]

Друзья, подскажите пожалуйста, не могу придумать, как написать следующий запрос:

Есть таблица: sales

id INT
total_sum decimal
type varchar
event_date timestamp
...

И таблица: sale_items

sale_id int
quantity decimal
...

Мне нужно получить информацию о sales в сгруппированном виде по event_date, но не могу понять как посчитать сумму всех sale_items в подзапросе. Вот текущий запрос (с закоменченным подзапросом, который делает весь запрос неправильным):

select
       DATE(event_date),
       COUNT(*)                                sales_count,
       COUNT(IF(type = 'refund', 1, NULL))     refunds_count,
       SUM(IF(type = 'sale', total_sum, 0))    revenue,
       AVG(IF(type = 'sale', total_sum, NULL)) avg_check,
#        (select count(*) from sale_items where sale_id=sales.id) as sale_items_count
from sales
group by DATE(event_date);

Возможно ли это выполнить одни запросом? Буду очень благодарен за помощь с запросом.

Answer 1

[galaxy]

как-то так

...
  sum(i.count)
from sales
join (
  select sale_id, count(*) count from sale_items
) i on (i.sale_id = sales.id)
group by DATE(event_date);

Comments

[Sergey Pashkevich]

Не завелось (

[galaxy]

Ну ошибся. В подзапросе:

select sale_id, count(*) count from sale_items group by sale_id

[Sergey Pashkevich]

Благодарю вас за помощь. Сейчас отмечу ваш ответ правильным. А ещё вы не подскажите этот запрос с точки зрения оптимизации ну будет медленным?
Сейчас в таблице sales 500к записей, а в sale_items 800к и они быстро растут.

[Sergey Pashkevich]

Вот что показывает EXPLAIN

[galaxy]

Конечно, будет тормозить без ограничений по event_date. Да и с ограничением подзапрос будет почти наверняка по всей таблице sale_items работать.

[Sergey Pashkevich]

Понял вас, будет возможность выбора периода по event_date, но никто не исключает что пользователь выберет год, если конечно самому это не проверять и большие периоды отклонять.
А Вы не знаете как лучше поступать в таком случае и что можно сделать, чтобы всё-таки оставить выбор большого периода?

[galaxy]

Ничего. Ну сами подумайте: БД должна перелопатить под миллион записей, что тут сделаешь.

Сделайте как вариант агрегированный по дням вариант таблицы (обычно это называется materialized view)

[Sergey Pashkevich]

Т.е. это будет отдельная таблица в которую я буду записывать уже готовые данные по дням (если у меня "день" будет являться минимальным периодом), и она будет заполняться некой "задачей", которая будет брать прошедшие дни и заполнять эту таблицу, Вы что-то такого имели ввиду?

[galaxy]

Да

[Sergey Pashkevich]

Спасибо Вам за помощь.