Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Объединение пересекающихся диапазонов дат.  [new]
DnRumata
Member

Откуда:
Сообщений: 11
Очень долго маюсь с выборкой и не могу сделать красивое решение, бога ради помогите.

Есть большой список дат - (дата начала - дата конца).
Мне нужно найти минимальную дату начала для пересекающейся линейки диапазонов и максимальную дату конца.

Как это выглядит кодом:

+ Пример исходных данных

CREATE TABLE #SourceData
(
	--RID UNIQUEIDENTIFIER,
	CI NVARCHAR(10) NOT NULL,
	DateTimeStarted DATE NOT NULL,
	DateTimeCompleted DATE NOT NULL
)

INSERT INTO #SourceData
(
CI,
DateTimeStarted,
DateTimeCompleted
)
VALUES
(
'CI0025',
CAST('20190110' AS DATE),
CAST('20190114' AS DATE)
)
INSERT INTO #SourceData
(
CI,
DateTimeStarted,
DateTimeCompleted
)
VALUES
(
'CI0025',
CAST('20190112' AS DATE),
CAST('20190117' AS DATE)
)
INSERT INTO #SourceData
(
CI,
DateTimeStarted,
DateTimeCompleted
)
VALUES
(
'CI0025',
CAST('20190116' AS DATE),
CAST('20190120' AS DATE)
)
INSERT INTO #SourceData
(
CI,
DateTimeStarted,
DateTimeCompleted
)
VALUES
(
'CI0025',
CAST('20181003' AS DATE),
CAST('20181004' AS DATE)
)
INSERT INTO #SourceData
(
CI,
DateTimeStarted,
DateTimeCompleted
)
VALUES
(
'CI0025',
CAST('20181003' AS DATE),
CAST('20181005' AS DATE)
)
INSERT INTO #SourceData
(
CI,
DateTimeStarted,
DateTimeCompleted
)
VALUES
(
'CI0025',
CAST('20181007' AS DATE),
CAST('20181009' AS DATE)
)
INSERT INTO #SourceData
(
CI,
DateTimeStarted,
DateTimeCompleted
)
VALUES
(
'CI0025',
CAST('20181008' AS DATE),
CAST('20191010' AS DATE)
)
INSERT INTO #SourceData
(
CI,
DateTimeStarted,
DateTimeCompleted
)
VALUES
(
'CI0025',
CAST('20171010' AS DATE),
CAST('20171014' AS DATE)
)
INSERT INTO #SourceData
(
CI,
DateTimeStarted,
DateTimeCompleted
)
VALUES
(
'CI0025',
CAST('20170501' AS DATE),
CAST('20170503' AS DATE)
)
INSERT INTO #SourceData
(
CI,
DateTimeStarted,
DateTimeCompleted
)
VALUES
(
'CI0025',
CAST('20170502' AS DATE),
CAST('20170506' AS DATE)
)
INSERT INTO #SourceData
(
CI,
DateTimeStarted,
DateTimeCompleted
)
VALUES
(
'CI0025',
CAST('20170505' AS DATE),
CAST('20170520' AS DATE)
)



Собственно сама выборка которую я сделал.
;WITH Src AS
(
	SELECT CI, caMin.MinStarted, caMax.MaxCompleted
	FROM #SourceData sd
	CROSS APPLY
	(
		SELECT MIN(sdm.DateTimeStarted) AS MinStarted
		FROM #SourceData sdm
		WHERE sdm.CI = sd.CI
		AND sdm.DateTimeStarted <= sd.DateTimeCompleted
		AND sdm.DateTimeCompleted >= sd.DateTimeStarted 
	) caMin
	CROSS APPLY
	(
		SELECT MAX(sdma.DateTimeCompleted) AS MaxCompleted
		FROM #SourceData sdma
		WHERE sdma.CI = sd.CI
		AND sdma.DateTimeStarted <= sd.DateTimeCompleted
		AND sdma.DateTimeCompleted >= sd.DateTimeStarted 
	) caMax
)
SELECT * FROM Src
GROUP BY CI,MinStarted, MaxCompleted


Суть, у нас есть диапазоны дат.
Некоторые из них между собой пересекаются, некоторые нет, некоторые лежат друг в друге и т.д. и т.п.

Нужно найти максимальный и минимальный даты этих ЦЕПОЧЕК пересечений.

Что не делает мой код и в чем вопрос:


У нас есть диапазоны:
10-12
11-16
15-18

В конце у меня должно это сгруппироваться в одну запись:
10-18

Тобишь минимальная дата начала - 10 и максимальная дата конца - 18(потому что диапазон продолжается).

И этого мой код не сделает из-за условий Cross Apply.


Дополнительная информация по задаче:

  • Вложенность подобных цепочек вряд-ли будет выше 10-13ти.
  • Размеры исходной таблицы(SourceData) порядка 800+ тысяч записей, тобишь большие объемы, выборка строго индексирована.
  • Очень высокие требования к быстродействию, он должен молотить всю эту выборку быстрее чем за 4 минуты на 800 тысяч строк.
  • В таблице множество разных CI и множество пересечений внутри, выборку из 200 тысяч мой текущий запрос сводит к порядка 80 тысячам записей!


    Мои идеи - напрашивается рекурсия, но я не понимаю условия ее ограничения, не понимаю что ее ограничивает.

    Пожалуйста помогите решить проблему нахождения Min и Max дат вот таких цепочек.
  • 26 апр 19, 19:19    [21872977]     Ответить | Цитировать Сообщить модератору
     Re: Объединение пересекающихся диапазонов дат.  [new]
    DnRumata
    Member

    Откуда:
    Сообщений: 11
    Вот так выглядит работа моего текущего кода на графиках.

    Крестик то что он не делает.

    К сообщению приложен файл. Размер - 21Kb
    26 апр 19, 19:42    [21872986]     Ответить | Цитировать Сообщить модератору
     Re: Объединение пересекающихся диапазонов дат.  [new]
    ПЕНСИОНЕРКА
    Member

    Откуда: Владимирская обл
    Сообщений: 4723
    DnRumata,

    для начала перевела вашу простыню кода в табличку(надеюсь, что не ошиблась)
    2019.01102019.0114
    2019.01122019.0117
    2019.01162019.0120=11
    2018.10032018.1004
    2018.10032018.1005 =3
    2018.10072018.1009
    2018.10082019.1010 =5
    2017.10102017.1014 =5
    2017.05012017.0503
    2017.05022017.0506
    2017.05052017.0520 =20
    26 апр 19, 20:01    [21872999]     Ответить | Цитировать Сообщить модератору
     Re: Объединение пересекающихся диапазонов дат.  [new]
    fallenyasha
    Member

    Откуда:
    Сообщений: 12
    Ну что-то такого плана, любая вложенность и количество пересечений, надо только посмотреть что там с планом и поколдовать над индексами :)

    with
    src1 as
    	(select *,
    		case when lag(DateTimeCompleted) over (partition by CI order by DateTimeStarted, DateTimeCompleted) >= DateTimeStarted then 0 else 1 end isOpen,
    		case when lead(DateTimeStarted) over (partition by CI order by DateTimeStarted, DateTimeCompleted) <= DateTimeCompleted then 0 else 1 end isClose
    	from #SourceData),
    src2 as
    	(select *,
    		case when isClose = 1 then DateTimeCompleted else lead(DateTimeCompleted) over (partition by CI order by DateTimeStarted, DateTimeCompleted) end closeDate
    	from src1
    	where isOpen = 1 or isClose = 1)
    select CI, DateTimeStarted, closeDate as DateTimeCompleted
    from src2
    where isOpen = 1
    
    26 апр 19, 20:12    [21873006]     Ответить | Цитировать Сообщить модератору
     Re: Объединение пересекающихся диапазонов дат.  [new]
    vikkiv
    Member

    Откуда: London
    Сообщений: 2693
    DnRumata,
    DnRumata
    Вот так выглядит работа моего текущего кода на графиках.

    Крестик то что он не делает.
    в крестике на диаграме 3 диапазона, всё из 2х и одного покрыто решением.
    если делать решение в 2 прохода - то изначально 3-х диапазонные
    обьединения (а после первого прохода уже 2х-диапазонные) тоже будут покрыты
    26 апр 19, 20:15    [21873008]     Ответить | Цитировать Сообщить модератору
     Re: Объединение пересекающихся диапазонов дат.  [new]
    ПЕНСИОНЕРКА
    Member

    Откуда: Владимирская обл
    Сообщений: 4723
    DnRumata,

    с ms sql не работала, а в коде сделала бы через массив примерно так(макет)

    dim xm(0 to 50000) as long
    set rst=currentdb.openrecordset("select dnach,dkon from tab") 
    do while  rst.eof=false
    for d1=dnach to dkon
    xm(d1)=1
    next d1
    rst.movenext
    k=0  
    for d1=1 to 50000
    if xm(d1)=0 then
    if k>0 then
      debug.print dn,dk
      dn=d1
      dk=d1
      k=0
    endif
    else
      k=k+1
      dk=d1
    endif
    next d1
    
    26 апр 19, 20:17    [21873009]     Ответить | Цитировать Сообщить модератору
     Re: Объединение пересекающихся диапазонов дат.  [new]
    aleks222
    Member

    Откуда:
    Сообщений: 919
    Докатились. Неучи.

    1. Начало = дата которая НИЧЕМ не перекрыта.
    2. Конец = дата которая НИЧЕМ не перекрыта.
    3. Начало предшествует концу.
    4. Вот и фсе.
    26 апр 19, 20:34    [21873020]     Ответить | Цитировать Сообщить модератору
     Re: Объединение пересекающихся диапазонов дат.  [new]
    DnRumata
    Member

    Откуда:
    Сообщений: 11
    aleks222,

    Ога, ну и как узнать что дата ничем не перекрыта?
    26 апр 19, 20:44    [21873025]     Ответить | Цитировать Сообщить модератору
     Re: Объединение пересекающихся диапазонов дат.  [new]
    DnRumata
    Member

    Откуда:
    Сообщений: 11
    fallenyasha,

    Сейчас погляжу, спасибо
    26 апр 19, 20:45    [21873026]     Ответить | Цитировать Сообщить модератору
     Re: Объединение пересекающихся диапазонов дат.  [new]
    DnRumata
    Member

    Откуда:
    Сообщений: 11
    ПЕНСИОНЕРКА,

    2019-01-10 = 20190110

    Это даты.
    Код рабочий и спокойно запускается на ms sql
    26 апр 19, 20:47    [21873028]     Ответить | Цитировать Сообщить модератору
     Re: Объединение пересекающихся диапазонов дат.  [new]
    DnRumata
    Member

    Откуда:
    Сообщений: 11
    vikkiv,

    Потому и подумал о рекурсии.
    Проблема в том что проходов может быть порядка 10ти
    26 апр 19, 20:50    [21873031]     Ответить | Цитировать Сообщить модератору
     Re: Объединение пересекающихся диапазонов дат.  [new]
    vikkiv
    Member

    Откуда: London
    Сообщений: 2693
    DnRumata,

    тогда надо профайлинг данных делать и искать оптимальный способ
    т.к. теоретически можно подойти и с другого конца - искать пробелы между диапазонами
    когда ясно будет чего меньше - от туда уже копать вариант подходящий по производительности
    26 апр 19, 20:55    [21873034]     Ответить | Цитировать Сообщить модератору
     Re: Объединение пересекающихся диапазонов дат.  [new]
    aleks222
    Member

    Откуда:
    Сообщений: 919
    DnRumata
    aleks222,

    Ога, ну и как узнать что дата ничем не перекрыта?


    Может сразу в управдомы преквалифицироваиться?

    +
    set nocount on;
    
    declare @SourceData table
    (
    	--RID UNIQUEIDENTIFIER,
    	CI NVARCHAR(10) NOT NULL,
    	DateTimeStarted DATE NOT NULL,
    	DateTimeCompleted DATE NOT NULL
    	, n int identity unique
    )
    
    INSERT INTO @SourceData
    (
    CI,
    DateTimeStarted,
    DateTimeCompleted
    )
    VALUES
    (
    'CI0025',
    CAST('20190110' AS DATE),
    CAST('20190114' AS DATE)
    )
    INSERT INTO @SourceData
    (
    CI,
    DateTimeStarted,
    DateTimeCompleted
    )
    VALUES
    (
    'CI0025',
    CAST('20190112' AS DATE),
    CAST('20190117' AS DATE)
    )
    INSERT INTO @SourceData
    (
    CI,
    DateTimeStarted,
    DateTimeCompleted
    )
    VALUES
    (
    'CI0025',
    CAST('20190116' AS DATE),
    CAST('20190120' AS DATE)
    )
    INSERT INTO @SourceData
    (
    CI,
    DateTimeStarted,
    DateTimeCompleted
    )
    VALUES
    (
    'CI0025',
    CAST('20181003' AS DATE),
    CAST('20181004' AS DATE)
    )
    INSERT INTO @SourceData
    (
    CI,
    DateTimeStarted,
    DateTimeCompleted
    )
    VALUES
    (
    'CI0025',
    CAST('20181003' AS DATE),
    CAST('20181005' AS DATE)
    )
    INSERT INTO @SourceData
    (
    CI,
    DateTimeStarted,
    DateTimeCompleted
    )
    VALUES
    (
    'CI0025',
    CAST('20181007' AS DATE),
    CAST('20181009' AS DATE)
    )
    INSERT INTO @SourceData
    (
    CI,
    DateTimeStarted,
    DateTimeCompleted
    )
    VALUES
    (
    'CI0025',
    CAST('20181008' AS DATE),
    CAST('20191010' AS DATE)
    )
    INSERT INTO @SourceData
    (
    CI,
    DateTimeStarted,
    DateTimeCompleted
    )
    VALUES
    (
    'CI0025',
    CAST('20171010' AS DATE),
    CAST('20171014' AS DATE)
    )
    INSERT INTO @SourceData
    (
    CI,
    DateTimeStarted,
    DateTimeCompleted
    )
    VALUES
    (
    'CI0025',
    CAST('20170501' AS DATE),
    CAST('20170503' AS DATE)
    )
    INSERT INTO @SourceData
    (
    CI,
    DateTimeStarted,
    DateTimeCompleted
    )
    VALUES
    (
    'CI0025',
    CAST('20170502' AS DATE),
    CAST('20170506' AS DATE)
    )
    INSERT INTO @SourceData
    (
    CI,
    DateTimeStarted,
    DateTimeCompleted
    )
    VALUES
    (
    'CI0025',
    CAST('20170505' AS DATE),
    CAST('20170520' AS DATE)
    )
    
    
    declare @b table(CI NVARCHAR(10) NOT NULL,	DateTimeStarted DATE NOT NULL, n int identity primary key);
    declare @e table(CI NVARCHAR(10) NOT NULL,	DateTimeCompleted DATE NOT NULL, n int identity primary key);
    
    with t as ( select *  from @SourceData)
    insert @b
      select CI, DateTimeStarted from t where not exists( select * from t as t0 where t0.CI = t.CI and ( t0.DateTimeStarted < t.DateTimeStarted and t.DateTimeStarted <= t0.DateTimeCompleted and t0.n <> t.n ) or (t0.DateTimeStarted = t.DateTimeStarted and t0.n < t.n ) ) order by DateTimeStarted asc
    
    select * from @b;
    
    with t as ( select *  from @SourceData)
    insert @e
      select CI, DateTimeStarted from t where not exists( select * from t as t0 where t0.CI = t.CI and ( t0.DateTimeStarted <= t.DateTimeCompleted and t.DateTimeCompleted < t0.DateTimeCompleted and t0.n <> t.n ) or ( t.DateTimeCompleted = t0.DateTimeCompleted and t0.n > t.n ) ) order by DateTimeCompleted asc
    
    select * from @e;
    
    select b.DateTimeStarted, e.DateTimeCompleted
      from @b as b inner join @e as e on e.CI = b.CI and e.n = b.n
    


    Сообщение было отредактировано: 26 апр 19, 21:13
    26 апр 19, 21:01    [21873037]     Ответить | Цитировать Сообщить модератору
     Re: Объединение пересекающихся диапазонов дат.  [new]
    invm
    Member

    Откуда: Москва
    Сообщений: 9270
    with t1 as
    (
    select
     *,
     dense_rank() over (partition by t.CI order by t.DateTimeStarted) as r
    from
     #SourceData t
    where
     not exists(select 1 from #SourceData where CI = t.CI and DateTimeStarted < t.DateTimeStarted and DateTimeCompleted > t.DateTimeStarted)
    ),
    t2 as
    (
    select
     *,
     dense_rank() over (partition by t.CI order by t.DateTimeStarted) as r
    from
     #SourceData t
    where
     not exists(select 1 from #SourceData where CI = t.CI and DateTimeCompleted > t.DateTimeCompleted and DateTimeStarted < t.DateTimeCompleted)
    )
    select
     t1.CI, min(t1.DateTimeStarted), max(t2.DateTimeCompleted)
    from
     t1 join
     t2 on t2.CI = t1.CI and t2.r = t1.r
    group by
     t1.CI, t1.r;
    
    26 апр 19, 21:11    [21873048]     Ответить | Цитировать Сообщить модератору
     Re: Объединение пересекающихся диапазонов дат.  [new]
    aleks222
    Member

    Откуда:
    Сообщений: 919
    Слегка лоханулся...
    declare @b table(CI NVARCHAR(10) NOT NULL, DateTimeStarted DATE NOT NULL, n int identity,  primary key(CI , n));
    declare @e table(CI NVARCHAR(10) NOT NULL, DateTimeCompleted DATE NOT NULL, n int identity, primary key(CI , n));
    
    with t as ( select *  from @SourceData)
    insert @b
      select CI, DateTimeStarted from t where not exists( select * from t as t0 where t0.CI = t.CI and ( t0.DateTimeStarted < t.DateTimeStarted and t.DateTimeStarted <= t0.DateTimeCompleted and t0.n <> t.n ) or (t0.DateTimeStarted = t.DateTimeStarted and t0.n < t.n ) ) order by CI, DateTimeStarted asc
    
    select * from @b;
    
    with t as ( select *  from @SourceData)
    insert @e
      select CI, DateTimeStarted from t where not exists( select * from t as t0 where t0.CI = t.CI and ( t0.DateTimeStarted <= t.DateTimeCompleted and t.DateTimeCompleted < t0.DateTimeCompleted and t0.n <> t.n ) or ( t.DateTimeCompleted = t0.DateTimeCompleted and t0.n > t.n ) ) order by CI, DateTimeCompleted asc
    
    select * from @e;
    
    select b.DateTimeStarted, e.DateTimeCompleted
      from @b as b inner join @e as e on e.CI = b.CI and e.n = b.n
    
    26 апр 19, 21:14    [21873050]     Ответить | Цитировать Сообщить модератору
     Re: Объединение пересекающихся диапазонов дат.  [new]
    ПЕНСИОНЕРКА
    Member

    Откуда: Владимирская обл
    Сообщений: 4723
    vikkiv
    искать пробелы между диапазонами

    на этом и основан мой пример в коде --немного наврала с if...else...endif
    результат за 1 проход , причем можно подсчитать количество попаданий в интервал и график построить по интервалу

    Sub mm190426()
    Dim xm(0 To 50000) As Long
    Dim d1 As Long, j1 As Long, r1 As Long, r1k As Long
    Dim dnach As Long, dkon As Long, k, dn, dk
    r1 = 1
    r1k = 11
    For j1 = r1 To r1k
    dnach = Cells(j1, 1)
    dkon = Cells(j1, 2)
    Debug.Print j1, Cells(j1, 1), Cells(j1, 2), Cells(j1, 1) - Cells(j1, 2)
        For d1 = dnach To dkon
        xm(d1) = 1
        Next d1
    Next j1
    
    ''''''''''''
    k = 0
    For d1 = 1 To 50000
        If xm(d1) = 0 Then
            If k > 0 Then
            Debug.Print CDate(dn), CDate(dk), dk - dn + 1
            End If
             k = 0
        Else
          k = k + 1
          If k = 1 Then dn = d1
          dk = d1
        End If
    Next d1
    
    End Sub
    
    26 апр 19, 21:16    [21873053]     Ответить | Цитировать Сообщить модератору
     Re: Объединение пересекающихся диапазонов дат.  [new]
    aleks222
    Member

    Откуда:
    Сообщений: 919
    invm
    with t1 as
    (
    select
     *,
     dense_rank() over (partition by t.CI order by t.DateTimeStarted) as r
    from
     #SourceData t
    where
     not exists(select 1 from #SourceData where CI = t.CI and DateTimeStarted < t.DateTimeStarted and DateTimeCompleted > t.DateTimeStarted)
    ),
    t2 as
    (
    select
     *,
     dense_rank() over (partition by t.CI order by t.DateTimeStarted) as r
    from
     #SourceData t
    where
     not exists(select 1 from #SourceData where CI = t.CI and DateTimeCompleted > t.DateTimeCompleted and DateTimeStarted < t.DateTimeCompleted)
    )
    select
     t1.CI, min(t1.DateTimeStarted), max(t2.DateTimeCompleted)
    from
     t1 join
     t2 on t2.CI = t1.CI and t2.r = t1.r
    group by
     t1.CI, t1.r;
    


    Садись, неуд.
    При наличии двух полностью совпадающих диапазонов это обломается.
    26 апр 19, 21:17    [21873055]     Ответить | Цитировать Сообщить модератору
     Re: Объединение пересекающихся диапазонов дат.  [new]
    DnRumata
    Member

    Откуда:
    Сообщений: 11
    invm
    with t1 as
    (
    select
     *,
     dense_rank() over (partition by t.CI order by t.DateTimeStarted) as r
    from
     #SourceData t
    where
     not exists(select 1 from #SourceData where CI = t.CI and DateTimeStarted < t.DateTimeStarted and DateTimeCompleted > t.DateTimeStarted)
    ),
    t2 as
    (
    select
     *,
     dense_rank() over (partition by t.CI order by t.DateTimeStarted) as r
    from
     #SourceData t
    where
     not exists(select 1 from #SourceData where CI = t.CI and DateTimeCompleted > t.DateTimeCompleted and DateTimeStarted < t.DateTimeCompleted)
    )
    select
     t1.CI, min(t1.DateTimeStarted), max(t2.DateTimeCompleted)
    from
     t1 join
     t2 on t2.CI = t1.CI and t2.r = t1.r
    group by
     t1.CI, t1.r;
    


    Не могли бы вы объяснить, как работает подобный код?
    Что возвращает 1ая и 2ая CTE?
    И DenseRank - он на чем основывается?
    26 апр 19, 21:23    [21873056]     Ответить | Цитировать Сообщить модератору
     Re: Объединение пересекающихся диапазонов дат.  [new]
    DnRumata
    Member

    Откуда:
    Сообщений: 11
    aleks222
    Слегка лоханулся...
    declare @b table(CI NVARCHAR(10) NOT NULL, DateTimeStarted DATE NOT NULL, n int identity,  primary key(CI , n));
    declare @e table(CI NVARCHAR(10) NOT NULL, DateTimeCompleted DATE NOT NULL, n int identity, primary key(CI , n));
    
    with t as ( select *  from @SourceData)
    insert @b
      select CI, DateTimeStarted from t where not exists( select * from t as t0 where t0.CI = t.CI and ( t0.DateTimeStarted < t.DateTimeStarted and t.DateTimeStarted <= t0.DateTimeCompleted and t0.n <> t.n ) or (t0.DateTimeStarted = t.DateTimeStarted and t0.n < t.n ) ) order by CI, DateTimeStarted asc
    
    select * from @b;
    
    with t as ( select *  from @SourceData)
    insert @e
      select CI, DateTimeStarted from t where not exists( select * from t as t0 where t0.CI = t.CI and ( t0.DateTimeStarted <= t.DateTimeCompleted and t.DateTimeCompleted < t0.DateTimeCompleted and t0.n <> t.n ) or ( t.DateTimeCompleted = t0.DateTimeCompleted and t0.n > t.n ) ) order by CI, DateTimeCompleted asc
    
    select * from @e;
    
    select b.DateTimeStarted, e.DateTimeCompleted
      from @b as b inner join @e as e on e.CI = b.CI and e.n = b.n
    


    2017-10-01 2017-09-13 - мягко говоря не то.
    26 апр 19, 21:31    [21873061]     Ответить | Цитировать Сообщить модератору
     Re: Объединение пересекающихся диапазонов дат.  [new]
    DnRumata
    Member

    Откуда:
    Сообщений: 11
    ПЕНСИОНЕРКА
    vikkiv
    искать пробелы между диапазонами

    на этом и основан мой пример в коде --немного наврала с if...else...endif
    результат за 1 проход , причем можно подсчитать количество попаданий в интервал и график построить по интервалу

    Sub mm190426()
    Dim xm(0 To 50000) As Long
    Dim d1 As Long, j1 As Long, r1 As Long, r1k As Long
    Dim dnach As Long, dkon As Long, k, dn, dk
    r1 = 1
    r1k = 11
    For j1 = r1 To r1k
    dnach = Cells(j1, 1)
    dkon = Cells(j1, 2)
    Debug.Print j1, Cells(j1, 1), Cells(j1, 2), Cells(j1, 1) - Cells(j1, 2)
        For d1 = dnach To dkon
        xm(d1) = 1
        Next d1
    Next j1
    
    ''''''''''''
    k = 0
    For d1 = 1 To 50000
        If xm(d1) = 0 Then
            If k > 0 Then
            Debug.Print CDate(dn), CDate(dk), dk - dn + 1
            End If
             k = 0
        Else
          k = k + 1
          If k = 1 Then dn = d1
          dk = d1
        End If
    Next d1
    
    End Sub
    


    В коде я бы давно сделал, но CRT создать не могу.
    Изначально выборка вообще помощь другому отделу Sharepoint щиков.

    Ну и циклом лопатить 800к строк определенно не круто
    26 апр 19, 21:39    [21873063]     Ответить | Цитировать Сообщить модератору
     Re: Объединение пересекающихся диапазонов дат.  [new]
    DnRumata
    Member

    Откуда:
    Сообщений: 11
    vikkiv
    DnRumata,

    тогда надо профайлинг данных делать и искать оптимальный способ
    т.к. теоретически можно подойти и с другого конца - искать пробелы между диапазонами
    когда ясно будет чего меньше - от туда уже копать вариант подходящий по производительности


    Нет пробелы искать не стоит, по одной простой причине:

    Диапазоны между собой не связаны.

    Тобишь может быть 2019-01-10 - 2019-01-11
    2019-01-30 - 2019-02-05

    И это 2 раздельных строки.

    Фишка в том что диапазонов дохрена + дублирующиеся данные внутри, дубликаты я могу почистить заранее.

    У меня проиндексировано и группировку вывел ибо быстрее чем Top 1 от сортировки(точнее легче индексом покрыть, из-за того что работа на времянка плодить их в реалтайме не хочется, ибо скорость -_-)

    Разные CI у каждого свои диапазоны, они могут совпадать между разными CI.

    У меня голова пухнет, 2 выборки в комментах рабочие, но мне нужно время чтобы полностью понять как они работают )
    26 апр 19, 21:44    [21873065]     Ответить | Цитировать Сообщить модератору
     Re: Объединение пересекающихся диапазонов дат.  [new]
    fallenyasha
    Member

    Откуда:
    Сообщений: 12
    Был не прав со своим решением, там косяк с интервалами поглощающими другие.

    Касательно решения invm: вроде бы как есть ошибка во второй CTE, там dense_rank нужен с сортировкой по DateTimeCompleted

    with
    t1 as
    	(select CI, DateTimeStarted, dense_rank() over (partition by t.CI order by t.DateTimeStarted) as r
    	from #SourceData t
    	where not exists(select 1 from #SourceData where CI = t.CI and DateTimeStarted < t.DateTimeStarted and DateTimeCompleted > t.DateTimeStarted)),
    t2 as
    	(select CI, DateTimeCompleted, dense_rank() over (partition by t.CI order by t.DateTimeCompleted) as r
    	from #SourceData t
    	where not exists(select 1 from #SourceData where CI = t.CI and DateTimeStarted < t.DateTimeCompleted and DateTimeCompleted > t.DateTimeCompleted))
    select distinct
    	t1.CI, t1.DateTimeStarted, t2.DateTimeCompleted
    from t1
    inner join t2 on t2.CI = t1.CI and t2.r = t1.r
    


    И возможно что distinct отработает быстрее чем группировка, надо смотреть на реальных объемах данных.
    26 апр 19, 21:47    [21873066]     Ответить | Цитировать Сообщить модератору
     Re: Объединение пересекающихся диапазонов дат.  [new]
    fallenyasha
    Member

    Откуда:
    Сообщений: 12
    Работает сие так:
    CTE1 вытаскивает все DateTimeStarted не попадающие ни в какие другие интервалы
    CTE2 вытаскивает все DateTimeCompleted не попадающие ни в какие другие интервалы
    dense_rank() нумерует строки с разбивкой по CI и сортировкой по дате, при том что для каждой уникальной даты будет проставлен новый последовательный номер

    Например в первой CTE
    2017-05-01 1
    2017-10-10 2
    2018-10-03 3
    2018-10-03 3
    2018-10-07 4

    А далее по сути надо вытащить уникальные записи и сджойнить, уникальность либо группировкой, либо дистинктом.
    26 апр 19, 21:52    [21873071]     Ответить | Цитировать Сообщить модератору
     Re: Объединение пересекающихся диапазонов дат.  [new]
    invm
    Member

    Откуда: Москва
    Сообщений: 9270
    DnRumata
    Что возвращает 1ая и 2ая CTE?
    И DenseRank - он на чем основывается?
    Выполните отдельно запросы из CTE и все увидите.
    А вообще поищите по форуму темы по "объединение интервалов", "непрерывный интервал" и т.п.
    26 апр 19, 22:00    [21873079]     Ответить | Цитировать Сообщить модератору
     Re: Объединение пересекающихся диапазонов дат.  [new]
    DnRumata
    Member

    Откуда:
    Сообщений: 11
    fallenyasha,

    Сейчас проверил, вроде норм у INVM.

    Косяк нашел, поправил, но на ранках шустрее летает и план поприятней.

    Спасибо за разъяснение, как дорвусь до боевых данных проверю, тобишь в понедельник.

    Наверное придется слегка перелопатить индексы, но с этим уже разберусь.


    Огромное спасибо ребят, если снова всплывет отпишу, хотя надеюсь сам смогу починить :З
    26 апр 19, 22:04    [21873081]     Ответить | Цитировать Сообщить модератору
    Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
    Все форумы / Microsoft SQL Server Ответить