ReSQL.ru
     
powered by simpleCommunicator - 2.0.49     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Вычисление средней оценки
8 сообщений из 8, страница 1 из 1
Вычисление средней оценки
    #40061489
Honcho
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Honcho
Гость
Всем крепкого здоровья.
Помогите, пожалуйста, с решением задачи. Задача не учебная. Я не прошу сделать за меня мою работу и выдать готовый запрос, хотя бы подскажите направление (как поступают профессионалы в таких случаях).

Суть: Сотрудники выполняют типовые задания и получают оценки. Сотрудник может выполнить только один (или несколько) из пяти этапов: получил заказ, передал на производство, отгрузил, оформил документы.
Шестибалльная система оценок: 0 - опозорился/нанёс ущерб, 1 - не справился, ... 5 - отлично справился. NULL - не участвовал в данном этапе выполнения задания. Таким образом, если он просто "блестяще отгрузил", итоговая средняя оценка по заданию - 5, не смотря на то, что в остальных ячейках оценок - NULL.
Требуется посчитать среднюю (арифметическую) оценку по каждому сотруднику за выбранный период при том, что количество выполненных заданий у каждого - своё.

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
CREATE TABLE `ac_dummy` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` smallint(5) unsigned NOT NULL COMMENT 'id сотрудника из таблицы Сотрудники',
  `accomplishment_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Датавремя завершения выполнения задания.',
  `evaluation_1` tinyint(3) DEFAULT NULL COMMENT 'Оценка.',
  `evaluation_2` tinyint(3) DEFAULT NULL COMMENT 'Оценка.',
  `evaluation_3` tinyint(3) DEFAULT NULL COMMENT 'Оценка.',
  `evaluation_4` tinyint(3) DEFAULT NULL COMMENT 'Оценка.',
  `evaluation_5` tinyint(3) DEFAULT NULL COMMENT 'Оценка.',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
INSERT INTO `ac_dummy` VALUES (1,1,'2021-04-11 19:07:25',NULL,NULL,1,NULL,NULL),
(2,2,'2021-04-11 19:10:33',NULL,NULL,NULL,5,5),(3,2,'2021-04-11 19:10:33',5,NULL,NULL,NULL,NULL),
(4,1,'2021-04-11 19:14:09',NULL,NULL,NULL,NULL,NULL),(5,1,'2021-04-11 19:14:09',5,4,3,4,5),
(6,1,'2021-04-11 19:14:09',NULL,4,4,NULL,NULL),(7,1,'2021-04-11 19:14:09',5,NULL,NULL,NULL,NULL),
(8,2,'2021-04-11 19:14:09',NULL,NULL,NULL,0,4),(9,1,'2021-04-11 19:14:09',4,NULL,4,NULL,4);



Обычно здесь просят показать мои неудачные запросы. Их нет, потому, что я не знаю как подступиться. Если ничего не придумаю - сделаю выборку по дате, переберу и сгруппирую её средствами PHP, но это медленно и не изящно.
...
Рейтинг: 0 / 0
11.04.2021, 20:07
| Ответить | Цитировать | Написать  
Вычисление средней оценки
    #40061516
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
Honcho
Код: sql
1.
2.
3.
4.
5.
  `evaluation_1` tinyint(3) DEFAULT NULL COMMENT 'Оценка.',
  `evaluation_2` tinyint(3) DEFAULT NULL COMMENT 'Оценка.',
  `evaluation_3` tinyint(3) DEFAULT NULL COMMENT 'Оценка.',
  `evaluation_4` tinyint(3) DEFAULT NULL COMMENT 'Оценка.',
  `evaluation_5` tinyint(3) DEFAULT NULL COMMENT 'Оценка.',

Все-таки задача учебная.
Вменяемый архитектор боевой БД так не сделает.

Код: sql
1.
2.
3.
4.
5.
6.
7.
SELECT t.*,
(
  IFNULL(evaluation_1, 0) + IFNULL(evaluation_2, 0) + IFNULL(evaluation_3, 0) + IFNULL(evaluation_4, 0) + IFNULL(evaluation_5, 0)
) / (
  IF(evaluation_1 IS NULL, 0, 1) + IF(evaluation_2 IS NULL, 0, 1) + IF(evaluation_3 IS NULL, 0, 1) + IF(evaluation_4 IS NULL, 0, 1) + IF(evaluation_5 IS NULL, 0, 1)
) AS avg_evaluation
FROM ac_dummy t


Если бы структура таблицы была более логичной - по записи на каждую оценку - то хватило бы одной функции AVG() и группировки по сотруднику.
...
Рейтинг: 0 / 0
11.04.2021, 22:22
| Ответить | Цитировать | Написать  
Вычисление средней оценки
    #40061569
Honcho
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Honcho
Гость
miksoft,
Большое спасибо.
Что можете посоветовать в плане архитектуры, при условии, что в большой оценке 5 составляющих присутствуют всегда, и evaluation_1 - "приём заказа" - не то же самое, что evaluation_5 - "оформление закрывающих документов"?
...
Рейтинг: 0 / 0
12.04.2021, 08:31
| Ответить | Цитировать | Написать  
Вычисление средней оценки
    #40061634
Honcho
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Honcho
Гость
Получилось следующее:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SELECT employee_id, COUNT(*) AS tasks_count, 
SUM((
  IFNULL(evaluation_1, 0) + IFNULL(evaluation_2, 0) + IFNULL(evaluation_3, 0) + IFNULL(evaluation_4, 0) + IFNULL(evaluation_5, 0)
) / (
  IF(evaluation_1 IS NULL, 0, 1) + IF(evaluation_2 IS NULL, 0, 1) + IF(evaluation_3 IS NULL, 0, 1) + IF(evaluation_4 IS NULL, 0, 1) + IF(evaluation_5 IS NULL, 0, 1)
)) / COUNT(*) AS avg_evaluation 
FROM ac_dummy WHERE 
accomplishment_date BETWEEN CAST('2021-04-11 00:00:00' AS DATETIME) AND CAST('2021-04-11 23:59:59' AS DATETIME) 
AND COALESCE(evaluation_1, evaluation_2, evaluation_3, evaluation_4, evaluation_5) IS NOT NULL 
GROUP BY employee_id;
...
Рейтинг: 0 / 0
12.04.2021, 11:27
| Ответить | Цитировать | Написать  
Вычисление средней оценки
    #40061834
Gluck99
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gluck99
Участник
Honcho
Что можете посоветовать в плане архитектуры
Сделать поля: "ID сотрудника", "дата-время события", "оценка", "код события/этапа". Код события/этапа можно оформить как ID элемента справочника, т.е. отдельную таблицу, и джойнить её, можно как ENUM. ENUM легко трансформируется в integer и обратно. Правда, вместе с этим немного усложняется задача понимания в каком этапе сотрудник не участвовал, если вдруг это необходимо. Зато система становится масштабируемой, если завтра добавится ещё один этап, или этапы начнут обособляться в группы, то решить вопрос можно с минимумом телодвижений.
...
Рейтинг: 0 / 0
12.04.2021, 21:01
| Ответить | Цитировать | Написать  
Вычисление средней оценки
    #40062110
Honcho
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Honcho
Гость
Gluck99,

Спасибо. Я думал над этим. Сначала хотел вынести оценки в отдельную таблицу:

task_ideval_typeeval_value15341151534125153413415341431534155
Но потом решил, что проще записать оценки в одну строку таблицы заданий (в примере - ac_dummy): всего 5 tinyint - удобно и компактно, сразу видно неучастие сотрудника в каком-то этапе.

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

Как вариант думал хранить все оценки в одном целом числе. Понадобилось бы 15 бит - smallint. Всё было бы ещё быстрее и компактнее )
...
Рейтинг: 0 / 0
13.04.2021, 15:34
| Ответить | Цитировать | Написать  
Вычисление средней оценки
    #40062216
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
Honcho
Но потом решил, что проще записать оценки в одну строку таблицы заданий (в примере - ac_dummy): всего 5 tinyint - удобно и компактно, сразу видно неучастие сотрудника в каком-то этапе.
Неудобно. Тривиальные запросы превращаются в монстров. А как добавите новое задание - извольте этих монстров переписать.
А когда заказчик войдет во вкус и захочет разные задания для разных подразделений, вложенные задания, пересдачи и т.п. - будет еще неудобнее.
...
Рейтинг: 0 / 0
13.04.2021, 21:18
| Ответить | Цитировать | Написать  
Вычисление средней оценки
    #40062451
Honcho
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Honcho
Гость
Всем большое спасибо за помощь и конструктивную критику.
...
Рейтинг: 0 / 0
14.04.2021, 14:37
| Ответить | Цитировать | Написать  
8 сообщений из 8, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Вычисление средней оценки
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&fpage=9&tid=1828108]:
 0ms
get settings:
 11ms
get forum list:
 11ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 70ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 45ms
get tp. blocked users:
 2ms
others: 239ms
total:  399ms
созд. / загр.: 399ms
x
x
Закрыть


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