Полезные запросы SQL

Обсуждение вопросов и решений

Модератор: ykolesnikov

Ответить
paver
OTRS Мастер
Сообщения: 507
Зарегистрирован: 22 апр 2015, 06:45
Откуда: Томск
Благодарил (а): 7 раз
Поблагодарили: 45 раз

Полезные запросы SQL

Сообщение paver » 27 апр 2016, 09:02

(теперь есть соответствующая тема в hawtos: viewtopic.php?f=4&t=3911)

Ползая по просторам форума, наткнулся на просьбу:
viewtopic.php?f=5&t=2678
Помогите пожалуйста выгрузить статистику из OTRS по артиклям (а не по тикетам). Нужно получить отчёт о том сколько articles было обработано агентом за определённый период. В модуле статистики я могу получить данные только по изменениям произведёнными с тикетами, а вот сколько в этом тикете было articles - статистика не показывает.
Тоже стало интересно, набросал запросик:

Код: Выделить всё

SELECT users.last_name, count(article.create_by) FROM article LEFT JOIN users ON article.create_by = users.id GROUP BY article.create_by
В принципе полезно для анализа. Можно и период задать, как в исходной просьбе.

Или вот, например, получить Id тикетов с повышенной активностью (больше 5 артиклей на заявку):

Код: Выделить всё

SELECT count(ticket_id), ticket_id
 FROM article
 GROUP BY ticket_id
 HAVING count(ticket_id) > 5
Собственно, вопрос-предложение.
Если кто активно юзает эту фичу - может поделитесь в топике своими запросами, которые полезны для вас (и регулярно используете)? Может и другим пригодится.

Потом тему можно будет и в Howtos перенести.
Последний раз редактировалось paver 03 ноя 2016, 12:00, всего редактировалось 2 раза.
--
OTRS 6.0.22

Alex Pelikh
OTRS Новобранец
Сообщения: 44
Зарегистрирован: 16 мар 2016, 18:22

Re: Полезные запросы SQL

Сообщение Alex Pelikh » 27 апр 2016, 14:27

Забыли вырубить из выборки системного пользователя Admin OTRS
И лучше выбирать Имя и Фамилию, иначе риск - Однофамильцы, и непонятно будет кто есть кто
Либо разными полями, либо так:

Код: Выделить всё

SELECT concat(users.last_name, ' ', users.first_name) as agent_fullname,  count(article.create_by) FROM article LEFT JOIN users ON article.create_by = users.id where users.id != 1 GROUP BY article.create_by
Но выбирать отчетные данные прямыми выборками из продуктивной базы на постоянной основе - плохая затея, особенно на большой базе.
Заведите slave реплику и тащите с нее данные внешней отчетной системой.
Она и период позволит выбрать, и всё что угодно, в зависимости от ваших хотелок и возможностей человека, который будет делать шаблоны отчетов.
OTRS 5.0.10, Percona 5.7
Customized KIX4OTRS
Ubuntu 14.04
PentahoBI 7.0

paver
OTRS Мастер
Сообщения: 507
Зарегистрирован: 22 апр 2015, 06:45
Откуда: Томск
Благодарил (а): 7 раз
Поблагодарили: 45 раз

Re: Полезные запросы SQL

Сообщение paver » 28 апр 2016, 14:32

Alex Pelikh писал(а):Но выбирать отчетные данные прямыми выборками из продуктивной базы на постоянной основе - плохая затея, особенно на большой базе.
Я не про регулярные отчеты, а про инструмент админа. Надо что-то быстро посмотреть, что стандартными средствами не делается.

Со вторым отчетом чайниканул, канешна ( . Исправил
--
OTRS 6.0.22

paver
OTRS Мастер
Сообщения: 507
Зарегистрирован: 22 апр 2015, 06:45
Откуда: Томск
Благодарил (а): 7 раз
Поблагодарили: 45 раз

Re: Полезные запросы SQL

Сообщение paver » 03 июн 2016, 13:08

Тут возник вопрос к знатокам SQL.
Захотелось получить статистику одновременно по открытым и закрытым заявкам в разрезе дней.
Получилось такое:

Код: Выделить всё

SELECT a.date, b.created, c.closed
  FROM (
    SELECT DATE_FORMAT(create_time,'%Y-%m-%d') as date
    FROM ticket_history
    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 created
    FROM ticket_history
    WHERE history_type_id = 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 closed
    FROM ticket_history
     WHERE history_type_id IN (1, 27) AND state_id IN (2, 3) AND create_time > CURRENT_DATE - INTERVAL 30 DAY
    GROUP BY date
  ) as c USING (date)
Нельзя ли посимпатичнее его изобразить, выглядит как-то неоптимизировано?
В постгресе общую часть из этих трех запросов можно было бы через CTE попробовать оформить (+ избавиться от внешнего оберточного селекта), но у нас мускуль (
--
OTRS 6.0.22

paver
OTRS Мастер
Сообщения: 507
Зарегистрирован: 22 апр 2015, 06:45
Откуда: Томск
Благодарил (а): 7 раз
Поблагодарили: 45 раз

Re: Полезные запросы SQL

Сообщение paver » 09 июн 2016, 06:04

paver писал(а):Нельзя ли посимпатичнее его изобразить, выглядит как-то неоптимизировано? (
Сам себе отвечу:

Код: Выделить всё

SELECT date,
  SUM(history_type_id = 1) AS created,
  SUM(history_type_id IN (1, 27) AND state_id IN (2, 3)) AS closed
FROM (
  SELECT DISTINCT history_type_id, ticket_id, state_id, LEFT(create_time, 10) AS date
  FROM ticket_history
  WHERE history_type_id IN (1, 27) AND create_time > CURRENT_DATE - INTERVAL 30 DAY) AS a
GROUP BY date
--
OTRS 6.0.22

goro
OTRS Новобранец
Сообщения: 120
Зарегистрирован: 18 авг 2015, 17:18

Re: Полезные запросы SQL

Сообщение goro » 19 окт 2016, 08:51

Коллеги, а есть ли у кого запрос чтоб получить статистику из определенной очереди?
OTRS 4, Centos 6
OTRS 5, Centos 7

bloodice
OTRS Новобранец
Сообщения: 120
Зарегистрирован: 24 ноя 2010, 06:48

Re: Полезные запросы SQL

Сообщение bloodice » 19 окт 2016, 11:08

Может кому то и понятно какая статистика Вам нужно, но очень сильно сомневаюсь.
OTRS 4.0.12, ITSM 4.0.12 тестовая

goro
OTRS Новобранец
Сообщения: 120
Зарегистрирован: 18 авг 2015, 17:18

Re: Полезные запросы SQL

Сообщение goro » 19 окт 2016, 11:14

нужны такие данные поступления тикета, в какую подочередь. Точнее поля дата создания, дата закрытия, подочередь, ответственный.
OTRS 4, Centos 6
OTRS 5, Centos 7

alexus
OTRS Гуру
Сообщения: 5192
Зарегистрирован: 20 сен 2010, 18:17
Откуда: Москва
Благодарил (а): 92 раза
Поблагодарили: 82 раза

Re: Полезные запросы SQL

Сообщение alexus » 20 окт 2016, 01:22

Есть таблица ticket_history. Там в ней всё достаточно прозрачно ;)
С уважением,
Алексей Юсов

Prod: OTRS CE ITSM 6.0.28 on CentOS 7 Apache 2.4 MariaDB 10.4.13 + Radiant Customer Portal

Radiant System OTRS Intergrator RU
Группа OTRS Community в Teleram
Хотите внедрить OTRS? Спросите меня как!

goro
OTRS Новобранец
Сообщения: 120
Зарегистрирован: 18 авг 2015, 17:18

Re: Полезные запросы SQL

Сообщение goro » 27 окт 2016, 17:58

А какое поле отвечает за дату закрытия тикета?
OTRS 4, Centos 6
OTRS 5, Centos 7

paver
OTRS Мастер
Сообщения: 507
Зарегистрирован: 22 апр 2015, 06:45
Откуда: Томск
Благодарил (а): 7 раз
Поблагодарили: 45 раз

Re: Полезные запросы SQL

Сообщение paver » 28 окт 2016, 07:10

goro писал(а):А какое поле отвечает за дату закрытия тикета?
Если вопрос по ticket_history, то искать запись с
history_type_id IN (1, 27) AND state_id IN (2, 3)
т.е. смена состояния на закрытую + или -
Поля create_time или change_time (они эквивалентны)
--
OTRS 6.0.22

goro
OTRS Новобранец
Сообщения: 120
Зарегистрирован: 18 авг 2015, 17:18

Re: Полезные запросы SQL

Сообщение goro » 28 окт 2016, 11:13

А дата создания, дата закрытия, подочередь, ответственный?
OTRS 4, Centos 6
OTRS 5, Centos 7

alexus
OTRS Гуру
Сообщения: 5192
Зарегистрирован: 20 сен 2010, 18:17
Откуда: Москва
Благодарил (а): 92 раза
Поблагодарили: 82 раза

Re: Полезные запросы SQL

Сообщение alexus » 28 окт 2016, 11:39

goro писал(а):А дата создания, дата закрытия, подочередь, ответственный?
Посмотрите на Историю тикета, посмотрите на таблицу, проанализируйте соответствие, подумайте, как правильно делать запросы.
С уважением,
Алексей Юсов

Prod: OTRS CE ITSM 6.0.28 on CentOS 7 Apache 2.4 MariaDB 10.4.13 + Radiant Customer Portal

Radiant System OTRS Intergrator RU
Группа OTRS Community в Teleram
Хотите внедрить OTRS? Спросите меня как!

goro
OTRS Новобранец
Сообщения: 120
Зарегистрирован: 18 авг 2015, 17:18

Re: Полезные запросы SQL

Сообщение goro » 01 ноя 2016, 19:09

подскажите в какой таблице статус тикета найти?
в текет, тикет хистори не нашел...
OTRS 4, Centos 6
OTRS 5, Centos 7

alexus
OTRS Гуру
Сообщения: 5192
Зарегистрирован: 20 сен 2010, 18:17
Откуда: Москва
Благодарил (а): 92 раза
Поблагодарили: 82 раза

Re: Полезные запросы SQL

Сообщение alexus » 01 ноя 2016, 19:17

https://github.com/OTRS/otrs/blob/maste ... .mysql.sql
1. CREATE TABLE ticket & CREATE TABLE ticket_history -> state_id
2. CREATE TABLE ticket_state -> id
С уважением,
Алексей Юсов

Prod: OTRS CE ITSM 6.0.28 on CentOS 7 Apache 2.4 MariaDB 10.4.13 + Radiant Customer Portal

Radiant System OTRS Intergrator RU
Группа OTRS Community в Teleram
Хотите внедрить OTRS? Спросите меня как!

paver
OTRS Мастер
Сообщения: 507
Зарегистрирован: 22 апр 2015, 06:45
Откуда: Томск
Благодарил (а): 7 раз
Поблагодарили: 45 раз

Re: Полезные запросы SQL

Сообщение paver » 02 ноя 2016, 06:37

goro писал(а):подскажите в какой таблице статус тикета найти?
в текет, тикет хистори не нашел...
А можно узнать, как искал?
--
OTRS 6.0.22

svdvovan
OTRS Новобранец
Сообщения: 34
Зарегистрирован: 02 июн 2016, 10:57
Благодарил (а): 1 раз
Поблагодарили: 1 раз
Контактная информация:

Re: Полезные запросы SQL

Сообщение svdvovan » 02 ноя 2016, 10:43

Запрос "закрыто заявок за 9часов"

Код: Выделить всё

SELECT count(*) FROM ticket WHERE ticket_state_id IN (2)  AND queue_id IN (1,2,4,5) AND DATE(change_time) >= DATE_SUB(CURRENT_DATE, INTERVAL 9 HOUR)
Запрос "создано заявок за 9часов"

Код: Выделить всё

SELECT count(*) FROM ticket WHERE ticket_state_id   AND queue_id IN (1,2,4,5) AND DATE(create_time) >= DATE_SUB(CURRENT_DATE, INTERVAL 9 HOUR)
Я в Zabbix вывожу эти запросы в виде графиков...

goro
OTRS Новобранец
Сообщения: 120
Зарегистрирован: 18 авг 2015, 17:18

Re: Полезные запросы SQL

Сообщение goro » 02 ноя 2016, 11:25

alexus писал(а):https://github.com/OTRS/otrs/blob/maste ... .mysql.sql
1. CREATE TABLE ticket & CREATE TABLE ticket_history -> state_id
2. CREATE TABLE ticket_state -> id

state_id - число, как его интерпритировать в название статуса?
OTRS 4, Centos 6
OTRS 5, Centos 7

mukexa
OTRS Новобранец
Сообщения: 148
Зарегистрирован: 30 апр 2013, 19:08
Откуда: Украина, Киев.
Поблагодарили: 1 раз

Re: Полезные запросы SQL

Сообщение mukexa » 02 ноя 2016, 11:35

goro писал(а): state_id - число, как его интерпритировать в название статуса?
ticket_state_type?
OTRS 5s, Ubuntu 12.04

alexus
OTRS Гуру
Сообщения: 5192
Зарегистрирован: 20 сен 2010, 18:17
Откуда: Москва
Благодарил (а): 92 раза
Поблагодарили: 82 раза

Re: Полезные запросы SQL

Сообщение alexus » 02 ноя 2016, 17:48

goro писал(а):state_id - число, как его интерпритировать в название статуса?
Select * сделайте по таблицам - там всё же видно. Хотя если Вы такие вопросы задаёте, то скорее всего Вам потребуется помощь специалиста по SQL.
С уважением,
Алексей Юсов

Prod: OTRS CE ITSM 6.0.28 on CentOS 7 Apache 2.4 MariaDB 10.4.13 + Radiant Customer Portal

Radiant System OTRS Intergrator RU
Группа OTRS Community в Teleram
Хотите внедрить OTRS? Спросите меня как!

bloodice
OTRS Новобранец
Сообщения: 120
Зарегистрирован: 24 ноя 2010, 06:48

Re: Полезные запросы SQL

Сообщение bloodice » 03 ноя 2016, 06:52

svdvovan писал(а):Запрос "закрыто заявок за 9часов"

Код: Выделить всё

SELECT count(*) FROM ticket WHERE ticket_state_id IN (2)  AND queue_id IN (1,2,4,5) AND DATE(change_time) >= DATE_SUB(CURRENT_DATE, INTERVAL 9 HOUR)
Ну сделаю я какую нибудь заметку (или что либо другое что обновляет change_time) у закрытого уже полгода тикета и Ваш замечательный запрос сосчитает его.
OTRS 4.0.12, ITSM 4.0.12 тестовая

paver
OTRS Мастер
Сообщения: 507
Зарегистрирован: 22 апр 2015, 06:45
Откуда: Томск
Благодарил (а): 7 раз
Поблагодарили: 45 раз

Re: Полезные запросы SQL

Сообщение paver » 03 ноя 2016, 07:52

bloodice писал(а):
svdvovan писал(а):Ну сделаю я какую нибудь заметку (или что либо другое что обновляет change_time) у закрытого уже полгода тикета и Ваш замечательный запрос сосчитает его.
Совершенно справедливо. Раньше сам считал тикеты, но через хистори надежнее в общем.

2svdvovan
Тут есть примеры
http://otrs.ru/forum/viewtopic.php?f=4&t=3911
--
OTRS 6.0.22

svdvovan
OTRS Новобранец
Сообщения: 34
Зарегистрирован: 02 июн 2016, 10:57
Благодарил (а): 1 раз
Поблагодарили: 1 раз
Контактная информация:

Re: Полезные запросы SQL

Сообщение svdvovan » 03 ноя 2016, 09:47

paver писал(а):2svdvovan
Тут есть примеры
viewtopic.php?f=4&t=3911
отличные примеры! - утащил себе парочку 8-)

goro
OTRS Новобранец
Сообщения: 120
Зарегистрирован: 18 авг 2015, 17:18

Re: Полезные запросы SQL

Сообщение goro » 25 янв 2017, 18:17

У кого есть запрос на получение кол-ва заявок в очереди?
В какой таблице искать? В таблице Queue не нашел...

т.е. как сюда добавить еще и кол-во заявок в каждой очереди?
SELECT
queue.id,
queue.`name`
FROM
queue
OTRS 4, Centos 6
OTRS 5, Centos 7

alexus
OTRS Гуру
Сообщения: 5192
Зарегистрирован: 20 сен 2010, 18:17
Откуда: Москва
Благодарил (а): 92 раза
Поблагодарили: 82 раза

Re: Полезные запросы SQL

Сообщение alexus » 25 янв 2017, 19:05

С уважением,
Алексей Юсов

Prod: OTRS CE ITSM 6.0.28 on CentOS 7 Apache 2.4 MariaDB 10.4.13 + Radiant Customer Portal

Radiant System OTRS Intergrator RU
Группа OTRS Community в Teleram
Хотите внедрить OTRS? Спросите меня как!

Ответить