在MySQL中汇总重叠日期时间的最有效方法 [英] Most efficient way to sum overlapping datetimes in MySQL

问题描述

我正在评估与MySQL中重叠的日期时间之和相关的SO解决方案.我找不到银弹解决方案，所以想知道是否有任何经典/工业级算法程序可用，或者是否需要开发定制的程序.

I'm in the process of evaluating the proposed solutions on SO related to the sum of overlapping datetimes in MySQL. I wasn't able to find out a silver-bullet solution, so would like to know if any classic/industrial-grade algorithmic procedure is available or if a custom-made needs to be developed.

总时间应为8小时(4 + 4).

通过MySQL提出的解决方案

function final_balance($teacher_id, $aa, $teaching_id=0) {
    $dbo = $this->Attendance->getDataSource();
    $years=$this->Attendance->Student->Year->find('list', array('fields' => array('anno', 'data_from')));
    $filteraa='attendances.start>="'.$years[$aa].'"';
    $this->query('SET @interval_id = 0');
    $this->query('SET @interval_end = \'1970-01-01 00:00:00\'');
    $sql='SELECT 
        MIN(start) as start,
        MAX(end) as end 
        FROM (
            SELECT
               @interval_id := IF(attendances.start > @interval_end, @interval_id + 1, @interval_id) AS interval_id,
               @interval_end := IF(attendances.start < @interval_end, GREATEST(@interval_end, attendances.end), attendances.end) AS interval_end,
               attendances.start,
               attendances.end
             FROM attendances
             INNER JOIN attendance_sheets ON (
                 attendance_sheet_id = attendance_sheets.id AND 
                 attendance_sheets.teacher_id='.$teacher_id.' AND '.$filteraa.' AND 
                 attendance_sheet_status_id = 2 AND 
                 attendance_status_id!=3'.
                 ($teaching_id?' AND attendances.teaching_id IN ('.$teaching_id.')':'').'                    
             )
             ORDER BY attendances.start,attendances.end
        ) intervals GROUP BY interval_id';

    // final query to sum in the temp table
    $finalStatement =array(
        'table' => $dbo->expression('('.$sql.')')->value,
        'alias' => 'Attendance',
        'fields' => array(
            'DATE_FORMAT(start, \'%d/%m/%Y\') as data',
            'DATE_FORMAT(start, \'%m-%Y\') as datamese',
            'DATE(start) as datasql',
            $teacher_id.' AS teacher_id',
            'DAY(start) as giorno',
            'MONTH(start) as mese', 
            'YEAR(start) as anno',
            'SEC_TO_TIME(SUM((TIME_TO_SEC(end) - TIME_TO_SEC(start)))) as ore',
        ),
        'conditions' => array(),
        'limit' => null,
        'group' => array('CONCAT(YEAR(start),MONTH(start))', 'DATE(start) WITH ROLLUP'),
        'order' => null
    );
    $finalQuery= $dbo->buildStatement($finalStatement, $this->Attendance);
    return $this->Attendance->query($dbo->expression($finalQuery)->value);
}

参考

MySQL中重叠日期时间范围的总和 执行不同的任务

MySQL:总计时间范围(排除重叠部分) 和 MySQL:对日期时间进行求和，而不需两次计算重叠时间 在我看来，两者似乎都没有考虑所有情况

MySQL: sum time ranges exluding overlapping ones and MySQL: sum datetimes without counting overlapping periods twice both seems to me like not considering all the cases

GeeksForCode:合并重叠间隔

推荐答案

根据情况，以下内容可能有用且高效.

Depending on the circumstances, the following might be useful and efficient.

创建另一个每小时具有一行的表.在仅选择新列并对行进行重复数据删除的同时，将该表与您的表进行内部联接.

Create another table that has one row per hour. Inner join that table with your table while selecting only the new column and dedupe the rows.

您可以不断提高分辨率(例如，提高到几分钟或几秒钟)，但这可能会使您的代码运行缓慢.

You can keep increasing the resolution (eg. to minutes or seconds), but that might make your code run slow.

这篇关于在MySQL中汇总重叠日期时间的最有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！