SQL : 병합 날짜 범위

나는 비즈니스 작업 일정의 작업 조각을 설명하는 테이블을했습니다 : (날짜 형식을 24 시간 형식입니다)

PK  | STARTDATE          | ENDDATE
__________________________________________
1   | 2012/07/21 02:00   | 2012/07/21 04:00
2   | 2012/07/21 03:00   | 2012/07/21 10:00
3   | 2012/07/21 06:00   | 2012/07/21 17:00
4   | 2012/07/21 18:00   | 2012/07/21 19:00

지금, 나는이 같은 (주어진 시작과 끝 날짜 이내) 날짜 범위를 병합 좋아한다 :

PK  | STARTDATE          | ENDDATE
__________________________________________
1   | 2012/07/21 02:00   | 2012/07/21 17:00
2   | 2012/07/21 18:00   | 2012/07/21 19:00

SQL97 표준이 할 수있는 방법이 있나요? 그렇다면, 내가 invered 병합을하려는 예를 들어 경우, 결과가 있어야한다 (다른 작업과 함께 무엇인가

PK  | STARTDATE          | ENDDATE
__________________________________________
1   | 2012/07/21 00:00   | 2012/07/21 02:00
2   | 2012/07/21 19:00   | 2012/07/22 00:00

해결법

==============================

1.여기에 SQL 서버 구문을 사용하는 예입니다. 처음에는 이전 겹쳐진 행이없는 "헤드"또는 행을 결정한다. 는 "머리"의 마지막 "아이"를 확인하려면, 다음 "머리"보다 작은 마지막 행을 찾습니다. 여기에 SQL이다 :

여기에 SQL 서버 구문을 사용하는 예입니다. 처음에는 이전 겹쳐진 행이없는 "헤드"또는 행을 결정한다. 는 "머리"의 마지막 "아이"를 확인하려면, 다음 "머리"보다 작은 마지막 행을 찾습니다. 여기에 SQL이다 :

; with  heads as
        (
        select  row_number() over (order by head.StartDate) as PK
        ,       *
        from    YourTable head
        where   not exists 
                (
                select  *
                from    YourTable prev
                where   prev.StartDate < head.StartDate
                        and head.StartDate < prev.EndDate
                )
        )
select  row_number() over (order by h.StartDate) as PK
,       h.StartDate
,       max(yt.EndDate) as EndDate
from    heads h
left join
        heads nh
on      nh.PK = h.PK + 1
left join
        YourTable yt
on      h.StartDate <= yt.StartDate
        and (yt.StartDate < nh.StartDate or nh.StartDate is null)
group by
        h.StartDate

SQL 바이올린에서 라이브 예.

==============================

2.이것은 대부분의 SQL-92 지원의 DBMS에서 트릭을 할해야합니다. 아니 여기에 SQL 구문을 고급.

이것은 대부분의 SQL-92 지원의 DBMS에서 트릭을 할해야합니다. 아니 여기에 SQL 구문을 고급.

이 같은 테이블 4 회에 가입하기 때문에 성능이 좋지 않을 수 있습니다. DBMS 특정 구문을 사용하는 옵션 인 경우 당신은 아마 훨씬 더 나은 성능을 얻을 수 있습니다.

SELECT
  D.StartDate,
  (
    SELECT Min(E.EndDate)
    FROM dbo.Dates E
    WHERE
      E.EndDate >= D.EndDate
      AND NOT EXISTS (
        SELECT *
        FROM dbo.Dates E2
        WHERE
        E.StartDate < E2.StartDate
        AND E.EndDate > E2.StartDate
      )
  ) EndDate
FROM
  dbo.Dates D
WHERE
  NOT EXISTS (
    SELECT *
    FROM dbo.Dates D2
    WHERE
      D.StartDate < D2.EndDate
      AND D.EndDate > D2.EndDate
  );

여러 가지 다른 RDBMS에서에서 작업이 동일한 쿼리에 대한 SQL 바이올린을 참조하십시오 :

여기에 아직 재귀하지 않는 새 쿼리가, 그리고 한 번만 테이블을 검색합니다. 이 쿼리 (몇 행이 샘플 비용의 88 %)의 가장 비싼 부분이 종류를 가지고있다. 이 주요 엉덩이를 걷어차 수있는 것처럼하지만, 더 적은을하는 혜택을 읽기 과소 평가하지, 그리고에 가입 할 필요가 없습니다 ... 가끔 쿼리합니다.

WITH Data AS (
   SELECT
      StartDate = Convert(datetime, StartDate),
      EndDate = Convert(datetime, EndDate)
   FROM (VALUES
      ('02:00', '04:00'), ('03:00', '10:00'), (' 09:00', '12:00'), (' 11:00', '17:00'), (' 18:00', '19:00')
   ) D (StartDate, EndDate)
), LeadLag AS (
   SELECT
      PrevEndDate = Coalesce(Lag(EndDate) OVER (ORDER BY StartDate), Convert(datetime2, '00010101')),
      NextStartDate = Coalesce(Lead(StartDate) OVER (ORDER BY StartDate), Convert(datetime2, '99991231')),
      *
   FROM Data
), Dates AS (
   SELECT
      X.*
   FROM
      LeadLag
      CROSS APPLY (
         SELECT
            StartDate = CASE WHEN PrevEndDate < StartDate THEN StartDate ELSE NULL END,
            EndDate = CASE WHEN EndDate < NextStartDate THEN EndDate ELSE NULL END
      ) X
   WHERE
      X.StartDate IS NOT NULL
      OR X.EndDate IS NOT NULL
), Final AS (
   SELECT
      StartDate,
      EndDate = Min(EndDate) OVER (ORDER BY EndDate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
   FROM Dates
)
SELECT *
FROM Final
WHERE StartDate IS NOT NULL
;

==============================

3.이것은 내 솔루션입니다.

이것은 내 솔루션입니다.

IF OBJECT_ID('tempdb..#tblDates') IS NOT NULL
    DROP TABLE #tblDates

CREATE TABLE #tblDates (AutoId INT IDENTITY, StartDate DATE, EndDate DATE)

INSERT #tblDates (StartDate, EndDate) SELECT '2014-11-02', '2014-11-08'
INSERT #tblDates (StartDate, EndDate) SELECT '2014-11-07', '2014-11-10'
INSERT #tblDates (StartDate, EndDate) SELECT '2014-11-06', '2014-11-12'

INSERT #tblDates (StartDate, EndDate) SELECT '2014-11-02', '2014-11-15'

INSERT #tblDates (StartDate, EndDate) SELECT '2014-12-10', '2014-12-13'
INSERT #tblDates (StartDate, EndDate) SELECT '2014-12-12', '2014-12-15'
INSERT #tblDates (StartDate, EndDate) SELECT '2014-12-14', '2014-12-16'


-- Optional / Remove the duplicated records of same StartDate and EndDate
DELETE FROM #tblDates WHERE AutoId NOT IN (SELECT MAX(AutoId) FROM #tblDates GROUP BY StartDate, EndDate)

-- Optional / Get only the record with max EndDate grouped by StartDate, Remove Others
DELETE  d1
FROM    #tblDates d1
        JOIN (SELECT x.StartDate, MAX(x.EndDate) MAXEndDate FROM #tblDates x GROUP BY x.StartDate) d2 ON d2.StartDate = d1.StartDate AND d2.MAXEndDate != d1.EndDate

-- Optional / Get only the record with min StartDate grouped by EndDate, Remove Others
DELETE  d1
FROM    #tblDates d1
        JOIN (SELECT x.EndDate, MIN(x.StartDate) MINStartDate FROM #tblDates x GROUP BY x.EndDate) d2 ON d2.EndDate = d1.EndDate AND d2.MINStartDate != d1.StartDate

-- Optional / Remove the overlapping ranges of relevant StartDate and EndDate
DELETE  c
FROM    #tblDates p
        JOIN #tblDates c ON c.AutoId != p.AutoId AND c.StartDate BETWEEN p.StartDate AND p.EndDate AND c.EndDate BETWEEN p.StartDate AND p.EndDate


;WITH Ranges
AS
(
    SELECT  s.StartDate, s.EndDate
    FROM    #tblDates s
            LEFT JOIN #tblDates a ON a.AutoId != s.AutoId AND s.StartDate BETWEEN a.StartDate AND a.EndDate AND s.StartDate != a.StartDate
    WHERE   a.AutoId IS NULL
    UNION ALL
    SELECT  r.StartDate, d.EndDate
    FROM    Ranges r
            JOIN #tblDates d ON r.EndDate != d.EndDate AND r.EndDate BETWEEN d.StartDate AND d.EndDate
)

SELECT StartDate, MAX(EndDate) EndDate FROM Ranges GROUP BY StartDate

==============================

4.ErikE 응답을 바탕으로 :

ErikE 응답을 바탕으로 :

IF(object_id('dbo.Periods') is not null)
    drop table Periods

go
create table Periods (
    StartDate date not null,
    EndDate date not null
)
go
insert into Periods(StartDate,EndDate)
select '1980-01-01','1980-01-10' union all
select '1980-01-03','1980-01-07' union all

select '2000-01-01','2000-01-10' union all
select '2000-01-05','2000-01-30' union all
select '2000-01-12','2000-01-20' union all

select '2021-01-01','2021-01-01'
go

; with LeadLag AS (
   SELECT     
     rownum = row_number() OVER( ORDER BY StartDate),
     PrevEndDate = Coalesce(Lag(EndDate) OVER (ORDER BY StartDate), Convert(datetime2, '0001-01-01')), 
     p.*
   FROM Periods p
), Dates AS (
   SELECT
        StartDate = CASE WHEN PrevEndDate < StartDate THEN StartDate ELSE NULL END,
        EndDate,           
        rownum
   FROM   LeadLag
), startGrouping AS (
   SELECT
      StartDate =  max(StartDate) OVER (ORDER BY rownum rows UNBOUNDED PRECEDING),
      EndDate,
      rownum
   FROM Dates
),
 groups AS (
   SELECT
      StartDate,
      EndDate,
      rownum,
      ingroupRownum = row_number() OVER(PARTITION BY StartDate ORDER BY EndDate desc)
   FROM startGrouping e1
)
SELECT StartDate, EndDate
from groups
WHERE  ingroupRownum = 1

from https://stackoverflow.com/questions/11653255/sql-merge-date-ranges by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 그것에서 상속 부모를 쿼리 할 때 행의 소스 테이블의 이름을 가져옵니다 (0)	2020.07.08
[SQL] Microsoft.Jet.OLEDB.4.0는 문자 변환 (0)	2020.07.08
[SQL] SQL JDBC getgeneratedkeys 반환 열 "ID"을 (를) 찾을 수 없습니다, 열 유형의 알 수없는 (0)	2020.07.08
[SQL] SQL Server 2008의 쉼표로 분할 문자열 (0)	2020.07.08
[SQL] 두 날짜 사이의 금요일의 수 (0)	2020.07.08

복붙노트

[SQL] SQL : 병합 날짜 범위

SQL : 병합 날짜 범위

해결법

1.여기에 SQL 서버 구문을 사용하는 예입니다. 처음에는 이전 겹쳐진 행이없는 "헤드"또는 행을 결정한다. 는 "머리"의 마지막 "아이"를 확인하려면, 다음 "머리"보다 작은 마지막 행을 찾습니다. 여기에 SQL이다 :

2.이것은 대부분의 SQL-92 지원의 DBMS에서 트릭을 할해야합니다. 아니 여기에 SQL 구문을 고급.

3.이것은 내 솔루션입니다.

4.ErikE 응답을 바탕으로 :

'SQL' 카테고리의 다른 글

티스토리툴바