Как сгруппировать с условиями последовательности в MySQL или Google Data Studio?

У меня есть база данных посещаемости, в которой записи сотрудников хранятся, как показано ниже.

Структура базы данных

MySQL версии 5.7.26-log (в датах нет пробелов, все даты существуют, но для удобства я удалил массовые данные даты из оператора вставки строки)

CREATE TABLE `stackoverflow` ( `id` int(9) NOT NULL DEFAULT '0', 
                               `Date` date NOT NULL, 
                               `EmpID` varchar(100) NOT NULL, 
                               `name` varchar(100) NOT NULL, 
                               `TeamName` varchar(100) NOT NULL, 
                               `Status` varchar(100) NOT NULL 
                             ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

-- -- Dumping data for table `stackoverflow` -- 
INSERT INTO `stackoverflow` (`id`, `Date`, `EmpID`, `name`, `TeamName`, `Status`) 
VALUES 
(5, '2019-03-01', '303016', 'Yatendra Ranawat', 'Computer Vision', 'P'), 
(23, '2019-03-01', '303128', 'Nikhat Khan', 'Media - 3D Automation', 'P'), 
(193606, '2019-09-02', '303016', 'Yatendra Ranawat', 'Computer Vision', 'P'), 
(194631, '2019-09-03', '303016', 'Yatendra Ranawat', 'Noon', '-'), 
(222309, '2019-09-30', '303016', 'Yatendra Ranawat', 'Noon', '-'), 
(223336, '2019-10-01', '303016', 'Yatendra Ranawat', 'Noon-Indore', 'P'), 
(282742, '2019-11-28', '303016', 'Yatendra Ranawat', 'Noon-Indore', '-'), 
(283765, '2019-11-29', '303016', 'Yatendra Ranawat', 'Home Depot - Indore', 'P'), 
(303251, '2019-12-18', '303128', 'Nikhat Khan', 'Media - 3D Automation', '-'), 
(304275, '2019-12-19', '303128', 'Nikhat Khan', 'US Taxonomy - Indore', 'P'), 
(309393, '2019-12-24', '303128', 'Nikhat Khan', 'US Taxonomy - Indore', 'P'), 
(310416, '2019-12-25', '303128', 'Nikhat Khan', 'Media - 3D Automation', 'PH'), 
(354076, '2020-02-06', '303016', 'Yatendra Ranawat', 'Home Depot - Indore', '-'), 
(354088, '2020-02-06', '303128', 'Nikhat Khan', 'Media - 3D Automation', 'P');

Я использую Google Data Studio для создания отчета о продолжительности работы сотрудников в команде, как показано ниже.

Продолжительность сотрудника между командой-1

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

Что мне нужно сделать в MySQL / Google Data Studio, чтобы получить результат, как показано ниже, с таблицей данных, как указано выше?

или студия данных

Дайте мне знать, если вам потребуются какие-либо подробности с моей стороны. Любые советы или рекомендации будут с благодарностью.


mysql database google-data-studio
person Yatendra Ranawat    schedule 12.02.2020    source источник
comment
MySQL версии 4.8.5 Даже не 5.x? Действительно?   -  person Akina    schedule 13.02.2020
comment
Вчера вечером обновился до 5.7.26-log.   -  person Yatendra Ranawat    schedule 13.02.2020

Ответы (2)


arrow_upward
1
arrow_downward

Протестируйте также следующий вариант, он быстрее:

SELECT EmpID, name, TeamName, startdate, MAX(enddate) enddate
FROM (
SELECT name,
       CASE WHEN EmpId = @id AND TeamName = @team 
            THEN @startdate
            ELSE @startdate := `Date` 
            END startdate,
       `Date` enddate,
       @id := EmpID EmpID,
       @team := TeamName TeamName
FROM stackoverflow, (SELECT @id := '', @team := '', @startdate := '') variables
ORDER BY EmpID, `Date`
) t
GROUP BY 1,2,3,4
ORDER BY 1,2,4,5;

скрипка

В моей системе с горячим кешем это занимает 0,03 секунды, тогда как запросы из предыдущего ответа - 25,39 секунды и 1 минута 54,79 секунды соответственно. В вашей системе с 220 тыс. записей разница должна быть еще более впечатляющей.

person Akina    schedule 14.02.2020
comment
@YatendraRanawat Если вы где-то используете пользовательские переменные, я бы порекомендовал переименовать их в запросе и дать им длинные случайные имена (что-то вроде as7bg8lk2df7lki4yt8lv0), чтобы избежать каких-либо помех с гарантией. - person Akina; 14.02.2020
comment
... например, используйте select concat('@v', md5(current_timestamp)); для генерации имен... - person Akina; 14.02.2020

arrow_upward
1
arrow_downward

Тест

SELECT t4.EmpID, t4.name, t4.TeamName, MIN(t4.startdate) startdate, t4.enddate
FROM ( SELECT t1.EmpID, t1.name, t1.TeamName, t1.`Date` startdate, MAX(t2.`Date`) enddate
       FROM stackoverflow t1
       JOIN stackoverflow t2 ON t1.EmpID = t2.EmpID
                            AND t1.TeamName = t2.TeamName
                            AND t1.`Date` < t2.`Date`
       LEFT JOIN stackoverflow t3 ON t1.EmpID = t3.EmpID
                                 AND t1.TeamName != t3.TeamName
                                 AND t1.`Date` < t3.`Date`                          
                                 AND t3.`Date` < t2.`Date`
       WHERE t3.EmpId IS NULL
       GROUP BY 1,2,3,4 ) t4
GROUP BY 1,2,3,5
ORDER BY 1,2,4,5;

= этот запрос слишком медленный :( Сервер падает при работе с таблицей, содержащей 222839 строк — Ятендра Ранават

= @YatendraRanawat Преобразование LEFT JOIN в NOT EXISTS... – Акина

SELECT t4.EmpID, t4.name, t4.TeamName, MIN(t4.startdate) startdate, t4.enddate
FROM ( SELECT t1.EmpID, t1.name, t1.TeamName, t1.`Date` startdate, MAX(t2.`Date`) enddate
       FROM stackoverflow t1
       JOIN stackoverflow t2 ON t1.EmpID = t2.EmpID
                            AND t1.TeamName = t2.TeamName
                            AND t1.`Date` < t2.`Date`
       WHERE NOT EXISTS ( SELECT NULL
                          FROM stackoverflow t3 
                          WHERE t1.EmpID = t3.EmpID
                            AND t1.TeamName != t3.TeamName
                            AND t1.`Date` < t3.`Date`                          
                            AND t3.`Date` < t2.`Date` )
       GROUP BY 1,2,3,4 ) t4
GROUP BY 1,2,3,5
ORDER BY 1,2,4,5;

скрипка

person Akina    schedule 12.02.2020
comment
Работаю с демонстрационной таблицей, но не с исходной таблицей, как я уже упоминал. В датах нет пробелов, все даты существуют, но для удобства я удалил массовые данные даты из оператора вставки строки, вы можете увидеть полный набор данных здесь pastebin.com/7ZnfjX82 - person Yatendra Ranawat; 13.02.2020
comment
этот запрос слишком медленный :( Сбой сервера при работе с таблицей, содержащей 222839 строк - person Yatendra Ranawat; 13.02.2020
comment
Как? это то же самое, что и в предыдущем ответе? - person Yatendra Ranawat; 13.02.2020
comment
@YatendraRanawat Обновлено, проверьте. - person Akina; 13.02.2020
comment
@YatendraRanawat CREATE INDEX idx1 ON stackoverflow (EmpID, TeamName, Date); и CREATE INDEX idx2 ON stackoverflow (EmpID, Date); помогут. - person Akina; 13.02.2020
comment
Я попытался с индексацией таблицы, запрос занял почти 15 секунд, чтобы получить результат в таблице из 700 строк, согласно моим расчетам, ответ на запрос займет (222839/700 * 15/60) 79,6 часов в таблице из 222839 строк. однако решение, которое предоставляет Акина, работает, и поэтому я принимаю его как ответ. timetracker.iksulalive.com/screenshot/ - person Yatendra Ranawat; 14.02.2020