连续日期范围的MySQL实现 [英] MySQL Implementation of Consecutive Date Ranges

问题描述

我有一个包含合同表的MySQL数据库:

I have a MySQL database containing contracts table:

CREATE TABLE IF NOT EXISTS `contracts` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `employee_id` BIGINT(20) DEFAULT NULL,
    `start_date` DATE DEFAULT NULL,
    `end_date` DATE DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `contracts` (`id`,`employee_id`,`start_date`,`end_date`) 
VALUES 
(1, 555, '2010-01-01', '2012-12-31'), 
(2, 666, '2013-01-01', '2013-05-01'), 
(3, 666, '2013-05-02', '2013-10-11'),
(4, 777, '2012-01-10', '2013-03-01'),
(5, 777, '2013-03-02', '2014-07-15'),
(6, 777, '2015-01-16', '2015-05-20');

查询它，每位员工获得一个或多个合同行

Querying it I get one or multiple contract lines per employee

SELECT * FROM contracts


id  employee_id     start_date  end_date
1   555             2010-01-01  2012-12-31
2   666             2013-01-01  2013-05-01
3   666             2013-05-02  2013-10-11
4   777             2012-01-10  2013-03-01
5   777             2013-03-02  2014-07-15
6   777             2015-01-16  2015-05-20

如何查询合同表以将每个员工的连续范围分组?我正在寻找此输出:

How do I query contracts table to group consecutive ranges per employee? I'm looking for this output:

employee_id     start_date  end_date
555             2010-01-01  2012-12-31
666             2013-01-01  2013-10-11
777             2012-01-10  2014-07-15
777             2015-01-16  2015-05-20

考虑到合同日期之间没有间隔，员工666的记录将返回最低的开始日期和最高的结束日期.

Record for employee 666 would return lowest start date and highest end date taking into account there is no gap between contract dates.

员工777的记录将返回两行，因为记录ID 5和6之间有间隔.

Record for employee 777 would return two lines since there is a gap between record id 5 and 6.

有什么想法吗?

推荐答案

逻辑并不难，但是在MySQL中的实现却很困难.这个想法是添加一个标志来指示合同开始的开始.然后，对于每一行，对其进行累加.累积总和可用于分组.

The logic isn't so hard, but the implementation in MySQL is. The idea is to add a flag that indicates the beginning of a contract start. Then, for each row, do a cumulative sum of this. The cumulative sum can be used for grouping purposes.

第一步可以使用相关子查询:

The first step can use a correlated subquery:

   SELECT c1.*, 
       (NOT EXISTS (SELECT 1
                    FROM contracts c2
                    WHERE c1.employee_id = c2.employee_id AND
                          c1.start_date = c2.end_date + INTERVAL 1 DAY
                   )
       ) AS startflag
FROM contracts c1;

第二个将其用作子查询并进行累加:

The second uses this as a subquery and does a cumulative sum:

    SELECT 
c0.*
,(@rn := @rn + COALESCE(startflag, 0)) AS cumestarts
FROM 
(SELECT c1.*,
             (NOT EXISTS (SELECT 1
                          FROM contracts c2
                          WHERE c1.employee_id = c2.employee_id AND
                                c1.start_date = c2.end_date + INTERVAL 1 DAY
                         )
             ) AS startflag
      FROM contracts c1
      ORDER BY employee_id, start_date

) c0 CROSS JOIN (SELECT @rn := 0) params;

最后一步是按以下值进行汇总:

The final step is to aggregate by this value:

SELECT 
c.employee_id
,MIN(c.start_date) AS start_date
,MAX(c.end_date) AS end_date
,COUNT(*) AS numcontracts
FROM 
(
        SELECT 
        c0.*
        ,(@rn := @rn + COALESCE(startflag, 0)) AS cumestarts
        FROM 
        (SELECT c1.*,
                 (NOT EXISTS (SELECT 1
                      FROM contracts c2
                      WHERE c1.employee_id = c2.employee_id AND
                        c1.start_date = c2.end_date + INTERVAL 1 DAY
                     )
                 ) AS startflag
              FROM contracts c1
              ORDER BY employee_id, start_date

        ) c0 CROSS JOIN (SELECT @rn := 0) params

) c
GROUP BY c.employee_id, c.cumestarts

这篇关于连续日期范围的MySQL实现的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！