EMKiES8X

1SELECT `people_id`, SUM(DATEDIFF(end_date, `start_date`)) AS wd 
2FROM test_days GROUP BY people_id
3	
4SELECT SUM(days) total 
5FROM 
6(
7    SELECT datediff(`end_date`, `start_date`) days FROM test_days
8) AS get_days
9	
10CREATE TABLE `test_days` (
11  `people_id` varchar(100) DEFAULT NULL,
12  `start_date` date DEFAULT NULL,
13  `end_date` date DEFAULT NULL
14) ENGINE=InnoDB DEFAULT CHARSET=utf8;
15
16
17INSERT INTO `test_days` (`people_id`, `start_date`, `end_date`) VALUES
18('Петров', '2018-06-05', '2018-06-09'),
19('Петров', '2018-05-01', '2018-05-19'),
20('Петров', '2018-05-06', '2018-05-19'),
21('Петров', '2018-05-03', '2018-05-23');
22	
23create table seqnum(X int not null);
24-- Первые 8 записей
25insert into seqnum values(0),(1),(2),(3),(4),(5),(6),(7);
26-- И еще 512
27insert into seqnum
28select s1.x*64+s2.x*8+s3.x+8
29  from seqnum s1, seqnum s2, seqnum s3;
30	
31select d.people_id, count(distinct d.start_date + interval s.x day) days
32  from test_days d, seqnum s
33 where s.x<=DATEDIFF(end_date, start_date)
34 group by d.people_id
35	
36select people_id
37     , sum(days) as days
38  from (-- Список длительностей "монолитных" периодов в разрезе пользователя:
39         select all_start_date.people_id
40              , datediff(min(all_end_date.end_date), all_start_date.start_date) as days
41           from  (-- Начальные точки "монолитных" периодов
42                   select start_date
43                        , s1.people_id
44                     from test_days s1
45                    where not exists 
46                            (
47                               select null
48                                 from test_days s2
49                                where s2.start_date < s1.start_date
50                                  and s2.end_date >= s1.start_date
51                                  and s1.people_id = s2.people_id
52                            )
53                 ) all_start_date,
54                 (-- Конечные точки "монолитных" периодов
55                   select end_date
56                        , s1.people_id
57                     from test_days s1
58                    where not exists 
59                            (
60                               select null
61                                 from test_days s2
62                                where s2.end_date > s1.end_date
63                                  and s2.start_date <= s1.end_date
64                                  and s1.people_id = s2.people_id
65                            )
66                 ) all_end_date
67           where all_start_date.people_id = all_end_date.people_id
68             and all_start_date.start_date <= all_end_date.end_date
69           group by all_start_date.people_id, all_start_date.start_date
70       ) v
71 group by people_id
72 order by people_id
73	
74SELECT `people_id`, p2(`people_id`) FROM test_days GROUP BY `people_id`
75	
76--
77-- Функции
78--
79CREATE DEFINER=`root`@`%` FUNCTION `p2` (`name` VARCHAR(255)) RETURNS INT(10) BEGIN
80
81    DECLARE d1 date;
82    DECLARE d2 date;
83    DECLARE prev_d1 date;
84    DECLARE prev_d2 date;
85    DECLARE done INT DEFAULT 0;
86    DECLARE summ_days INT DEFAULT 0;
87    DEClARE cur CURSOR FOR 
88        SELECT start_date, end_date FROM test_days WHERE people_id = name       ORDER BY start_date ;
89    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
90    OPEN cur;
91    SET summ_days:=0;
92    FETCH cur INTO prev_d1,prev_d2;
93    SET summ_days =  DATEDIFF(prev_d2, prev_d1) + 1;
94    REPEAT
95        FETCH cur INTO d1,d2;
96
97            IF NOT done THEN
98
99                IF d1 > prev_d2 THEN
100                    SET summ_days = summ_days + DATEDIFF(d2, d1) + 1;
101                    SET prev_d1 = d1;
102                    SET prev_d2 = d2;
103                ELSE IF d2 > prev_d2 AND d1 <= prev_d2 THEN
104                    SET summ_days = summ_days + DATEDIFF(d2, prev_d2);
105                    SET prev_d2 = d2;
106                    END IF;
107                END IF;
108
109            END IF;
110
111
112    UNTIL done  END REPEAT;
113
114   CLOSE cur;
115
116   RETURN summ_days;
117
118END$$
119
120DELIMITER ;
121
122-- --------------------------------------------------------