Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / И снова к вопросу пересекающихся интервалов времени / 9 сообщений из 9, страница 1 из 1
07.10.2018, 08:28
    #39713976
nk13
nk13
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
И снова к вопросу пересекающихся интервалов времени
Возникла задача, имеющая похожие примеры,
 
ссылки
http://www.sqlbooks.ru/readarticle.aspx?part=09&file=addition11
http://www.sql.ru/forum/1661/zapros
http://www.sql.ru/forum/596967/peresechenie-periodov

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

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

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
--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)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.10.2018, 09:59
    #39713991
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
И снова к вопросу пересекающихся интервалов времени
Учиться надо было в школе.
Код: sql
1.
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 - будет только один раз.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.10.2018, 10:02
    #39713992
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
И снова к вопросу пересекающихся интервалов времени
[quot nk13]И второй момент, как корректно вычислить среднее значение для случаев, когда на один интервал времени может накладываться неизвестное количество временных интервалов, значение каждого из которых нужно учесть.

Элементарно, Ватсон!
Надо вычислить среднее для каждой точки начала и конца отрезков.
Между ними будет постоянно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.10.2018, 12:03
    #39714008
nk13
nk13
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
И снова к вопросу пересекающихся интервалов времени
aleks222,
авторНадо вычислить среднее для каждой точки начала и конца отрезков.
Между ними будет постоянно.

Согласен. Вопрос в правильном формировании этих отрезков.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.10.2018, 12:12
    #39714009
nk13
nk13
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
И снова к вопросу пересекающихся интервалов времени
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 - будет только один раз.

Специально так делал, чтобы получить в итоге по два отрезка, но как видно выше это не панацея. Нужно подумать, насчет идеи с узловыми точками, может можно и без дублирования записей обойтись.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.10.2018, 13:37
    #39714023
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
И снова к вопросу пересекающихся интервалов времени
nk13Согласен. Вопрос в правильном формировании этих отрезков.

Ничо ты не понял.
"Формировать" - не надо.
Достаточно посчитать среднее для всех СУЩЕСТВУЮЩИХ концов.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.10.2018, 14:18
    #39714033
nk13
nk13
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
И снова к вопросу пересекающихся интервалов времени
aleks222,
и тут письмо дошло, спасибо ))
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.10.2018, 15:02
    #39714038
nk13
nk13
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
И снова к вопросу пересекающихся интервалов времени
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.10.2018, 16:15
    #39714050
nk13
nk13
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
И снова к вопросу пересекающихся интервалов времени
Верхнюю границу тоже найти элементарно.

aleks222, огромное спасибо за то, что направили на правильное решение.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / И снова к вопросу пересекающихся интервалов времени / 9 сообщений из 9, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]