Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
nk13 Member Откуда: Сообщений: 137 |
Возникла задача, имеющая похожие примеры,
но все же отличающаяся от них, в которой немного запутался. Существует идентификатор, две даты задающие период и некоторое значение за этот период, которое делится по дням внутри периода равномерно. Необходимо найти среднее значение по всем имеющимся периодам, учитывая все пересекающиеся (то есть в момент пересечения нужно взять части значений за момент пересечения и получить по ним среднее значение). Следовательно для начала необходимо разбить имеющиеся периоды с учетом пересечений. --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 Получаю:
Это логично, так как когда один период попадает внутрь другого, получается минимум три отрезка, а при сравнивании периодов с самими с собой можно выделить только два. Соответственно проблема возникла с поиском и добавлением пропусков в пересекающихся интервалах. И второй момент, как корректно вычислить среднее значение для случаев, когда на один интервал времени может накладываться неизвестное количество временных интервалов, значение каждого из которых нужно учесть.
|
||||
7 окт 18, 08:28 [21697223] Ответить | Цитировать Сообщить модератору |
aleks222 Member Откуда: Сообщений: 1236 |
Учиться надо было в школе.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] Ответить | Цитировать Сообщить модератору |
aleks222 Member Откуда: Сообщений: 1236 |
[quot nk13]И второй момент, как корректно вычислить среднее значение для случаев, когда на один интервал времени может накладываться неизвестное количество временных интервалов, значение каждого из которых нужно учесть. Элементарно, Ватсон! Надо вычислить среднее для каждой точки начала и конца отрезков. Между ними будет постоянно. |
7 окт 18, 10:02 [21697247] Ответить | Цитировать Сообщить модератору |
nk13 Member Откуда: Сообщений: 137 |
aleks222,
Согласен. Вопрос в правильном формировании этих отрезков. |
||
7 окт 18, 12:03 [21697291] Ответить | Цитировать Сообщить модератору |
nk13 Member Откуда: Сообщений: 137 |
aleks222,
Учиться никогда не поздно. :)
Специально так делал, чтобы получить в итоге по два отрезка, но как видно выше это не панацея. Нужно подумать, насчет идеи с узловыми точками, может можно и без дублирования записей обойтись. |
||||
7 окт 18, 12:12 [21697292] Ответить | Цитировать Сообщить модератору |
aleks222 Member Откуда: Сообщений: 1236 |
Ничо ты не понял. "Формировать" - не надо. Достаточно посчитать среднее для всех СУЩЕСТВУЮЩИХ концов. |
||
7 окт 18, 13:37 [21697332] Ответить | Цитировать Сообщить модератору |
nk13 Member Откуда: Сообщений: 137 |
aleks222, и тут письмо дошло, спасибо )) |
7 окт 18, 14:18 [21697347] Ответить | Цитировать Сообщить модератору |
nk13 Member Откуда: Сообщений: 137 |
aleks222, вашим способом и правда всё просто, осталось концовки периода получить
|
||
7 окт 18, 15:02 [21697362] Ответить | Цитировать Сообщить модератору |
nk13 Member Откуда: Сообщений: 137 |
Верхнюю границу тоже найти элементарно. aleks222, огромное спасибо за то, что направили на правильное решение. |
7 окт 18, 16:15 [21697383] Ответить | Цитировать Сообщить модератору |
Все форумы / Microsoft SQL Server | ![]() |