Ответ 1
300 тыс. строк - это не огромная таблица. Мы часто видим 300 миллионов таблиц строк.
Самая большая проблема с вашим запросом заключается в том, что вы используете коррелированный подзапрос, поэтому он должен повторно выполнить подзапрос для каждой строки во внешнем запросе.
Часто бывает, что вам не нужно выполнять всю свою работу в одном выражении SQL. Есть преимущества разбить его на несколько простых операторов SQL:
- Легче кодировать.
- Легче оптимизировать.
- Легче отлаживать.
- Легче читать.
- Легче поддерживать, если/когда вам нужно выполнять новые требования.
Количество покупок
SELECT customer, COUNT(sale) AS number_of_purchases
FROM sales
GROUP BY customer;
Для этого запроса лучше всего использовать индекс продаж (клиент, продажа).
Последняя покупка
Это проблема greatest-n-per-group, которая часто возникает.
SELECT a.customer, a.sale as max_sale
FROM sales a
LEFT OUTER JOIN sales b
ON a.customer=b.customer AND a.dates < b.dates
WHERE b.customer IS NULL;
Другими словами, попробуйте сопоставить строку a с гипотетической строкой b с тем же клиентом и более высокой датой. Если такой строки не найдено, то a должен иметь наибольшую дату для этого клиента.
Для этого запроса лучше всего использовать индекс продаж (клиент, даты, продажа).
Если у вас может быть более одной продажи для клиента в эту самую большую дату, этот запрос вернет более одной строки для каждого клиента. Вам нужно будет найти еще один столбец, чтобы сломать галстук. Если вы используете первичный ключ с автоматическим приращением, он подходит как тай-брейкер, потому что он гарантированно уникален и имеет тенденцию увеличиваться в хронологическом порядке.
SELECT a.customer, a.sale as max_sale
FROM sales a
LEFT OUTER JOIN sales b
ON a.customer=b.customer AND (a.dates < b.dates OR a.dates = b.dates and a.id < b.id)
WHERE b.customer IS NULL;
Общий объем покупок, когда он имеет положительное значение
SELECT customer, SUM(sale) AS total_purchases
FROM sales
WHERE sale > 0
GROUP BY customer;
Для этого запроса лучше всего использовать индекс продаж (клиент, продажа).
Вам следует рассмотреть возможность использования NULL для обозначения отсутствующей стоимости продажи вместо -1. Совокупные функции, такие как SUM() и COUNT() игнорируют NULL, поэтому вам не нужно использовать предложение WHERE, чтобы исключить строки с продажей < 0.
Re: ваш комментарий
Теперь у меня есть таблица с полями год, квартал, total_sale (относительно пары (год, квартал)) и продажа. То, что я хочу собрать, - это информация о определенном периоде: в этом квартале, кварталах, году 2011... Информация должна быть разделена на лучших клиентов, с большими продажами и т.д. Можно ли получить последнюю покупную стоимость у клиентов с помощью total_purchases больше 5?
Пять лучших клиентов за четвертый квартал 2012 года
SELECT customer, SUM(sale) AS total_purchases
FROM sales
WHERE (year, quarter) = (2012, 4) AND sale > 0
GROUP BY customer
ORDER BY total_purchases DESC
LIMIT 5;
Я бы хотел протестировать его против реальных данных, но я считаю, что для этого запроса лучше всего использовать индекс продаж (год, квартал, клиент, продажа).
Последняя покупка для покупателей с полной покупкой > 5
SELECT a.customer, a.sale as max_sale
FROM sales a
INNER JOIN sales c ON a.customer=c.customer
LEFT OUTER JOIN sales b
ON a.customer=b.customer AND (a.dates < b.dates OR a.dates = b.dates and a.id < b.id)
WHERE b.customer IS NULL
GROUP BY a.id
HAVING COUNT(*) > 5;
Как и в предыдущем запросе наибольшего числа n-групп, индекс для продаж (клиент, даты, продажа) был бы лучшим для этого запроса. Вероятно, он не может оптимизировать как объединение, так и группу, поэтому это приведет к временной таблице. Но по крайней мере, это будет делать только временную таблицу вместо многих.
Эти запросы достаточно сложны. Вы не должны пытаться написать один SQL-запрос, который может дать все эти результаты. Вспомните классическую цитату Брайана Кернигана:
Всем известно, что отладка в два раза сложнее, чем запись программы в первую очередь. Итак, если вы настолько умны, насколько можете быть, когда пишете его, как вы его отлаживаете?