Bezdepbonusu7Программирование: статьи и ответы
Меню

Вопрос

Как суммировать записи от начального месяца до текущего за месяц

Автор вопроса: Tomasz GrochowskiОтветов: 3

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

Вот пример таблицы

   Date     |  Val
----------- | -----
2017-01-20  | 10
----------- | -----
2017-02-15  | 5
----------- | -----
2017-02-24  | 15
----------- | -----
2017-03-14  | 20

Мне нужно получить следующий вывод (формат даты не имеет значения):

2017-01-20 | 10
2017-02-24 | 30
2017-03-14 | 50

Когда я бегу

SELECT SUM(`val`) as `sum`, DATE(`date`) as `date` FROM table
AND `date` BETWEEN :startDate
AND :endDate GROUP BY year(`date`), month(`date`)

Я получил сумму в месяц, конечно.

Мне ничего не приходит в голову, как красиво вставить один запрос для достижения желаемого эффекта, возможно, W потребуется выполнить несколько вложенных запросов, но, возможно, вы знаете какое-то лучшее решение.

  • В SQL Server я бы использовал оконные функции для получения промежуточного итога, но я не знаю, поддерживает ли MySQL оконные функции и как изменился бы синтаксис, если бы это было так. Michael L. 06.02.2017

sql

Ответы

Ответ 1 · рейтинг 1 · Beowolve

Что-то вроде этого должно работать (не тестировать). Вы также можете решить эту проблему, используя подзапросы, но я думаю, что это будет более затратно. Если вы хотите отсортировать результат по общему значению, вариант подзапроса может быть быстрее.

SET @total:=0;
SELECT
   (@total := @total + q.sum) AS total, q.date
FROM
   (SELECT SUM(`val`) as `sum`, DATE(`date`) as `date` FROM table
AND `date` BETWEEN :startDate
AND :endDate GROUP BY year(`date`), month(`date`)) AS q

Ответ 2 · рейтинг 1 · McNets

Вы можете использовать функцию DATE_FORMAT как для форматирования запроса, так и для группировки.

DATE_FORMAT(дата, формат)

Форматирует значение даты в соответствии со строкой формата.

SELECT Date, @total := @total + val as total
FROM
    (select @total := 0) x,
    (select Sum(Val) as Val, DATE_FORMAT(Date, '%m-%Y') as Date
     FROM st where Date >= '2017-01-01' and Date <= '2017-12-31'
     GROUP BY DATE_FORMAT(Date, '%m-%Y')) y
;

+---------+-------+
| Date    | total |
+---------+-------+
| 01-2017 | 10    |
+---------+-------+
| 02-2017 | 30    |
+---------+-------+
| 03-2017 | 50    |
+---------+-------+

Проверить это можно здесь: http://rextester.com/FOQO81166

Ответ 3 · marcothesane

Попробуй это.

Я использую yearmonth как целое число (год даты, умноженный на 100 плюс месяц даты). Если вы хотите переформатировать, ваш звонок, но целые числа всегда немного быстрее.

Это полный сценарий, включая входные данные.

CREATE TABLE tab (
  dt DATE
, qty INT
);

INSERT INTO tab(dt,qty) VALUES( '2017-01-20',10);
INSERT INTO tab(dt,qty) VALUES( '2017-02-15', 5);
INSERT INTO tab(dt,qty) VALUES( '2017-02-24',15);
INSERT INTO tab(dt,qty) VALUES( '2017-03-14',20);

SELECT
  yearmonths.yearmonth
, SUM(by_month.month_qty) AS running_qty
FROM (
SELECT DISTINCT
  YEAR(dt) * 100 + MONTH(dt) AS yearmonth
FROM tab
) yearmonths
INNER JOIN (
SELECT
  YEAR(dt) * 100 + MONTH(dt) AS yearmonth
, SUM(qty)                 AS month_qty
FROM tab
GROUP BY YEAR(dt) * 100 + MONTH(dt)
) by_month
ON yearmonths.yearmonth >= by_month.yearmonth
GROUP BY yearmonths.yearmonth
ORDER BY 1;
;


yearmonth|running_qty
  201,701|       10.0
  201,702|       30.0
  201,703|       50.0
select succeeded; 3 rows fetched

Нужны объяснения?

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

Марко Вменяемый

Перевод вопроса и ответов сообщества Stack Overflow, лицензия CC BY-SA. Оригинал на Stack Overflow.

Похожие материалы