복붙노트

[SQL] 병합 중복 날짜 간격

SQL

병합 중복 날짜 간격

날짜 간격을 중복 병합 더 좋은 방법이 있나요? 내가 생각 해낸 해결책은 다른 사람이 작업을 수행 할 수있는 방법의 더 나은 아이디어가있는 경우 지금은 궁금해 너무 간단합니다.

/***** DATA EXAMPLE *****/
DECLARE @T TABLE (d1 DATETIME, d2 DATETIME)
INSERT INTO @T (d1, d2)
        SELECT '2010-01-01','2010-03-31' UNION SELECT '2010-04-01','2010-05-31' 
  UNION SELECT '2010-06-15','2010-06-25' UNION SELECT '2010-06-26','2010-07-10' 
  UNION SELECT '2010-08-01','2010-08-05' UNION SELECT '2010-08-01','2010-08-09' 
  UNION SELECT '2010-08-02','2010-08-07' UNION SELECT '2010-08-08','2010-08-08' 
  UNION SELECT '2010-08-09','2010-08-12' UNION SELECT '2010-07-04','2010-08-16' 
  UNION SELECT '2010-11-01','2010-12-31' UNION SELECT '2010-03-01','2010-06-13' 

/***** INTERVAL ANALYSIS *****/
WHILE (1=1)  BEGIN
  UPDATE t1 SET t1.d2 = t2.d2
  FROM @T AS t1 INNER JOIN @T AS t2 ON 
            DATEADD(day, 1, t1.d2) BETWEEN t2.d1 AND t2.d2 
  IF @@ROWCOUNT = 0 BREAK
END

/***** RESULT *****/
SELECT StartDate = MIN(d1) , EndDate = d2
FROM @T
GROUP BY d2
ORDER BY StartDate, EndDate

/***** OUTPUT *****/
/*****
StartDate   EndDate
2010-01-01  2010-06-13 
2010-06-15  2010-08-16 
2010-11-01  2010-12-31 
*****/

해결법

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

    1.저도 같은 솔루션을 찾고 단일 중복 범위 레코드를 반환 할 날짜를 중복 결합에이 게시물을 가로 질러왔다되었다.

    저도 같은 솔루션을 찾고 단일 중복 범위 레코드를 반환 할 날짜를 중복 결합에이 게시물을 가로 질러왔다되었다.

    날짜 간격을 포장에 다른 스레드가있다.

    나는 여기에 나열된 포함한 다양한 날짜 범위, 이것을 테스트하고 정확하게 모든 시간을 작동합니다.

    SELECT 
       s1.StartDate,
       --t1.EndDate 
       MIN(t1.EndDate) AS EndDate
FROM @T s1 
INNER JOIN @T t1 ON s1.StartDate <= t1.EndDate
  AND NOT EXISTS(SELECT * FROM @T t2 
                 WHERE t1.EndDate >= t2.StartDate AND t1.EndDate < t2.EndDate) 
WHERE NOT EXISTS(SELECT * FROM @T s2 
                 WHERE s1.StartDate > s2.StartDate AND s1.StartDate <= s2.EndDate) 
GROUP BY s1.StartDate 
ORDER BY s1.StartDate

    결과는 다음과 같습니다

    StartDate  | EndDate
2010-01-01 | 2010-06-13
2010-06-15 | 2010-06-25
2010-06-26 | 2010-08-16
2010-11-01 | 2010-12-31
  2. ==============================

    2.당신은 2010 년이 다시 물었다하지만 특정 버전을 지정하지 마십시오.

    당신은 2010 년이 다시 물었다하지만 특정 버전을 지정하지 마십시오.

    SQL 서버에있는 사람들에 대한 대답 2012+

    WITH T1
     AS (SELECT *,
                MAX(d2) OVER (ORDER BY d1) AS max_d2_so_far
         FROM   @T),
     T2
     AS (SELECT *,
                CASE
                  WHEN d1 <= DATEADD(DAY, 1, LAG(max_d2_so_far) OVER (ORDER BY d1))
                    THEN 0
                  ELSE 1
                END AS range_start
         FROM   T1),
     T3
     AS (SELECT *,
                SUM(range_start) OVER (ORDER BY d1) AS range_group
         FROM   T2)
SELECT range_group,
       MIN(d1) AS d1,
       MAX(d2) AS d2
FROM   T3
GROUP  BY range_group

    어느 반환

    +-------------+------------+------------+
| range_group |     d1     |     d2     |
+-------------+------------+------------+
|           1 | 2010-01-01 | 2010-06-13 |
|           2 | 2010-06-15 | 2010-08-16 |
|           3 | 2010-11-01 | 2010-12-31 |
+-------------+------------+------------+

    원하는 결과는 2010-06-25에 끝나는 기간이 한 2010-06-26를 시작으로 축소 할 할 보여 있기 때문에 DATEADD (DAY 1이 사용됩니다.이 조정해야 할 수있는 다른 사용 사례를 들어.

  3. ==============================

    3.여기에 단지 세 가지 간단한 검사와 솔루션입니다. 아니 CTE를, 아니 재귀, 아니 "에 의해 그룹", 루프, 아니에는 테이블 업데이트를 조인하지 않습니다 - 그 결과, 최선을 확장해야이 솔루션은 (내가 생각하지). 나는 최소 및 최대 날짜를 미리 알 수있는 경우 검사의 수는 두 가지로 감소 할 수 있다고 생각합니다; 찾기 간격을 두 번 적용 - 논리 자체는 두 스캔을 필요로한다.

    여기에 단지 세 가지 간단한 검사와 솔루션입니다. 아니 CTE를, 아니 재귀, 아니 "에 의해 그룹", 루프, 아니에는 테이블 업데이트를 조인하지 않습니다 - 그 결과, 최선을 확장해야이 솔루션은 (내가 생각하지). 나는 최소 및 최대 날짜를 미리 알 수있는 경우 검사의 수는 두 가지로 감소 할 수 있다고 생각합니다; 찾기 간격을 두 번 적용 - 논리 자체는 두 스캔을 필요로한다.

    declare @datefrom datetime, @datethru datetime

DECLARE @T TABLE (d1 DATETIME, d2 DATETIME)

INSERT INTO @T (d1, d2)

SELECT '2010-01-01','2010-03-31' 
UNION SELECT '2010-03-01','2010-06-13' 
UNION SELECT '2010-04-01','2010-05-31' 
UNION SELECT '2010-06-15','2010-06-25' 
UNION SELECT '2010-06-26','2010-07-10' 
UNION SELECT '2010-08-01','2010-08-05' 
UNION SELECT '2010-08-01','2010-08-09' 
UNION SELECT '2010-08-02','2010-08-07' 
UNION SELECT '2010-08-08','2010-08-08' 
UNION SELECT '2010-08-09','2010-08-12' 
UNION SELECT '2010-07-04','2010-08-16' 
UNION SELECT '2010-11-01','2010-12-31' 

select @datefrom = min(d1) - 1, @datethru = max(d2) + 1 from @t

SELECT 
StartDate, EndDate
FROM
(
    SELECT 
    MAX(EndDate) OVER (ORDER BY StartDate) + 1 StartDate,
    LEAD(StartDate ) OVER (ORDER BY StartDate) - 1 EndDate
    FROM
    (
        SELECT 
        StartDate, EndDate
        FROM
        (
            SELECT 
            MAX(EndDate) OVER (ORDER BY StartDate) + 1 StartDate,
            LEAD(StartDate) OVER (ORDER BY StartDate) - 1 EndDate 
            FROM 
            (
                SELECT d1 StartDate, d2 EndDate from @T 
                UNION ALL 
                SELECT @datefrom StartDate, @datefrom EndDate 
                UNION ALL 
                SELECT @datethru StartDate, @datethru EndDate
            ) T
        ) T
        WHERE StartDate <= EndDate
        UNION ALL 
        SELECT @datefrom StartDate, @datefrom EndDate 
        UNION ALL 
        SELECT @datethru StartDate, @datethru EndDate
    ) T
) T
WHERE StartDate <= EndDate

    결과는 다음과 같습니다

    StartDate   EndDate
2010-01-01  2010-06-13
2010-06-15  2010-08-16
2010-11-01  2010-12-31
  4. ==============================

    4.이 솔루션에서 나는 범위에 걸쳐 매일의 값을 저장하는 임시 달력 테이블을 만들었습니다. 이러한 유형의 테이블은 정적 만들 수 있습니다. 또한, 난 단지 2009-12-31로 시작하는 400 몇 가지 이상한 날짜를 저장하고있다. 날짜가 더 큰 범위에 걸쳐있는 경우 물론, 당신은 더 많은 값을해야합니다.

    이 솔루션에서 나는 범위에 걸쳐 매일의 값을 저장하는 임시 달력 테이블을 만들었습니다. 이러한 유형의 테이블은 정적 만들 수 있습니다. 또한, 난 단지 2009-12-31로 시작하는 400 몇 가지 이상한 날짜를 저장하고있다. 날짜가 더 큰 범위에 걸쳐있는 경우 물론, 당신은 더 많은 값을해야합니다.

    또한,이 솔루션은 내가 CTE를 사용하고 있다는 점에서 2005 + SQL Server와 함께 작동합니다.

    With Calendar As
    (
    Select DateAdd(d, ROW_NUMBER() OVER ( ORDER BY s1.object_id ), '1900-01-01') As [Date]
    From sys.columns as s1
        Cross Join sys.columns as s2
    )
    , StopDates As
    (
    Select C.[Date]
    From Calendar As C
        Left Join @T As T
            On C.[Date] Between T.d1 And T.d2
    Where C.[Date] >= ( Select Min(T2.d1) From @T As T2 )
        And C.[Date] <= ( Select Max(T2.d2) From @T As T2 )
        And T.d1 Is Null
    )
    , StopDatesInUse As
    (
    Select D1.[Date]
    From StopDates As D1
        Left Join StopDates As D2
            On D1.[Date] = DateAdd(d,1,D2.Date)
    Where D2.[Date] Is Null
    )
    , DataWithEariestStopDate As 
    (
    Select *
    , (Select Min(SD2.[Date])
        From StopDatesInUse As SD2
        Where T.d2 < SD2.[Date] ) As StopDate
    From @T As T
    )
Select Min(d1), Max(d2)
From DataWithEariestStopDate
Group By StopDate
Order By Min(d1)

    2009 년에 날짜를 사용과 편집하려면 문제가 최종 쿼리와는 아무 상관이있다. 문제는 일정 테이블이 충분히 큰되지 않는 것입니다. 나는 2009-12-31에서 일정 테이블을 시작했다. 나는 1900-01-01에서 시작 개정했다.

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

    5.이 시도

    이 시도

    ;WITH T1 AS
(
    SELECT d1, d2, ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS R
    FROM @T
), NUMS AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS R
    FROM T1 A
    CROSS JOIN T1 B
    CROSS JOIN T1 C
), ONERANGE AS 
(
    SELECT DISTINCT DATEADD(DAY, ROW_NUMBER() OVER(PARTITION BY T1.R ORDER BY (SELECT 0)) - 1, T1.D1) AS ELEMENT
    FROM T1
    CROSS JOIN NUMS
    WHERE NUMS.R <= DATEDIFF(DAY, d1, d2) + 1
), SEQUENCE AS
(
    SELECT ELEMENT, DATEDIFF(DAY, '19000101', ELEMENT) - ROW_NUMBER() OVER(ORDER BY ELEMENT) AS rownum
    FROM ONERANGE
)
SELECT MIN(ELEMENT) AS StartDate, MAX(ELEMENT) as EndDate
FROM SEQUENCE
GROUP BY rownum

    당신이 매일 별도의 행을 얻을 수 있도록 기본적인 아이디어는, 기존 데이터 풀다 처음이다. 이것은 ONERANGE에서 이루어집니다

    그런 다음, 날짜가 증가하고, 행 번호가 할 방법 방법 사이의 관계를 식별합니다. 차이점은 기존의 범위 / 섬 내에서 일정하게 유지된다. 즉시 새 데이터 아일랜드에 도착으로, 그들 사이의 차이는 행 번호 증가하면서 1 이상 1 일 증가 때문에 증가한다.

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

    6.아이디어는 간격을 병합 스캔 알고리즘을 시뮬레이션하는 것입니다. 내 솔루션은 SQL 구현의 넓은 범위에 걸쳐 작동하는지 확인합니다. 나는 MySQL은, 포스트 그레스, SQL-서버 2017, SQLite는 심지어 하이브에 그것을 테스트했습니다.

    아이디어는 간격을 병합 스캔 알고리즘을 시뮬레이션하는 것입니다. 내 솔루션은 SQL 구현의 넓은 범위에 걸쳐 작동하는지 확인합니다. 나는 MySQL은, 포스트 그레스, SQL-서버 2017, SQLite는 심지어 하이브에 그것을 테스트했습니다.

    테이블 스키마를 가정하면 다음과 같다.

    CREATE TABLE t (
  a DATETIME,
  b DATETIME
);

    우리는 또한 간격 [A, B)와 같은 반 열려있는 가정합니다.

    이 경우 (a는, i, j)가 테이블에, 그것은 취재 J 간격이 있다는 것을 나타내고, 이전의 시점을 덮는 전 구간이있다.

    CREATE VIEW r AS 
SELECT a,
       Sum(d) OVER (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS i,
       Sum(d) OVER (ORDER BY a ROWS UNBOUNDED PRECEDING) AS j
FROM  (SELECT a, Sum(d) AS d
       FROM   (SELECT a,  1 AS d FROM t
               UNION ALL
               SELECT b, -1 AS d FROM t) e
       GROUP  BY a) f;

    우리는 간격의 결합에있는 모든 엔드 포인트를 생산하고 인접한를 페어링합니다. 마지막으로, 우리는 홀수 번째의 행을 선택하여 간격의 세트를 생성한다.

    SELECT a, b
FROM (SELECT a,
             Lead(a)      OVER (ORDER BY a) AS b,
             Row_number() OVER (ORDER BY a) AS n
      FROM   r
      WHERE  j=0 OR i=0 OR i is null) e
WHERE  n%2 = 1;

    나는 샘플 DB-바이올린 및 SQL-바이올린을 만들었습니다. 또한 SQL에서 노동 조합의 간격이 블로그 포스트를 작성했습니다.

  7. ==============================

    7.여기에 다른 곳에서 나는 날짜 포장 문제가이 문제에 기하학적 인 접근 방식을 제공하지 않는 것으로 나타났습니다. 결국, 포함 된 모든 범위, 날짜 범위, 선으로 해석 될 수있다. 그래서 왜 SQL 지오메트리 유형으로 변환 및 범위를 병합 기하학 :: UnionAggregate를 사용하지.

    여기에 다른 곳에서 나는 날짜 포장 문제가이 문제에 기하학적 인 접근 방식을 제공하지 않는 것으로 나타났습니다. 결국, 포함 된 모든 범위, 날짜 범위, 선으로 해석 될 수있다. 그래서 왜 SQL 지오메트리 유형으로 변환 및 범위를 병합 기하학 :: UnionAggregate를 사용하지.

    이것은 완전히 중첩 범위를 포함하여 중복의 모든 유형을 처리하는 장점이있다. 조금 더 직관적 그 점에서, 그래서 그것은 또한, 다른 집계 쿼리처럼 작동합니다. 당신이 그것을 사용하는 신경 경우에도 결과의 시각적 표현의 보너스를 얻을. 마지막으로, 동시 범위 포장 (대신이 경우의 행 사각형 작업, 그리고 더 많은 고려 사항이 있습니다)에 대한 접근 I의 사용이다. 난 그냥 그 시나리오 작업에 대한 기존의 접근 방식을 가져올 수 없습니다.

    이것은 SQL 서버의 최신 버전을 필요로하는 단점이있다. 그것은 또한 숫자 테이블을 필요로하며, 이는 집계 형태에서 개별적으로 생산 라인을 추출하는 짜증나. 그러나 희망 미래에 마이크로 소프트 (또는 당신이 하나를 직접 구축 할 수 있습니다) 당신이 번호 테이블없이 쉽게 할 수있는 TVF를 추가합니다. 당신이 염두에 두어야 변환 불만 및 정밀 우려가 있으므로 또한, 기하학적 개체는 수레와 함께 작동합니다.

    성능이 많다는 나는 그것을 비교하는 방법을 몰라,하지만 난 그것도 대규모 데이터 세트와 함께 나를 위해 작동하도록 (안 여기에 표시) 몇 가지 일을했습니다.

    '숫자'에서 :

    '마르 아이 네스'에서 :

    외부 쿼리에서 :

    with 

    numbers as (

        select  row_number() over (order by (select null)) i 
        from    @t

    ),

    mergeLines as (

        select      lines = geometry::UnionAggregate(line)
        from        @t
        cross apply (select line = 
                        geometry::Point(convert(float, d1), 0, 0).STUnion(
                            geometry::Point(convert(float, d2) + 1, 0, 0)
                        ).STEnvelope()
                    ) l

    )

    select      ap.StartDate,
                ap.EndDate
    from        mergeLines ml
    join        numbers n on n.i between 1 and ml.lines.STNumGeometries()
    cross apply (select line = ml.lines.STGeometryN(i).STEnvelope()) l
    cross apply (select 
                    StartDate = convert(datetime,l.line.STPointN(1).STX),
                    EndDate = convert(datetime,l.line.STPointN(3).STX) - 1
                ) ap
    order by    ap.StartDate;

    8. from https://stackoverflow.com/questions/2561130/merge-overlapping-date-intervals by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] PostgreSQL의 IF 문  (0) 2020.04.19
[SQL] MySQL은 각 카테고리의 상위 5 반환  (0) 2020.04.19
[SQL] SQL은 다 대다 같은 유형의 관계 테이블로 만들려면  (0) 2020.04.19
[SQL] JSON 열의 배열의 요소를 쿼리  (0) 2020.04.19
[SQL] `REPLACE`와 MySQL에서 'INSERT ... ON DUPLICATE KEY UPDATE` 사이의 실제적인 차이점은 무엇입니까?  (0) 2020.04.19
,

티스토리툴바