Рассчитать промежуточную сумму в MySQL

У меня есть этот запрос MySQL:

SELECT DAYOFYEAR(`date`)  AS d, COUNT(*) 
FROM  `orders` 
WHERE  `hasPaid` > 0
GROUP  BY d
ORDER  BY d

Который возвращает что-то вроде этого:

d  | COUNT(*) |
20 |  5       |
21 |  7       |
22 | 12       |
23 |  4       |

То, что я действительно хотел бы, является другой колонкой в ​​конце, чтобы показать промежуточный итог:

d  | COUNT(*) | ??? |
20 |  5       |   5 |
21 |  7       |  12 |
22 | 12       |  24 |
23 |  4       |  28 |

Это возможно?

Источник

7 ответов

Решение

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

SET @runtot:=0;
SELECT
   q1.d,
   q1.c,
   (@runtot := @runtot + q1.c) AS rt
FROM
   (SELECT
       DAYOFYEAR(`date`) AS d,
       COUNT(*) AS c
    FROM  `orders`
    WHERE  `hasPaid` > 0
    GROUP  BY d
    ORDER  BY d) AS q1

Это даст вам дополнительный столбец RT (промежуточный итог). Не пропустите инструкцию SET вверху, чтобы сначала инициализировать переменную промежуточного итога, иначе вы просто получите столбец значений NULL.

Источник 
SELECT 
   DAYOFYEAR(O.`date`)  AS d, 
   COUNT(*),
   (select count(*) from `orders` 
       where  DAYOFYEAR(`date`) <= d and   `hasPaid` > 0)
FROM  
  `orders` as O
WHERE  
  O.`hasPaid` > 0
GROUP  BY d
ORDER  BY d

Это потребует некоторой синтаксической настройки (у меня нет MySQL для тестирования), но это показывает вам идею. Подзапрос должен просто вернуться назад и сложить все свежее, что вы уже включили во внешний запрос, и он должен делать это для каждой строки.

Посмотрите на этот вопрос о том, как использовать соединения для достижения того же.

Для решения проблем, связанных с ухудшением производительности с ростом данных: Так как есть макс. 366 дней в году, и я предполагаю, что вы не выполняете этот запрос в течение нескольких лет, подзапрос будет оцениваться до 366 раз. С правильными индексами даты и флагом hasPaid все будет в порядке.

Источник

Начиная с MySQL 8, вы будете использовать оконные функции для этого вида запроса:

SELECT dayofyear(`date`) AS d, count(*), sum(count(*)) OVER (ORDER BY dayofyear(`date`))
FROM `orders`
WHERE `hasPaid` > 0
GROUP BY d
ORDER BY d

В приведенном выше запросе агрегатная функция count(*) вложен в оконную функцию sum(..) OVER (..), что возможно из-за логического порядка операций в SQL. Если это слишком запутанно, вы можете легко прибегнуть к использованию производной таблицы или WITH предложение, чтобы лучше структурировать ваш запрос:

WITH daily (d, c) AS (
  SELECT dayofyear(`date`) AS d, count(*)
  FROM `orders`
  WHERE `hasPaid` > 0
  GROUP BY d
)
SELECT d, c, sum(c) OVER (ORDER BY d)
ORDER BY d
Источник

Можно рассчитать текущий баланс, используя временную таблицу в MySQL. Следующий запрос должен работать:

CREATE TEMPORARY table orders_temp1 (SELECT id, DAYOFYEAR(`date`)  AS d, COUNT(*) as total FROM  `orders` WHERE  `hasPaid` > 0 GROUP BY d ORDER  BY d);
CREATE TEMPORARY table orders_temp2 (SELECT * FROM orders_temp1);
SELECT d, total, (SELECT SUM(t2.total) FROM orders_temp2 t2 WHERE t2.id<=t1.id) as running_total FROM orders_temp1 t1;

Временная таблица используется для организации запроса. Обратите внимание, что временная таблица существует только на время соединения с сервером MySQL.

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

Источник

Если у вас нет другого выбора, кроме как сделать это в SQL, я бы суммировал результаты на языке программирования, который выполняет запрос. Подобное вложение будет очень медленным по мере роста стола.

Источник

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

Источник

Это одно из немногих мест, где курсоры работают быстрее, чем запросы на основе набора, если производительность критична, я бы либо

  • Сделайте это вне MySql или
  • Используйте MySql 5 курсоров
Источник

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

Источник
Другие вопросы по тегам