今天接到一個需求，一個人有多個任務(wù)，每個任務(wù)都有一個開始時間和結(jié)束時間，統(tǒng)計屬于這個人的任務(wù)開始時間和結(jié)束時間有重合的任務(wù)。支持分頁和篩選。

現(xiàn)在將需求抽象為如下場景:

任務(wù)表task

按照上述需求，應(yīng)該返回如下數(shù)據(jù):

思路

統(tǒng)計時間是否重疊，我們需要查詢出所有開始時間和結(jié)束時間有值的任務(wù)，然后根據(jù)人去分組，每個人下的任務(wù)互相比較，獲取到日期區(qū)間有重疊的任務(wù)，然后再根據(jù)篩選條件和分頁條件對任務(wù)進行分頁和篩選。

代碼實現(xiàn)

在這樣的想法下，最直接的實現(xiàn)是用代碼在內(nèi)存中去實現(xiàn)。

首先查詢出所有的任務(wù)，然后根據(jù)operatorId分組，然后for循環(huán)獲得所有時間區(qū)間重疊的task id，再把所有的task id代入到task表中去做分頁和字段篩選(此處不做代碼演示)。

這樣的實現(xiàn)在功能上沒問題的，但是如果任務(wù)數(shù)量比較大的時候，就需要我們復(fù)盤下結(jié)果返回的過程，看看有沒有優(yōu)化空間。

首先要做一次sql查詢，將所有的任務(wù)放到內(nèi)存中，然后在內(nèi)存中循環(huán)對比，獲取時間區(qū)間沖突的任務(wù)id集合，然后拿到任務(wù)id集合作為參數(shù)傳入新的sql查詢中，來做分頁和篩選操作。在這個過程中，多次sql查詢有一定開銷，查詢結(jié)果映射到內(nèi)存中有一些開銷，而這一連串操作都是數(shù)據(jù)篩選操作，能不能將這些操作交給專門做數(shù)據(jù)存儲和篩選的數(shù)據(jù)庫做呢？

sql實現(xiàn)

根據(jù)以上面的思路，很容易的到如下的查詢sql

select 
t1.*
from task t1, task t2
where t1.operator_id = t2.operator_id
and t1.id <> t2.id
and t1.start_date >= t2.start_date 
and t1.start_date < t2.end_date
union
select 
t2.*
from task t1, task t2
where t1.operator_id = t2.operator_id
and t1.id <> t2.id
and t1.start_date >= t2.start_date 
and t1.start_date < t2.end_date

上述sql雖然很簡單，但在實際實踐當(dāng)中，查詢卻很慢。這里假設(shè)一個人有1w個任務(wù)，查詢過程中每個任務(wù)都要和其他9999個任務(wù)對比時間區(qū)間，笛卡爾積為10000 * 9999 = 99990000，顯然這樣的查詢遍歷的次數(shù)太多了，導(dǎo)致查詢極其緩慢，這樣的慢sql需要優(yōu)化一下。

思路優(yōu)化

查詢?nèi)掌趨^(qū)間是否重復(fù)，假設(shè)有n條任務(wù)數(shù)據(jù)，則需要n*(n-1)次比較，如果能把對比次數(shù)降低，查詢速度應(yīng)該能得到提升。因此我們的目標(biāo)是降低日期對比的次數(shù)。

我們可以根據(jù)operatorId和開始時間對數(shù)據(jù)進行排序，下圖是以一個用戶為例。

1.jpg

排序后的數(shù)據(jù)，如果下一行數(shù)據(jù)的開始時間在上一行數(shù)據(jù)的時間區(qū)間內(nèi)，則這兩條記錄時間區(qū)間有重疊。這樣就只需要和上一條記錄做一次時間對比，而不是n-1次，大大節(jié)省了對比次數(shù)。

按上述規(guī)則，我們可以得到task2和task3兩個時間重疊的任務(wù)，由于task4和task3沒有時間重疊，無法返回，但實際上task4和task2是有時間重疊的，這種情況怎么處理呢？

我們注意到task3的時間區(qū)間是完全被包含在task2的時間區(qū)間內(nèi)的，如果是這種情況，就需要和時間范圍較大的那條記錄做對比了，因此task4的時間區(qū)間應(yīng)該和task2的時間區(qū)間對比，這樣就可以返回task2，task3和task4三條數(shù)據(jù)了。

有了這樣的思路后，接下來就是用sql實現(xiàn)了。

mysql用戶定義變量

使用sql實現(xiàn)上述查詢的難點是如何記錄上一條任務(wù)的開始時間和結(jié)束時間。此時就需要使用mysql的用戶定義變量了。

用戶變量是一個生命周期為session的變量，聲明后在當(dāng)次會話中的所有sql都可以獲取到該變量的值。我們可以用set或者select來初始化:

set @start = 1, @finish = 10;  
select @start = 1, @finish = 10;  
select * from places where place between @start and @finish;

根據(jù)這個特性我們可以聲明一個用戶定義變量，來存儲查詢結(jié)果每條記錄的前一行記錄的開始時間和結(jié)束時間，用來判斷時間區(qū)間是否有重疊。

select 
temp.id, 
temp.operator_id,
temp.start_date,
temp.end_date
from (
    select 
    t1.id,
    t1.operator_id,
    t1.start_date,
    t1.end_date,
    if(
        @lastOperatorId = t1.operator_id,
        t1.start_date >= @lastStartDate and t1.start_date < @lastEndDate,
        0
    ) as overlap,
    @lastOperatorId := t1.operator_id as last_operator_id,
    case
        when t1.end_date < @lastEndDate
        then @lastStartDate := @lastStartDate
        else @lastStartDate := t1.start_date
    end as last_start,
    case
        when t1.end_date < @lastEndDate
        then @lastEndDate := @lastEndDate
        else @lastEndDate := t1.end_date
    end as last_end
    from task t1,
    (
        select @lastOperatorId := 0, 
        @lastStarDate := '1000-01-01 00:00:00',
        @lastEndDate := '1000-01-01 00:00:00'
    )  sqlVars
    order by t1.operator_id asc, t1.start_date asc, t1.id asc
) temp
where temp.overlap = 1

首先我們先聲明一個sqlVars的臨時表以及@lastOperatorId，@lastStarDate和@lastEndDate三個用戶定義變量，分別用于記錄上一條task的操作人，開始時間以及結(jié)束時間。

@lastOperatorId相當(dāng)于對操作人進行分組，只對比同一個人的開始時間和結(jié)束時間。如果當(dāng)前記錄的開始時間在上一條記錄的時間區(qū)間內(nèi)，則overlap標(biāo)記為1，表示重疊。

接下來是將當(dāng)前行的值重新賦值給變量，如果當(dāng)前任務(wù)的時間區(qū)間在上一條記錄的時間區(qū)間內(nèi)，則不賦值。

經(jīng)過測試后，發(fā)現(xiàn)每個人的第一條數(shù)據(jù)沒有返回。原因是第一條數(shù)據(jù)是和后一條數(shù)據(jù)重疊的，與前一條數(shù)據(jù)不重疊，那該如何解決丟數(shù)據(jù)的問題呢？解決辦法也很簡單，倒序再查一遍即可。

select 
distinct
temp.id, 
temp.operator_id,
temp.start_date,
temp.end_date
from (
    select temp1.* from
    (
        select 
        t1.id,
        t1.operator_id,
        t1.start_date,
        t1.end_date,
        if(
            @lastOperatorId = t1.operator_id,
            t1.start_date >= @lastStartDate and t1.start_date < @lastEndDate,
            0
        ) as overlap,
        @lastOperatorId := t1.operator_id as last_operator_id,
        case
            when t1.end_date < @lastEndDate
            then @lastStartDate := @lastStartDate
            else @lastStartDate := t1.start_date
        end as last_start,
        case
            when t1.end_date < @lastEndDate
            then @lastEndDate := @lastEndDate
            else @lastEndDate := t1.end_date
        end as last_end
        from task t1,
        (
            select @lastOperatorId := 0, 
            @lastStarDate := '1000-01-01 00:00:00',
            @lastEndDate := '1000-01-01 00:00:00'
        )  sqlVars
        order by t1.operator_id asc, t1.start_date asc, t1.id asc
    ) temp1
    union
    select temp2.* from
    (
        select 
        t1.id,
        t1.operator_id,
        t1.start_date,
        t1.end_date,
        if(
            @descLastOperatorId = t1.operator_id,
            @descLastStartDate >= t1.start_date and @descLastStartDate < t1.end_date,
            0
        ) as overlap,
        @descLastOperatorId := t1.operator_id as last_operator_id,
        @descLastStartDate := t1.start_date as last_start,
        @descLastEndDate := t1.end_date as last_end
        from task t1,
        (
            select @descLastOperatorId := 0, 
            @descLastStarDate := '1000-01-01 00:00:00',
            @descLastEndDate := '1000-01-01 00:00:00'
        )  sqlVars
        order by t1.operator_id asc, t1.start_date desc, t1.id desc
    ) temp2
) temp
where temp.overlap = 1

這里需要注意的是，倒序查詢的sql中要聲明新的用戶定義變量，以防止和上一個sql的變量重復(fù)，導(dǎo)致查詢結(jié)果不正確。

到目前為止，我們就解決了使用sql查詢表中重疊時間區(qū)間的問題。本文提供一種sql解決的方案，如有更好的解決方案歡迎交流溝通。