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

Откуда:
Сообщений: 137
Возникла задача, имеющая похожие примеры,
+ ссылки

http://www.sqlbooks.ru/readarticle.aspx?part=09&file=addition11
https://www.sql.ru/forum/1661/zapros
https://www.sql.ru/forum/596967/peresechenie-periodov

но все же отличающаяся от них, в которой немного запутался.

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

Следовательно для начала необходимо разбить имеющиеся периоды с учетом пересечений.

--CREATE TABLE Test (Id int, Date1 DATETIME, Date2 DATETIME, Value NUMERIC(12,2))

-- случайные примеры значений
/*
INSERT INTO Test (Id, Date1, Date2, Value) VALUES (1, CAST('20180101' AS VARCHAR(8)), CAST('20180101' AS VARCHAR(8)), 10)
INSERT INTO Test (Id, Date1, Date2, Value) VALUES (2, CAST('20180106' AS VARCHAR(8)), CAST('20180107' AS VARCHAR(8)), 6)
INSERT INTO Test (Id, Date1, Date2, Value) VALUES (3, CAST('20180103' AS VARCHAR(8)), CAST('20180112' AS VARCHAR(8)), 2)
INSERT INTO Test (Id, Date1, Date2, Value) VALUES (4, CAST('20180115' AS VARCHAR(8)), CAST('20180120' AS VARCHAR(8)), 8)
INSERT INTO Test (Id, Date1, Date2, Value) VALUES (5, CAST('20180122' AS VARCHAR(8)), CAST('20180130' AS VARCHAR(8)), 12)
INSERT INTO Test (Id, Date1, Date2, Value) VALUES (6, CAST('20180128' AS VARCHAR(8)), CAST('20180215' AS VARCHAR(8)), 5)
INSERT INTO Test (Id, Date1, Date2, Value) VALUES (7, CAST('20180201' AS VARCHAR(8)), CAST('20180205' AS VARCHAR(8)), 0)

INSERT INTO Test (Id, Date1, Date2, Value) VALUES (8, CAST('20180310' AS VARCHAR(8)), CAST('20180320' AS VARCHAR(8)), 6)
INSERT INTO Test (Id, Date1, Date2, Value) VALUES (9, CAST('20180310' AS VARCHAR(8)), CAST('20180315' AS VARCHAR(8)), 3)
INSERT INTO Test (Id, Date1, Date2, Value) VALUES (10, CAST('20180324' AS VARCHAR(8)), CAST('20180330' AS VARCHAR(8)), 11)
INSERT INTO Test (Id, Date1, Date2, Value) VALUES (11, CAST('20180328' AS VARCHAR(8)), CAST('20180330' AS VARCHAR(8)), 9)
INSERT INTO Test (Id, Date1, Date2, Value) VALUES (12, CAST('20180401' AS VARCHAR(8)), CAST('20180405' AS VARCHAR(8)), 4)
INSERT INTO Test (Id, Date1, Date2, Value) VALUES (13, CAST('20180401' AS VARCHAR(8)), CAST('20180405' AS VARCHAR(8)), 6)
*/

-- Пытаюсь выделить все периоды,которые как-то пересекаются между собой следующим запросом

SELECT DISTINCT 
       CASE 
           WHEN t1.Date1 < t2.Date1 AND t1.Date2 <= t2.Date2 THEN t1.Date1
           WHEN t1.Date1 <= t2.Date1 AND t1.Date2 > t2.Date2 THEN DATEADD(d, 1, t2.Date2)
           WHEN t1.Date1 > t2.Date1 AND t1.Date2 > t2.Date2 THEN t1.Date1
           WHEN t1.Date1 >= t2.Date1 AND t1.Date2 <= t2.Date2 THEN t1.Date1
       END Date1,
       CASE 
           WHEN t1.Date1 < t2.Date1 AND t1.Date2 <= t2.Date2 THEN DATEADD(d, -1, t2.Date1)
           WHEN t1.Date1 <= t2.Date1 AND t1.Date2 > t2.Date2 THEN t1.Date2
           WHEN t1.Date1 > t2.Date1 AND t1.Date2 > t2.Date2 THEN t2.Date2
           WHEN t1.Date1 >= t2.Date1 AND t1.Date2 <= t2.Date2 THEN t1.Date2
       END Date2
FROM Test t1 INNER JOIN Test t2 ON (t1.Date1 BETWEEN t2.Date1 AND t2.Date2 OR t1.Date2 BETWEEN t2.Date1 AND t2.Date2
                                                                OR (t1.Date1 < t2.Date1 AND t1.Date2 > t2.Date2))  AND t1.Id != t2.Id
ORDER BY 1, 2


Получаю:

Date1 Date2
2018-01-06 00:00:00.000 2018-01-07 00:00:00.000
2018-01-08 00:00:00.000 2018-01-12 00:00:00.000
2018-01-22 00:00:00.000 2018-01-27 00:00:00.000
2018-01-28 00:00:00.000 2018-01-30 00:00:00.000
2018-02-01 00:00:00.000 2018-02-05 00:00:00.000
2018-02-06 00:00:00.000 2018-02-15 00:00:00.000
2018-03-10 00:00:00.000 2018-03-15 00:00:00.000
2018-03-16 00:00:00.000 2018-03-20 00:00:00.000
2018-03-24 00:00:00.000 2018-03-27 00:00:00.000
2018-03-28 00:00:00.000 2018-03-30 00:00:00.000
2018-04-01 00:00:00.000 2018-04-05 00:00:00.000

не хватает двух периодов:
2018-01-03 2018-01-05
2018-01-31 2018-01-31


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


+ система

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
7 окт 18, 08:28    [21697223]     Ответить | Цитировать Сообщить модератору
 Re: И снова к вопросу пересекающихся интервалов времени  [new]
aleks222
Member

Откуда:
Сообщений: 919
Учиться надо было в школе.
FROM Test t1 INNER JOIN Test t2 ON t1.Date1 <= t2.Date2 and t2.Date1 <= t1.Date2 and t1.Id <> t2.Id


and t1.Id <> t2.Id надо ли тебе ДВА раза одинаковые периоды?

and t1.Id < t2.Id - будет только один раз.
7 окт 18, 09:59    [21697246]     Ответить | Цитировать Сообщить модератору
 Re: И снова к вопросу пересекающихся интервалов времени  [new]
aleks222
Member

Откуда:
Сообщений: 919
[quot nk13]И второй момент, как корректно вычислить среднее значение для случаев, когда на один интервал времени может накладываться неизвестное количество временных интервалов, значение каждого из которых нужно учесть.

Элементарно, Ватсон!
Надо вычислить среднее для каждой точки начала и конца отрезков.
Между ними будет постоянно.
7 окт 18, 10:02    [21697247]     Ответить | Цитировать Сообщить модератору
 Re: И снова к вопросу пересекающихся интервалов времени  [new]
nk13
Member

Откуда:
Сообщений: 137
aleks222,
автор
Надо вычислить среднее для каждой точки начала и конца отрезков.
Между ними будет постоянно.


Согласен. Вопрос в правильном формировании этих отрезков.
7 окт 18, 12:03    [21697291]     Ответить | Цитировать Сообщить модератору
 Re: И снова к вопросу пересекающихся интервалов времени  [new]
nk13
Member

Откуда:
Сообщений: 137
aleks222,
Учиться надо было в школе.


Учиться никогда не поздно. :)


автор
FROM Test t1 INNER JOIN Test t2 ON t1.Date1 <= t2.Date2 and t2.Date1 <= t1.Date2 and t1.Id <> t2.Id


and t1.Id <> t2.Id надо ли тебе ДВА раза одинаковые периоды?

and t1.Id < t2.Id - будет только один раз.


Специально так делал, чтобы получить в итоге по два отрезка, но как видно выше это не панацея. Нужно подумать, насчет идеи с узловыми точками, может можно и без дублирования записей обойтись.
7 окт 18, 12:12    [21697292]     Ответить | Цитировать Сообщить модератору
 Re: И снова к вопросу пересекающихся интервалов времени  [new]
aleks222
Member

Откуда:
Сообщений: 919
nk13
Согласен. Вопрос в правильном формировании этих отрезков.


Ничо ты не понял.
"Формировать" - не надо.
Достаточно посчитать среднее для всех СУЩЕСТВУЮЩИХ концов.
7 окт 18, 13:37    [21697332]     Ответить | Цитировать Сообщить модератору
 Re: И снова к вопросу пересекающихся интервалов времени  [new]
nk13
Member

Откуда:
Сообщений: 137
aleks222,
и тут письмо дошло, спасибо ))
7 окт 18, 14:18    [21697347]     Ответить | Цитировать Сообщить модератору
 Re: И снова к вопросу пересекающихся интервалов времени  [new]
nk13
Member

Откуда:
Сообщений: 137
aleks222, вашим способом и правда всё просто, осталось концовки периода получить

SELECT q.*
FROM (SELECT DISTINCT t.Date1 dt,
(SELECT AVG(t0.Value / (DATEDIFF(d, t0.Date1, t0.Date2) + 1))
FROM Test t0
WHERE t.Date1 BETWEEN t0.Date1 AND t0.Date2) Average
FROM Test t
UNION
SELECT DISTINCT DATEADD(d, 1, t.Date2) dt,
(SELECT AVG(t0.Value / (DATEDIFF(d, t0.Date1, t0.Date2) + 1))
FROM Test t0
WHERE DATEADD(d, 1, t.Date2) BETWEEN t0.Date1 AND t0.Date2) Average
FROM Test t) q
WHERE q.Average IS NOT NULL
ORDER BY 1
7 окт 18, 15:02    [21697362]     Ответить | Цитировать Сообщить модератору
 Re: И снова к вопросу пересекающихся интервалов времени  [new]
nk13
Member

Откуда:
Сообщений: 137
Верхнюю границу тоже найти элементарно.

aleks222, огромное спасибо за то, что направили на правильное решение.
7 окт 18, 16:15    [21697383]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить