Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Несколько группировок по дате в одной таблице / 8 сообщений из 8, страница 1 из 1
08.06.2016, 10:30
    #39252541
paver
paver
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Несколько группировок по дате в одной таблице
Салют! Мой первый пост.

В mytable ведется журнал учета различных событий
Поля id, create_time как datetime и несколько полей признаков.
По некоторому условию1 нужно посчитать количество событий по дням за несколько последних дней.
Следующая конструкция работает без нареканий:

Код: sql
1.
2.
3.
4.
SELECT DATE_FORMAT(create_time,'%Y-%m-%d') as date, count(*) as c1
  FROM mytable
  WHERE (условие1) AND create_time > CURRENT_DATE - INTERVAL 30 DAY
  GROUP BY date



Задача усложняется, если число событий нужно подсчитать по нескольким условиям: условие1, условие2 и т.д.

Получился такой запрос:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
SELECT a.date, b.c1, c.c2
  FROM (
    SELECT DATE_FORMAT(create_time,'%Y-%m-%d') AS date
    FROM mytable
    WHERE create_time > CURRENT_DATE - INTERVAL 30 DAY
    GROUP BY date
  ) AS a
  LEFT JOIN (
    SELECT DATE_FORMAT(create_time,'%Y-%m-%d') AS date, count(*) AS c1
    FROM mytable
    WHERE (условие1)  AND create_time > CURRENT_DATE - INTERVAL 30 DAY
    GROUP BY date
  ) AS b USING (date)
  LEFT JOIN (
    SELECT DATE_FORMAT(create_time,'%Y-%m-%d') AS date, count(*) AS c2
    FROM mytable
     WHERE (условие2) AND create_time > CURRENT_DATE - INTERVAL 30 DAY
    GROUP BY date
  ) AS c USING (date)



Внешний оберточный селект нужен, чтобы работала конструкция USING (date) в джойнах (без него у меня не получилось).
Первый подзапрос (AS a) строит исчерпывающий список нужных дат.
Если понадобится еще условие, просто добавляем очередной LEFT JOIN.

Запрос вполне работающий, но есть сомнения в его производительности. Да и трижды строить одну и ту же выборку по create_time > CURRENT_DATE - INTERVAL 30 DAY как-то не комильфо.

Можно ли как-нибудь улучшить запрос?

И еще вопрос до кучи: эквивалентны ли функции DATE_FORMAT(create_time,'%Y-%m-%d') и LEFT(create_time, 10)?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.06.2016, 10:46
    #39252565
Добрый Э - Эх
Добрый Э - Эх
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Несколько группировок по дате в одной таблице
paver,

Тынц
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.06.2016, 12:33
    #39252696
paver
paver
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Несколько группировок по дате в одной таблице
Добрый Э - Эх,

вот жеж блин, читал этот топик, но не понял, что это про меня ) Сенкс!

Итог:
Код: sql
1.
2.
3.
4.
5.
6.
SELECT DATE_FORMAT(create_time,'%Y-%m-%d') as date,
  SUM(условие1) AS c1,
  SUM(условие2) AS c2
  FROM mytable
  WHERE create_time > CURRENT_DATE - INTERVAL 30 DAY
  GROUP BY date
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2016, 09:09
    #39343123
paver
paver
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Несколько группировок по дате в одной таблице
Захотелось к предыдущему результату добавить строчку с Итого

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
SELECT DATE_FORMAT(create_time,'%Y-%m-%d') as date,
  SUM(условие1) AS c1,
  SUM(условие2) AS c2
FROM mytable
WHERE create_time > CURRENT_DATE - INTERVAL 30 DAY
GROUP BY date
UNION
SELECT "ИТОГО",
  SUM(условие1),
  SUM(условие2)
FROM mytable
WHERE create_time > CURRENT_DATE - INTERVAL 30 DAY



В принципе конструкция рабочая, но, подозреваю, дважды считать одно и тоже по одной и той же выборке - неправильно.
Подскажите более красивый вариант, ну или сделайте Тынц, плз.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2016, 09:13
    #39343128
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Несколько группировок по дате в одной таблице
GROUP BY ... WITH ROLLUP?
http://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2016, 09:50
    #39343150
paver
paver
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Несколько группировок по дате в одной таблице
AkinaGROUP BY ... WITH ROLLUP?

Ух ты, какая прелесть )

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

В реале у меня используется другой запрос (для OTRS, если что):

Код: sql
1.
2.
3.
4.
5.
6.
7.
SELECT concat(u.last_name, ' ', u.first_name) as agent_fullname,
  SUM(h.history_type_id = 1) AS created,
  SUM(h.history_type_id IN (1, 27) AND h.state_id IN (2, 3)) AS closed
FROM ticket_history h
LEFT JOIN users u ON h.create_by = u.id
WHERE h.history_type_id IN (1, 27) AND h.create_time > CURRENT_DATE - INTERVAL 31 DAY
GROUP BY h.create_by WITH ROLLUP



В итоговой строке столбец agent_fullname содержит не NULL, который можно обработать как в примере по ссылке: SELECT IFNULL(year,"Total") as year, а ФИО агента из последней строки основного результата запроса. Ну, типа:
Код: plaintext
1.
Иванов А.А.      4      6
Иванов А.А.    118    107

Можно победить?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2016, 10:37
    #39343187
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Несколько группировок по дате в одной таблице
With Rollup ориентируется на выражение группировки, а не на поле выходного набора. Хочешь там видеть Null - группируй по выводимому выражению, а не по другому полю. Или добавь поле группировки в выходной набор и ориентируйся на него.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.11.2016, 11:57
    #39343293
paver
paver
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Несколько группировок по дате в одной таблице
Ага, спасибо.
Или добавь поле группировки в выходной набор и ориентируйся на него.
Да, но все равно в итоговой строке выводится фамилия агента, а это нехорошо

Поставил в группировку agent_fullname вместо h.create_by (почему-то не думал, что так можно), получил в нужном месте NULL.
Правда заменить его на Total (как в примере с SELECT IFNULL(year,"Total") as year) не получилось.
Вариант SELECT IFNULL(concat(u.last_name, ' ', u.first_name),"Total") as agent_fullname проходит без ошибок, но NULL на Total не меняет (
Подозреваю, что эту последнюю строчку можно как-то подправить в HAVING. Если да, то как?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Несколько группировок по дате в одной таблице / 8 сообщений из 8, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 329ms
Закрыть
start [/forum/topic.php?fid=47&tablet=1&tid=1831236]:
 0ms
get settings:
 8ms
get forum list:
 9ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 32ms
get topic data:
 5ms
get forum data:
 1ms
get page messages:
 23ms
get tp. blocked users:
 1ms
others: 246ms
total:  329ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]