복붙노트

[SQL] SQL 서버에서 날짜 간격을 병합

SQL

SQL 서버에서 날짜 간격을 병합

나는 다음과 같은 데이터를 가지고 :

StartDate   |  EndDate
-------------------------
1982.03.02  |  1982.09.30 
1982.10.01  |  1985.01.17 
1985.06.26  |  1985.07.26 
1985.07.30  |  1991.12.31 
1992.01.01  |  1995.12.31 
1996.01.01  |  2004.05.31 
2004.06.05  |  2006.01.31 
2006.02.01  |  2011.05.20

상기 결과 집합이 경우이므로 (2003년 5월 6일에 간격 엔딩 2003년 5월 7일에 간격 개시와 인접하므로, 모두 시작 및 종료일이 구간에 포함 된) I은 인접하는 임의의 간격을 병합해야 해야한다:

StartDate   |  EndDate
-------------------------
1982.03.02  |  1985.01.17 
1985.06.26  |  1985.07.26 
1985.07.30  |  2004.05.31 
2004.06.05  |  2011.05.20

나를 위해,이 작업을 수행 할 수있는 확실한 방법은 커서 세트를 반복하고 결과 세트를 행 단위를 구성하는 것입니다. 내가 어떤 성능 문제가 발생하지 않으려는 것 때문에 그러나이 기능은 잠재적으로 부하가 서버에, 하루에 수천 번 호출 할 수있는 코드 내에서 될 것입니다. 모든 데이터 세트가 작은 (20 개 행 상판)이고, 범위 내의 모든 기간을 발생시키는 모든 용액을 실행할 수 없게하므로, 데이터의 범위는 크다.

내가 보이지 않아요 더 좋은 방법이 있나요?

(데미안의 대답에서) 초기화 코드 :

CREATE TABLE Periods (
    StartDate datetime NOT NULL CONSTRAINT PK_Periods PRIMARY KEY CLUSTERED,
    EndDate datetime NOT NULL
)

INSERT INTO Periods(StartDate,EndDate)
SELECT '19820302', '19820930'
UNION ALL SELECT '19821001', '19850117'
UNION ALL SELECT '19850626', '19850726'
UNION ALL SELECT '19850730', '19911231'
UNION ALL SELECT '19920101', '19951231'
UNION ALL SELECT '19960101', '20040531'
UNION ALL SELECT '20040605', '20060131'
UNION ALL SELECT '20060201', '20110520'

해결법

  1. ==============================

    1.쿼리를 작성하는 것보다 나 샘플 데이터를 설정하는 것이 더 오래 걸립니다 - 당신이 표와 INSERT / SELECT 문을 CREATE 포함 질문을 게시하면 좋을 것입니다. 나는 당신의 테이블이, 내가 전화 한 광산 기간이라고 모르겠어요 :

    쿼리를 작성하는 것보다 나 샘플 데이터를 설정하는 것이 더 오래 걸립니다 - 당신이 표와 INSERT / SELECT 문을 CREATE 포함 질문을 게시하면 좋을 것입니다. 나는 당신의 테이블이, 내가 전화 한 광산 기간이라고 모르겠어요 :

    create table Periods (
    StartDate date not null,
    EndDate date not null
)
go
insert into Periods(StartDate,EndDate)
select '19820302','19820930' union all
select '19821001','19850117' union all
select '19850626','19850726' union all
select '19850730','19911231' union all
select '19920101','19951231' union all
select '19960101','20040531' union all
select '20040605','20060131' union all
select '20060201','20110520'
go
; with MergedPeriods as (
    Select p1.StartDate, p1.EndDate
    from
        Periods p1
            left join
        Periods p2
            on
                p1.StartDate = DATEADD(day,1,p2.EndDate)
    where
        p2.StartDate is null
    union all
    select p1.StartDate,p2.EndDate
    from
        MergedPeriods p1
            inner join
        Periods p2
            on
                p1.EndDate = DATEADD(day,-1,p2.StartDate)
)
select StartDate,MAX(EndDate) as EndDate
from MergedPeriods group by StartDate

    결과:

    StartDate   EndDate
1982-03-02  1985-01-17
1985-06-26  1985-07-26
1985-07-30  2004-05-31
2004-06-05  2011-05-20
  2. ==============================

    2.여기에 두 테이블까지 모든 제출의 가장 좋은 수행이 실행 계획 (대신 세 이상)에 액세스하는 쿼리입니다. 모든 쿼리는 물론 인덱스에 의해 도움이된다. 실행 계획의 요금이 더 비싼 같은 쿼리하지만, 실제 읽고 & CPU는 훨씬 더 나은 메모를하다하시기 바랍니다. 실행 계획의 예상 비용은 실제 성능과 동일하지 않습니다.

    여기에 두 테이블까지 모든 제출의 가장 좋은 수행이 실행 계획 (대신 세 이상)에 액세스하는 쿼리입니다. 모든 쿼리는 물론 인덱스에 의해 도움이된다. 실행 계획의 요금이 더 비싼 같은 쿼리하지만, 실제 읽고 & CPU는 훨씬 더 나은 메모를하다하시기 바랍니다. 실행 계획의 예상 비용은 실제 성능과 동일하지 않습니다.

    WITH Grps AS (
   SELECT
      (Row_Number() OVER (ORDER BY P1.StartDate) - 1) / 2 Grp,
      P1.StartDate,
      P1.EndDate
   FROM
      Periods P1
      CROSS JOIN (SELECT -1 UNION ALL SELECT 1) D (Dir)
      LEFT JOIN Periods P2 ON
         DateAdd(Day, D.Dir, P1.StartDate) = P2.EndDate
         OR DateAdd(Day, D.Dir, P1.EndDate) = P2.StartDate
   WHERE
      (Dir = -1 AND P2.EndDate IS NULL)
      OR (Dir = 1 AND P2.StartDate IS NULL)
)
SELECT
   Min(StartDate) StartDate,
   Max(EndDate) EndDate
FROM Grps
GROUP BY Grp;

    내가 언급 할 가치가 생각 한가지 더 당신의 날짜 기간 테이블을 조회하는 것은 모든 주위 대부분의 경우 간단하고 더 나은 독점 종료 날짜 대신 폐쇄 사람의 (일명 "열기"종료 날짜)를 사용하는 경우 수행 할 것입니다 :

    StartDate   | EndDate     | EndDate
(Inclusive) | (Inclusive) | (Exclusive)
---------------------------------------
1982.03.02  | 1982.09.30  | 1982.10.01
1982.10.01  | 1985.01.17  | 1985.01.18

    독점 종료 날짜를 사용하여 (제 생각에) 당신이 날짜 컬럼의 데이터 유형을 변경하거나 쿼리, 코드, 또는 다른 논리에 영향을주지 않고, 날짜의 해상도를 변경할 수 있기 때문에 대부분의 시간을 가장 좋습니다. 날짜 대신 24 시간 가까운 12 시간으로 필요한 경우 예를 들어, 당신은 당신이 한 일을 변경해야 할 것입니다하지 독점 종료 날짜를 사용하는 경우 반면에, 달성 것을 얻기 위해 큰 일이있을 것이다!

    당신이 독점 종료 날짜를 사용한 경우, 내 쿼리는 다음과 같을 것이다 :

    WITH Grps AS (
   SELECT
      (Row_Number() OVER (ORDER BY P1.StartDate) - 1) / 2 Grp,
      P1.StartDate,
      P1.EndDate
   FROM
      Periods P1
      CROSS JOIN (SELECT 1 UNION ALL SELECT 2) X (Which)
      LEFT JOIN Periods P2 ON
         (X.Which = 1 AND P1.StartDate = P2.EndDate)
         OR (X.Which = 2 AND P1.EndDate = P2.StartDate)
   WHERE
      P2.EndDate IS NULL
      OR P2.StartDate IS NULL
)
SELECT
   Min(StartDate) StartDate,
   Max(EndDate) EndDate
FROM Grps
GROUP BY Grp;

    공지 사항은 예를 들어 12 시간의 기간으로 전환하는 경우 변경해야 할 것 "1 일"의 하드 코딩 된 값으로, 이제 더 DateAdd 함수 또는 참조 DateDiff이 없습니다.

    여기에 내가 마지막으로 거의 5 년 배운 것들을 통합하는 업데이트 쿼리입니다. 이 쿼리는 이제 더는 전혀 조인하고 성능 문제가 될 수 그것은 3 개 정렬 작업을 가지고 있지만, 나는이 쿼리가 합리적으로 잘 경쟁 할 것이라고 생각하고 인덱스의 부재에서 아마 다른 사람에게 손을 아래로 이길 것 없습니다.

    WITH Groups AS (
   SELECT Grp = Row_Number() OVER (ORDER BY StartDate) / 2, *
   FROM
      #Periods
      (VALUES (0), (0)) X (Dup)
), Ranges AS (
   SELECT StartDate = Max(StartDate), EndDate = Min(EndDate)
   FROM Groups
   GROUP BY Grp
   HAVING Max(StartDate) <> DateAdd(day, 1, Min(EndDate))
), ReGroups AS (
   SELECT
      Grp = Row_Number() OVER (ORDER BY StartDate) / 2,
      StartDate,
      EndDate
   FROM
      Ranges
      CROSS JOIN (VALUES (0), (0)) X (Dup)
)
SELECT
   StartDate = Min(StartDate),
   EndDate = Max(EndDate)
FROM ReGroups
GROUP BY Grp
HAVING Count(*) = 2
;

    그리고 여기 (종류 이전 쿼리가 시뮬레이션 무엇의) 윈도 기능을 사용하여 또 다른 버전입니다 :

    WITH LeadLag AS (
   SELECT
      PrevEndDate = Coalesce(Lag(EndDate) OVER (ORDER BY StartDate), '00010101'),
      NextStartDate = Coalesce(Lead(StartDate) OVER (ORDER BY StartDate), '99991231'),
      *
   FROM #Periods
), Dates AS (
   SELECT
      X.*
   FROM
      LeadLag
      CROSS APPLY (
         SELECT
            StartDate = CASE WHEN DateAdd(day, 1, PrevEndDate) <> StartDate THEN StartDate ELSE NULL END,
            EndDate = CASE WHEN DateAdd(day, 1, 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. ==============================

    3.기간을 시작 행 : 당신은 머리를 찾아 볼 수 있습니다. 그런 다음 하위 쿼리에서 다음 머리 전에 마지막 종료 날짜를 검색 :

    기간을 시작 행 : 당신은 머리를 찾아 볼 수 있습니다. 그런 다음 하위 쿼리에서 다음 머리 전에 마지막 종료 날짜를 검색 :

    ; with heads as
        (
        select  StartDate
        ,       EndDate
        ,       row_number() over (order by StartDate) as rn
        from    @YourTable h
        where   not exists
                (
                select  *
                from    @YourTable next
                where   next.EndDate = dateadd(day, -1, h.StartDate)
                )
        )
select  heads.StartDate
,       (
        select  top 1 EndDate
        from    @YourTable
        where   EndDate < COALESCE(
                (
                select  StartDate
                from    heads h2
                where   heads.rn + 1 = h2.rn
                ), '9999-01-01')
        order by
                EndDate desc
        ) as EndDate
from    heads

    한 번에 예.

  4. ==============================

    4.흠 ... 난 당신이 말한 알고

    흠 ... 난 당신이 말한 알고

    그러나 어떤 이유로 나는 그냥이 완료 될 방법을 보여주고 싶었다. 나는 당신의 시간을 낭비 의미하지 않는다.

    이미이없는 경우 첫 번째는 숫자 테이블을 만들 수 있습니다.

    CREATE TABLE Numbers (
   Num int NOT NULL CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED
)
INSERT Numbers VALUES (0)
WHILE @@RowCount < 65536
   INSERT Numbers SELECT Num FROM Numbers + (SELECT Max(Num) FROM Numbers) + 1

    다음 그룹 일부 섬!

    WITH Dts AS (
   SELECT
      DateAdd(Day, Num, StartDate) Dt,
      DateAdd(
         Day,
         -DENSE_RANK() OVER (ORDER BY StartDate, Num),
         DateAdd(Day, Num, StartDate)
      ) Grp
   FROM
      Periods P
      INNER JOIN Numbers N ON DateDiff(Day, P.StartDate, P.EndDate) >= N.Num
)
SELECT Min(Dt) StartDate, Max(Dt) EndDate
FROM Dts
GROUP BY Grp
ORDER BY StartDate

    당신은 SQL 2000을 사용하는 경우이없는 일, 그래서 알려 주시기 바랍니다 것이고, 나는 당신을위한 다른 해결책을 마련하겠습니다.

  5. ==============================

    5.여기에 PostgreSQL을위한 매우 비슷한 스레드는 다음과 같습니다

    여기에 PostgreSQL을위한 매우 비슷한 스레드는 다음과 같습니다

    타임 스탬프에 대한 시작 시간과 종료 시간 사이의 PostgreSQL 정합 간격

    아니에요, 그래서 나는 단지 T-SQL과 약간 잘 알고 완전히 확인 테이크 아웃은 적용되지만 일반적인 생각은 추가적으로 색인 요지와 지오메트리 유형 (또는 R-트리) 인덱스를 저장하고, 질의에 반대. 이것은 매우 빠른 쿼리를 만들 것입니다.

    (아래의 예 세그먼트 코드가 너무 peufeu의 응답에서, 그리고 날짜 범위에 적용) :

    CREATE TABLE segments( start INTEGER NOT NULL, stop INTEGER NOT NULL, range_box BOX NOT NULL );
INSERT INTO segments SELECT n,n+1,BOX(POINT(n,-1),POINT(n+1,1)) FROM generate_series( 1, 1000000 ) n;
CREATE INDEX segments_box ON segments USING gist( range_box );
CREATE INDEX segments_start ON segments(start);
CREATE INDEX segments_stop ON segments(stop);

EXPLAIN ANALYZE SELECT * FROM segments WHERE 300000 BETWEEN start AND stop;
 Index Scan using segments_start on segments  (cost=0.00..12959.24 rows=209597 width=72) (actual time=91.990..91.990 rows=2 loops=1)
   Index Cond: (300000 >= start)
   Filter: (300000 <= stop)
 Total runtime: 92.023 ms

EXPLAIN ANALYZE SELECT * FROM segments WHERE range_box && '(300000,0,300000,0)'::BOX;
 Bitmap Heap Scan on segments  (cost=283.49..9740.27 rows=5000 width=72) (actual time=0.036..0.037 rows=2 loops=1)
   Recheck Cond: (range_box && '(300000,0),(300000,0)'::box)
   ->  Bitmap Index Scan on segments_box  (cost=0.00..282.24 rows=5000 width=0) (actual time=0.032..0.032 rows=2 loops=1)
         Index Cond: (range_box && '(300000,0),(300000,0)'::box)
 Total runtime: 0.064 ms

    또, 위에서 언급 한 PostgreSQL의 특정이지만, T-SQL에서 필요한 유형 / 연산자 / 인덱스뿐만 아니라 존재하는 경우 찾고 가치가있을 수도 있습니다.

  6. ==============================

    6.사람이 PostGIS와이 일을 구현을 찾고 있다면 올드 스레드는하지만, 여기에 예입니다 :

    사람이 PostGIS와이 일을 구현을 찾고 있다면 올드 스레드는하지만, 여기에 예입니다 :

    -- Create the data:
drop table if exists periods;
create temporary table periods as
select '19820302'::date as StartDate,'19820930'::date as EndDate union all
select '19821001'::date,'19850117'::date union all
select '19850626'::date,'19850726'::date union all
select '19850730'::date,'19911231'::date union all
select '19920101'::date,'19951231'::date union all
select '19960101'::date,'20040531'::date union all
select '20040605'::date,'20060131'::date union all
select '20060201'::date,'20110520'::date;

-- Run with PostGIS
-- Convert all intervals to lines, and then do point intersection.
select 
  '1970-01-01'::date+st_x(st_astext(st_pointn(line,1)))::int4 as start, 
  '1970-01-01'::date+st_x(st_astext(st_pointn(line,st_numpoints(line))))::int4-1 as end 
from 
(select (st_dump(st_linemerge(st_union(the_geom)))).geom as line from 
(select st_makeline(st_makepoint(startdate-'1970-01-01',0),
        st_makepoint(enddate-'1970-01-01'+1,0)) as the_geom from periods)t 
)x;  

-- Result
start       |  end
-------------------------
1982-03-02  |  1985-01-17 
1985-06-26  |  1985-07-26 
1985-07-30  |  2004-05-31 
2004-06-05  |  2011-05-20
  7. ==============================

    7.

    alter table MergedPeriods (
   StartDate date not null,
EndDate date not null
)
go
insert into MergedPeriods(StartDate,EndDate)
select '20130210','20130215' union all
select '20130216','20130228' union all
select '20130302','20130312' union all
select '20130317','20130325' union all
select '20130326','20130405' union all
select '20130406','20130411' union all
select '20130502','20130610' 
go
; with MergedPeriods as (
    Select p1.StartDate, p1.EndDate
    from
        [test].[dbo].[Periods] p1
            left join
        [test].[dbo].[Periods] p2
            on
                p1.StartDate = DATEADD(day,1,p2.EndDate)
    where

       p2.StartDate is null
    union all
    select p1.StartDate,p2.EndDate
    from
        MergedPeriods p1
            inner join
        [test].[dbo].[Periods] p2
            on
                p1.EndDate = DATEADD(day,-1,p2.StartDate)



)

select MIN(StartDate),MAX(EndDate) as EndDate
from MergedPeriods group by StartDate

    8. from https://stackoverflow.com/questions/6068619/merging-date-intervals-in-sql-server by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] SQL Server 2008의 로그 파일 (* .LDF)을 읽기  (0) 2020.07.06
[SQL] 데이터 간격으로 그룹  (0) 2020.07.06
[SQL] SQL 서버 WHILE 루프 2008 삽입  (0) 2020.07.06
[SQL] 오라클 리드 및 래그 함수 테라 당량  (0) 2020.07.06
[SQL] 센서 / 읽기 / 경고 데이터베이스 설계에 대한 의견  (0) 2020.07.06
,

티스토리툴바