MySQL Подсчёт уникального количества дней в таблице с диапазонами дат

#mysql #sql

Пытаюсь найти решение для одной задачки: есть таблица, содержащая Имя, дату начала
интервала и дату конца интервала.
Суть в том, что у человека с одним и тем же именем - может быть несколько записей,
в которых даты могут пересекаться (собственно фото)



Нужно найти для каждого человека (в данном случае для одного Петрова) количество
уникальных дней за все даты. То есть не учитывать наложение дат...

Если суммарное количество дней найти по всем записям элементарно - например

   SELECT `people_id`, SUM(DATEDIFF(end_date, `start_date`)) AS wd 
FROM test_days GROUP BY people_id


Или

SELECT SUM(days) total 
FROM 
(
    SELECT datediff(`end_date`, `start_date`) days FROM test_days
) AS get_days


то как исключить наложения я не додумался. 

Если есть добрые люди - поделитесь идеей, пожалуйста.

Сам код таблицы

CREATE TABLE `test_days` (
  `people_id` varchar(100) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `test_days` (`people_id`, `start_date`, `end_date`) VALUES
('Петров', '2018-06-05', '2018-06-09'),
('Петров', '2018-05-01', '2018-05-19'),
('Петров', '2018-05-06', '2018-05-19'),
('Петров', '2018-05-03', '2018-05-23');

    

Ответы

Ответ 1

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

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

create table seqnum(X int not null);
-- Первые 8 записей
insert into seqnum values(0),(1),(2),(3),(4),(5),(6),(7);
-- И еще 512
insert into seqnum
select s1.x*64+s2.x*8+s3.x+8
  from seqnum s1, seqnum s2, seqnum s3;


А теперь можем размножать и считать уникальные:

select d.people_id, count(distinct d.start_date + interval s.x day) days
  from test_days d, seqnum s
 where s.x<=DATEDIFF(end_date, start_date)
 group by d.people_id


Пример на sqlfiddle.com


Ответ 2

Если кому интересно - примерно такая идея получилась:
Словесное описание алгоритма действий:
1) Для каждого пользователя объединяем все маленькие пересекающие между собой интервалы
в сплошные не пересекающиеся между собой "мегаинтервалы"
2) Для каждого пользователя определяем длительность каждого полученного "мегаинтервала" 
3) Для каждого пользователя определяем сумму длительностей всех "мегаинтервалов"Этот
вариант предложили на sql форуме

select people_id
     , sum(days) as days
  from (-- Список длительностей "монолитных" периодов в разрезе пользователя:
         select all_start_date.people_id
              , datediff(min(all_end_date.end_date), all_start_date.start_date) as days
           from  (-- Начальные точки "монолитных" периодов
                   select start_date
                        , s1.people_id
                     from test_days s1
                    where not exists 
                            (
                               select null
                                 from test_days s2
                                where s2.start_date < s1.start_date
                                  and s2.end_date >= s1.start_date
                                  and s1.people_id = s2.people_id
                            )
                 ) all_start_date,
                 (-- Конечные точки "монолитных" периодов
                   select end_date
                        , s1.people_id
                     from test_days s1
                    where not exists 
                            (
                               select null
                                 from test_days s2
                                where s2.end_date > s1.end_date
                                  and s2.start_date <= s1.end_date
                                  and s1.people_id = s2.people_id
                            )
                 ) all_end_date
           where all_start_date.people_id = all_end_date.people_id
             and all_start_date.start_date <= all_end_date.end_date
           group by all_start_date.people_id, all_start_date.start_date
       ) v
 group by people_id
 order by people_id


И, моя беродехрень, в виде функции. Не самый изящный вариант, не самый рациональный
и крутой, но зато вот такой:
Вызов функции в запросе:

SELECT `people_id`, p2(`people_id`) FROM test_days GROUP BY `people_id`


И. непосредственно, сама функция:

--
-- Функции
--
CREATE DEFINER=`root`@`%` FUNCTION `p2` (`name` VARCHAR(255)) RETURNS INT(10) BEGIN

    DECLARE d1 date;
    DECLARE d2 date;
    DECLARE prev_d1 date;
    DECLARE prev_d2 date;
    DECLARE done INT DEFAULT 0;
    DECLARE summ_days INT DEFAULT 0;
    DEClARE cur CURSOR FOR 
        SELECT start_date, end_date FROM test_days WHERE people_id = name       ORDER
BY start_date ;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    OPEN cur;
    SET summ_days:=0;
    FETCH cur INTO prev_d1,prev_d2;
    SET summ_days =  DATEDIFF(prev_d2, prev_d1) + 1;
    REPEAT
        FETCH cur INTO d1,d2;

            IF NOT done THEN

                IF d1 > prev_d2 THEN
                    SET summ_days = summ_days + DATEDIFF(d2, d1) + 1;
                    SET prev_d1 = d1;
                    SET prev_d2 = d2;
                ELSE IF d2 > prev_d2 AND d1 <= prev_d2 THEN
                    SET summ_days = summ_days + DATEDIFF(d2, prev_d2);
                    SET prev_d2 = d2;
                    END IF;
                END IF;

            END IF;


    UNTIL done  END REPEAT;

   CLOSE cur;

   RETURN summ_days;

END$$

DELIMITER ;

-- --------------------------------------------------------