#mysql #sum #conditional-aggregation
#mysql #сумма #условная агрегация
Вопрос:
Вот запрос, который я пытаюсь выполнить.
SELECT salesrep,
SUM(lines_total) AS OTSUM,
SUM(order_type = 'NEW') OTNEW,
SUM(order_type = 'REPEAT') OTREPEAT,
SUM(lines_total
WHERE order_closed BETWEEN '2020-11-01' AND '2020-11-30') AS OTLMSUM
SUM(lines_total
WHERE order_closed BETWEEN '2020-12-01' AND '2020-12-31') AS OTTMSUM
FROM `ranger_orders`
WHERE order_closed BETWEEN '2020-01-01' AND '2020-12-31'
Конечно, это не удается, но как мне настроить суммы для нескольких диапазонов дат, диапазон дат WHERE в конце должен оставаться как есть.
Для этих строк:
SUM(lines_total WHERE order_closed BETWEEN '2020-11-01' AND '2020-11-30') AS OTLMSUM
SUM(lines_total WHERE order_closed BETWEEN '2020-12-01' AND '2020-12-31') AS OTTMSUM
Комментарии:
1. Если вы не знаете, как выразить проблему в коде, то лучше предоставить описательное описание результата, которого вы пытаетесь достичь, с помощью примеров ввода и вывода, чем публиковать бессмысленный код.
Ответ №1:
Вам нужны CASE
выражения для условной агрегации, например:
SUM(CASE WHEN order_closed BETWEEN '2020-12-01' AND '2020-12-31' THEN lines_total ELSE 0 END) AS OTTMSUM
Для каждой из сумм измените BETWEEN '2020-12-01' AND '2020-12-31'
соответствующим образом.
Ответ №2:
Чтобы данные были выровнены по строкам с помощью salesrep, вам все равно нужно добавить все числа, но вы можете заменить их нулями там, где они находятся вне диапазона….
SUM(IF (order_closed BETWEEN '2020-11-01' AND '2020-11-30', lines_total, 0)) AS OTLMSUM
Вы указали, что получаете синтаксическую ошибку, но это похоже на семантическую ошибку:
SUM(order_type = 'NEW') OTNEW
Вы действительно пытаетесь их сложить? Подсчитать их? Классифицировать их?
Запрос может быть чем-то вроде…
SELECT salesrep,
SUM(lines_total) AS OTSUM,
SUM(IF(order_type = 'NEW', 1, 0)) OTNEW,
SUM(IF(order_type = 'REPEAT', 1, 0)) OTREPEAT,
SUM(IF (order_closed BETWEEN '2020-11-01' AND '2020-11-30', lines_total, 0)) AS OTLMSUM
SUM(if(order_closed BETWEEN '2020-12-01' AND '2020-12-31', lines_total, 0)) AS OTTMSUM
FROM `ranger_orders`
WHERE order_closed BETWEEN '2020-01-01' AND '2020-12-31'
Кроме того, для агрегированных запросов (SUM, AVERAGE, COUNT …) у вас должно быть предложение GROUP BY ….
GROUP BY salesrep
Однако не было бы проще сделать это….
SELECT salesrep,
SUM(lines_total) AS OTSUM,
SUM(IF(order_type = 'NEW', 1, 0)) AS OTNEW,
SUM(IF(order_type = 'REPEAT', 1, 0)) AS OTREPEAT,
DATE_FORMAT(order_closed, '%Y-%m') AS month,
SUM(lines_total) AS otsum
FROM `ranger_orders`
WHERE order_closed BETWEEN '2020-01-01' AND '2020-12-31'
GROUP BY salesrep, DATE_FORMAT(order_closed, '%Y-%m')
Комментарии:
1.
SUM(order_type = 'NEW')
это не семантическая ошибка. Он подсчитывает количество строк, гдеorder_type = 'NEW'
.2. Нет, это не так — вы полагаетесь на побочный эффект MySQL, интерпретирующий истинное логическое значение как ‘1’ и неявное приведение к целому числу. Последнее является стандартной частью SQL-92, но довольно неэффективно. Первое — нет.
3. Да, это так. Я полагаюсь на хорошо документированную функцию MySQL для требования, которое помечено MySQL.
4. Ссылка, пожалуйста.
5. Вы уже упоминали об этом: true оценивается как 1, а false 0, dev.mysql.com/doc/refman/8.0/en/boolean-literals.html