Задачи
1. Переходы задач внутри команды
Предыстория: есть несколько команд разработки и набор их задач. Каждая задача в момент времени находится у одного разработчика и может быть передана внутри команды или другой команде. И, допустим, нам в будущем хочется собрать статистику, как часто задачи передавались между командами.
Данные на входе – таблица TASK_HISTORY:
ROW_ID TASK_ID ASSIGNEE TEAM START_DATE END_DATE
1 1 Саша из A А 01.01.2019 02.01.2019
2 1 Катя из А А 02.01.2019 03.01.2019
3 1 Вася из Б Б 03.01.2019 05.01.2019
4 1 Саша из А А 05.01.2019 15.01.2019
5 1 Петя из Б Б 15.01.2019 30.01.2019
6 1 Вася из Б Б 30.01.2019 31.01.2019
7 1 Петя из Б Б 31.01.2019 01.02.2019
8 2 Саша из A А 01.01.2019 02.01.2019
9 2 Катя из А А 02.01.2019 03.01.2019
10 2 Саша из А А 03.01.2019 05.01.2019
11 2 Вася из Б Б 05.01.2019 15.01.2019
Это история того, как разработчики передавали друг другу задачи. Здесь:
• ROW_ID – уникальный номер строки в таблице
• TASK_ID – номер задачи
• ASSIGNEE – сотрудник, которому передали в работу задачу
• TEAM – команда сотрудника
• START_DATE, END_DATE – период времени, пока задача была у сотрудника в работе.
Для статистики того, как задачи передавали между командами, нам неинтересны последовательные переходы задачи внутри одного отдела. И мы хотим убрать такие переходы из данных.
Что нужно сделать:
1. Написать запрос SELECT и в нем вычислить столбец SWITCH_NUM – счетчик, который отсчитывает последовательные переходы задачи внутри одной команды:
ROW_ID TASK_ID ASSIGNEE TEAM START_DATE END_DATE SWITCH_NUM
1 1 Саша из A А 01.01.2019 02.01.2019 1
2 1 Катя из А А 02.01.2019 03.01.2019 2
3 1 Вася из Б Б 03.01.2019 05.01.2019 1
4 1 Саша из А А 05.01.2019 15.01.2019 1
5 1 Петя из Б Б 15.01.2019 30.01.2019 1
6 1 Вася из Б Б 30.01.2019 31.01.2019 2
7 1 Петя из Б Б 31.01.2019 01.02.2019 3
8 2 Саша из A А 01.01.2019 02.01.2019 1
9 2 Катя из А А 02.01.2019 03.01.2019 2
10 2 Саша из А А 03.01.2019 05.01.2019 3
11 2 Вася из Б Б 05.01.2019 15.01.2019 1
2. С помощью предыдущего SELECT написать итоговый запрос DELETE, который удалит из TASK_HISTORY все последовательные переходы задач в рамках отдела. Т.е. все строки, где SWITCH_NUM > 1.
2. Версионный список задач.
Предыстория: есть команда разработчиков и их задачи в Jira. Допустим, мы планируем собрать статистику по тому, на ком и когда были какие задачи.
На входе есть следующие данные:
1. Таблица TASK – список задач команды:
TASK_ID LAST_STATUS LAST_ASSIGNEE CREATED_DATE CLOSED_DATE
1 Закрыто Коля 01.01.2019 01.02.2019
2 Закрыто Петя 02.01.2019 02.02.2019
3 Новое Саша 01.02.2019 NULL
Содержит поля:
• TASK_ID – номер задачи
• LAST_STATUS – последний статус по этой задаче
• LAST_ASSIGNEE – последний сотрудник, который работал над задачей
• CREATED_DATE – дата создания задачи
• CLOSED_DATE – дата закрытия задачи
2. Таблица TASK_ASSIGNEE_HISTORY – история переходов задач между сотрудниками:
TASK_ID OLD_ASSIGNEE NEW_ASSIGNEE CHANGE_DATE
1 NULL Саша 02.01.2019
1 Саша Петя 15.01.2019
1 Петя Коля 31.01.2019
3 Петя Коля 02.02.2019
3 Коля Петя 03.02.2019
3 Петя Саша 04.02.2019
Содержит поля:
• TASK_ID – номер задачи
• OLD_ASSIGNEE – предыдущий сотрудник
• NEW_ASSIGNEE – следующий сотрудник
• CHANGE_DATE – дата, когда сменился сотрудник
3. Таблица TASK_STATUS_HISTORY – история того, как менялись статусы задач:
TASK_ID OLD_STATUS NEW_STATUS CHANGE_DATE
1 Новое Оценка 03.01.2019
1 Оценка Разработка 05.01.2019
1 Разработка Тестирование 15.01.2019
1 Тестирование Внедрение 30.01.2019
1 Внедрение Закрыто 01.02.2019
2 Новое Оценка 03.01.2019
2 Оценка Разработка 10.01.2019
2 Разработка Тестирование 20.01.2019
2 Тестирование Внедрение 01.02.2019
2 Внедрение Закрыто 02.02.2019
Содержит поля:
• TASK_ID – номер задачи
• OLD_STATUS – предыдущий статус задачи
• NEW_STATUS – новый статус задачи
• CHANGE_DATE – дата, когда поменяли статус
Что нужно сделать:
1. Написать запрос, который объединит таблицы TASK_ASSIGNEE_HISTORY и TASK в единую версионную таблицу TASK_ASSIGNEE_HISTORY_VERSIONED:
TASK_ID ASSIGNEE START_DATE END_DATE
1 NULL 01.01.2019 02.01.2019
1 Саша 02.01.2019 15.01.2019
1 Петя 15.01.2019 31.01.2019
1 Коля 31.01.2019 01.02.2019
2 Петя 02.01.2019 02.02.2019
3 Петя 01.02.2019 02.02.2019
3 Коля 02.02.2019 03.02.2019
3 Петя 03.02.2019 04.02.2019
3 Саша 04.02.2019 NULL
2. Аналогично с TASK_STATUS_HISTORY – создать по ней TASK_STATUS_HISTORY_VERSIONED.
3. Объединить историю изменений статусов и переходов между сотрудниками в единую версионную таблицу TASK_HISTORY:
TASK_ID STATUS ASSIGNEE START_DATE END_DATE
1 Новое NULL 01.01.2019 02.01.2019
1 Новое Саша 02.01.2019 03.01.2019
1 Оценка Саша 03.01.2019 05.01.2019
1 Разработка Саша 05.01.2019 15.01.2019
1 Тестирование Петя 15.01.2019 30.01.2019
1 Внедрение Петя 30.01.2019 31.01.2019
1 Внедрение Коля 31.01.2019 01.02.2019
1 Закрыто Коля 01.02.2019 01.02.2019
2 Новое Петя 02.01.2019 03.01.2019
2 Оценка Петя 03.01.2019 10.01.2019
2 Разработка Петя 10.01.2019 20.01.2019
2 Тестирование Петя 20.01.2019 01.02.2019
2 Внедрение Петя 01.02.2019 02.02.2019
Ты понимаешь что подобная долбоебическая задача напрочь отбивает желание ее решать?
Нельзя было придумать более жизненную ситуацию?
