SQLize | PHPize | SQLtest

SELECT @period_id := @period_id + 1 AS period_id, period_start AS from_date, period_end AS to_date FROM ( SELECT rental_date AS period_start, IFNULL(return_date, CURDATE()) AS period_end, -- Группируем последовательные или перекрывающиеся периоды SUM(is_new_period) OVER (ORDER BY rental_date) AS group_num FROM ( SELECT r.rental_date, r.return_date, -- Определяем начало нового периода, если текущий старт после предыдущего окончания + 1 день CASE WHEN rental_date <= DATE_ADD(@prev_end, INTERVAL 1 DAY) THEN 0 ELSE 1 END AS is_new_period, @prev_end := IFNULL(r.return_date, CURDATE()) FROM rentals r, (SELECT @prev_end := NULL) init WHERE r.customer_id = 1 ORDER BY r.rental_date ) sub ) grouped_periods JOIN ( SELECT group_num, MIN(rental_date) AS period_start, MAX(IFNULL(return_date, CURDATE())) AS period_end FROM ( SELECT r.rental_date, r.return_date, SUM(is_new_period) OVER (ORDER BY rental_date) AS group_num, @prev_end2 := IFNULL(r.return_date, CURDATE()) FROM rental r, (SELECT @prev_end2 := NULL) init2 WHERE r.customer_id = 1 ORDER BY r.rental_date ) sub2 GROUP BY group_num ) periods ON grouped_periods.group_num = periods.group_num ORDER BY from_date;