Несколько группировок по дате в одной таблице / MySQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

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

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / MySQL [игнор отключен] [закрыт для гостей] / Несколько группировок по дате в одной таблице

8 сообщений из 8, страница 1 из 1

Несколько группировок по дате в одной таблице

#39252541

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:30

| Ответить | Цитировать | Написать

Несколько группировок по дате в одной таблице

#39252565

Добрый Э - Эх

Гость

paver,

Тынц

...

Рейтинг:

0 / 0

08.06.2016, 10:46

| Ответить | Цитировать | Написать

Несколько группировок по дате в одной таблице

#39252696

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.06.2016, 12:33

| Ответить | Цитировать | Написать

Несколько группировок по дате в одной таблице

#39343123

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:09

| Ответить | Цитировать | Написать

Несколько группировок по дате в одной таблице

#39343128

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

GROUP BY ... WITH ROLLUP?
http://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html

...

Рейтинг:

0 / 0

08.11.2016, 09:13

| Ответить | Цитировать | Написать

Несколько группировок по дате в одной таблице

#39343150

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

Иванов А.А.      4      6
Иванов А.А.    118    107

Можно победить?

...

Рейтинг:

0 / 0

08.11.2016, 09:50

| Ответить | Цитировать | Написать

Несколько группировок по дате в одной таблице

#39343187

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

With Rollup ориентируется на выражение группировки, а не на поле выходного набора. Хочешь там видеть Null - группируй по выводимому выражению, а не по другому полю. Или добавь поле группировки в выходной набор и ориентируйся на него.

...

Рейтинг:

0 / 0

08.11.2016, 10:37

| Ответить | Цитировать | Написать

Несколько группировок по дате в одной таблице

#39343293

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

08.11.2016, 11:57

| Ответить | Цитировать | Написать

8 сообщений из 8, страница 1 из 1

Форумы / MySQL [игнор отключен] [закрыт для гостей] / Несколько группировок по дате в одной таблице

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=47&msg=39343150&tid=1831236]:	0ms
get settings:	10ms
get forum list:	22ms
check forum access:	4ms
check topic access:	4ms
track hit:	152ms
get topic data:	8ms
get forum data:	2ms
get page messages:	39ms
get tp. blocked users:	1ms
others:	235ms

total:	477ms

	Необходимые cookie
	Cookie для сбора статистики
	Cookie для маркетинга и рекламы