日日操夜夜添-日日操影院-日日草夜夜操-日日干干-精品一区二区三区波多野结衣-精品一区二区三区高清免费不卡

本文介紹了使用重疊時(shí)間跨度對(duì)數(shù)據(jù)進(jìn)行SQL分組的處理方法，對(duì)大家解決問題具有一定的參考價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)吧！

問題描述

我需要根據(jù)記錄的開始和結(jié)束時(shí)間，通過重疊的時(shí)間跨度將彼此相關(guān)的數(shù)據(jù)分組在一起。SQL-FIDDLE此處：http://sqlfiddle.com/#!18/87e4b/1/0

我構(gòu)建的當(dāng)前查詢給出的結(jié)果不正確。CallID%3應(yīng)提供的CallCount為4。這并不是因?yàn)橛涗?不包括(因?yàn)樗慌c3重疊)，而是因?yàn)樗c其他相關(guān)記錄之一重疊而應(yīng)包括在內(nèi)。因此，我認(rèn)為可能需要一個(gè)遞歸CTE，但我不確定如何編寫它。

架構(gòu)：

CREATE TABLE Calls
    ([callid] int, [src] varchar(10), [start] datetime, [end] datetime, [conf] varchar(5));

INSERT INTO Calls
    ([callid],[src],[start],[end],[conf])
VALUES
    ('1','5555550001','2019-07-09 10:00:00', '2019-07-09 10:10:00', '111'),
    ('2','5555550002','2019-07-09 10:00:01', '2019-07-09 10:11:00', '111'),
    ('3','5555550011','2019-07-09 11:00:00', '2019-07-09 11:10:00', '111'),
    ('4','5555550012','2019-07-09 11:00:01', '2019-07-09 11:11:00', '111'),
    ('5','5555550013','2019-07-09 11:01:00', '2019-07-09 11:15:00', '111'),
    ('6','5555550014','2019-07-09 11:12:00', '2019-07-09 11:16:00', '111'),
    ('7','5555550014','2019-07-09 15:00:00', '2019-07-09 15:01:00', '111');

當(dāng)前查詢：

SELECT 
    detail_record.callid,
    detail_record.conf,
    MIN(related_record.start) AS sessionStart,
    MAX(related_record.[end]) As sessionEnd,
    COUNT(related_record.callid) AS callCount
FROM    
    Calls AS detail_record
    INNER JOIN
    Calls AS related_record     
        ON related_record.conf = detail_record.conf
        AND ((related_record.start >= detail_record.start
                AND related_record.start < detail_record.[end])
            OR (related_record.[end] > detail_record.start
                AND related_record.[end] <= detail_record.[end])
            OR (related_record.start <= detail_record.start
                AND related_record.[end] >= detail_record.[end])
            )
WHERE
    detail_record.start > '1/1/2019'
    AND detail_record.conf = '111'
GROUP BY
    detail_record.callid,
    detail_record.start,
    detail_record.conf
HAVING 
    MIN(related_record.start) >= detail_record.start
ORDER BY sessionStart DESC

預(yù)期結(jié)果：

callid  conf  sessionStart          sessionEnd              callCount
   7    111   2019-07-09T15:00:00Z  2019-07-09T15:01:00Z    1
   3    111   2019-07-09T11:00:00Z  2019-07-09T11:15:00Z    4
   1    111   2019-07-09T10:00:00Z  2019-07-09T10:11:00Z    2

推薦答案

這是一個(gè)缺口和孤島問題。它不需要遞歸CTE。您可以使用窗口函數(shù)：

select min(callid), conf, grouping, min([start]), max([end]), count(*)
from (select c.*,
             sum(case when prev_end < [start] then 1 else 0 end) over (order by start) as grouping
      from (select c.*,
                   max([end]) over (partition by conf order by [start] rows between unbounded preceding and 1 preceding) as prev_end
            from calls c
           ) c
     ) c
group by conf, grouping;

最里面的子查詢計(jì)算上一個(gè)結(jié)尾。中間的子查詢將其與當(dāng)前開始進(jìn)行比較，以確定相鄰行的組何時(shí)是新組的開始。然后，累加和確定分組。

和，外部查詢聚合以匯總有關(guān)每個(gè)組的信息。

Here是小提琴。

這篇關(guān)于使用重疊時(shí)間跨度對(duì)數(shù)據(jù)進(jìn)行SQL分組的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，