Совокупная сумма MySQL, сгруппированная по дате

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

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

SELECT
   e.Date AS e_date,
   count(e.ID) AS num_interactions
FROM example AS e
JOIN example e1 ON e1.Date <= e.Date
GROUP BY e.Date;

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

так выглядит вывод. http://screencast.com/t/N1KFNFyil на 12-23 есть 5 взаимодействий, но они не сгруппированы, потому что отметка времени отличается . поэтому мне нужно найти способ игнорировать метку времени и просто смотреть на день.

если я попробую GROUP BY DAY(e.Date), он группирует данные только по дням (т. е. все, что произошло 1-го числа любого месяца, группируется в одну строку), и вывод совсем не тот, что мне нужен http://screencast.com/t/HN6DH3GV63M

GROUP BY DAY(e.Date), MONTH(e.Date) разбивает его по месяцам и дням месяца, но снова счет не работает.

Я вообще не эксперт MySQL, поэтому я озадачен тем, что мне не хватает


mysql date group-by cumulative-sum
person John Ruddell    schedule 09.03.2014    source источник
comment
это группа по скриншоту ДЕНЬ(), МЕСЯЦ(). не мог добавить больше 2 в свой пост. screencast.com/t/MnaXK7nLkY   -  person John Ruddell    schedule 09.03.2014

Ответы (2)


arrow_upward
22
arrow_downward

Новый ответ

Сначала я не понял, что вы пытаетесь вычислить промежуточную сумму. Вот как это будет выглядеть:

SET @runningTotal = 0;
SELECT 
    e_date,
    num_interactions,
    @runningTotal := @runningTotal + totals.num_interactions AS runningTotal
FROM
(SELECT 
    DATE(eDate) AS e_date,
    COUNT(*) AS num_interactions
FROM example AS e
GROUP BY DATE(e.Date)) totals
ORDER BY e_date;

Исходный ответ

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

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

SELECT
   DATE(e.Date) AS e_date,
   count(e.ID) AS num_interactions
FROM example AS e
JOIN example e1 ON DATE(e1.Date) <= DATE(e.Date)
GROUP BY DATE(e.Date);
person clhereistian    schedule 09.03.2014
comment
Благодарю за ваш ответ. зачистки времени это то, что я хотел сделать. Я все еще не получаю правильных результатов, поэтому я думаю, что это связано с соединением. что я хочу, так это то, что если счет равен 3 в первый день, а затем 4 во второй день, чтобы он отображал 7 на второй день (совокупное количество предыдущего с текущим). количество все еще увеличивается с присоединением DATE() screencast.com/t/qcsRcZt7Ws4z... theres только 5 взаимодействий на 2013-12-23 .. так что я думаю, мне нужно искать другой способ сделать это. - person John Ruddell; 09.03.2014
comment
Таким образом, проблема с соединением заключается в том, что оно сопоставляет каждую запись со всеми возможными. ака .. если есть 5 экземпляров, то он соответствует 1 со всеми 5, а затем следующему со всеми 5, поэтому счет увеличивается до 25 вместо 5. есть мысли, как это исправить? - person John Ruddell; 09.03.2014
comment
Извините, я не понял, что вы пытаетесь сделать промежуточный итог. добавлю к своему ответу - person clhereistian; 09.03.2014
comment
Это здорово, так как будет хорошо работать даже с большими наборами данных. - person Gusstavv Gil; 29.09.2016
comment
Отличный ответ @clhereistian (таблица читается только один раз)! Спасибо, это мне очень помогает. Совет: если вы используете ORM (например, Doctrine в PHP), который может принимать только один запрос за выполнение: замените SET @runningTotal = 0; на @runningTotal := 0, в подзапросе :) (Этот совет также может помочь вам/мне сделать многокумулятивную сумму с UNION между запросами) - person didier2l; 30.07.2018
comment
Большое спасибо, это значительно ускорило мое предыдущее решение на основе подзапросов. - person shadowhorst; 16.05.2019

arrow_upward
13
arrow_downward

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

SELECT
   DATE(e.Date) AS e_date,
   count(e.ID) AS num_daily_interactions,
   (
      SELECT 
         COUNT(id)
      FROM example 
      WHERE DATE(Date) <= e_date
   ) as total_interactions_per_day
FROM example AS e
GROUP BY e_date;

Будет ли это менее эффективным, чем ваш запрос? Я могу просто выполнить расчет в python после получения количества в день, если это более эффективно, потому что это будет в масштабе от тысяч до сотен тысяч возвращаемых строк.

person John Ruddell    schedule 10.03.2014
comment
Ваш запрос выглядит хорошо. Просто убедитесь, что сравнение дат дает то, что вы ожидаете, поскольку вы сравниваете тип даты с типом даты и времени. - person clhereistian; 10.03.2014
comment
ответ - то, что я хочу. я просто немного сомневаюсь, потому что по мере роста этой таблицы она будет медленной. еще раз спасибо за все .. очень полезно - person John Ruddell; 10.03.2014
comment
Я бы попытался ОБЪЯСНИТЬ оба запроса. Я чувствую, что этот будет запускать подзапрос для каждой даты, что не должно быть очень медленным, если у нас есть правильный индекс, но @clhereistian выполняет одну группу, а затем просто перебирает ее, что может быть лучше (в частности, если - это был мой случай - у вас лишние, неиндексированные условия фильтрации). - person chesterbr; 27.06.2014
comment
@chesterbr В итоге я изменил этот запрос, потому что это заняло некоторое время. если вы посмотрите на мои более свежие вопросы, я задаю вопрос об его оптимизации. .. это 90 000 000 000 строк для проверки.. его индексация и оптимизация все еще требуют немногого для сравнения. - person John Ruddell; 27.06.2014