Вот грубо говоря такая таблица: ID  StartedAt, EndedAt
И грубо говоря по ней делается примерно такой запрос:
SUM(DATEDIFF(SECOND, [StartedAt], [EndedAt])).

Возникла ситуация, когда могут перекрываться промежутки, т.е. StartedAt больше, чем EndedAt в предыдущей строке. Либо у меня голова отказывается варить в субботу вечером, либо просто не существует способа извлечь сумму минус перекрывающиеся промежутки одним запросом. Обрабатывать запись за записью стремновато, т.к. их много-много... Может у кого-нибудь возникнет светлая идея?

Представь себе:
1 - 01.12 - 10.12 - 1600
2 - 05.12 - 12.12 - 1200

Вопрос: какая сумма должна быть за период со 2 по 7-ое, и главное, почему?
Правила сначала определи, как разделять сумму по датам.

причем тут числа?

т.е. зачем так?

> Представь себе:
> 1 - 01.12 - 10.12 - 1600
> 2 - 05.12 - 12.12 - 1200


так надо
> 1 - 01.12 - 10.12
> 2 - 05.12 - 12.12
ну а сумма - очевидна: 12 дней

Не понял что значит 1600 и 1200. Там полный timestamp хранится. Сумма будет равна количеству секунд между (01.12-12.12), т.е. сумма двух промежутков, минус их пересечение.

Единственное, что приходит в голову - это поставить триггер на добавление записи и если StartedAt меньше последнего EndedAt, то делать его равным последнему EndedAt. Тогда запрос будет тривиальным.

т.е., коль скоро дата суть число:
на параллельных прямых к оси OX есть отрезки
требуется найти сумму длин их проекций на OX

> Сумма будет равна количеству секунд между (01.12-12.12),
>  т.е. сумма двух промежутков, минус их пересечение.

А
Max([EndedAt]) - Min([StartedAt])
не то?

>  Sergey13 ©   (10.12.12 15:34) [6]


Нет, ведь между ними могут быть незаполненные промежутки, т.е. например:
1. 01.12.2012 - 03.12.2012
2. 05.12.2012 - 07.12.2012

> Иксик ©   (09.12.12 01:00)  

А зачем запросами, всего один запрос, а потом проход по датасет, очень быстро.

аля так посчитать пересечения, потом отнять от исходного

--сырьё
insert into TT
select * from (
select CONVERT(datetime, '01.01.2012') F, CONVERT(datetime, '03.01.2012') S
union select CONVERT(datetime, '12.01.2012'), CONVERT(datetime, '18.01.2012')
union select CONVERT(datetime, '15.01.2012'), CONVERT(datetime, '16.01.2012')
union select CONVERT(datetime, '17.01.2012'), CONVERT(datetime, '22.01.2012')
) A
--сырьё

--запрос
select
*,
case
 when (F1 between f2 and s2) and (S1 between f2 and s2) then DATEDIFF(DAY, [F1], [S1])
 when (F1 between f2 and s2) then  DATEDIFF(DAY, [F1], [s2])
 when (S1 between f2 and s2) then  DATEDIFF(DAY, [f2], [S1])
 else 0
end
from (
select T1.F f1, T1.S s1, T2.F f2, T2.S s2
from TT T1
full join TT T2 on T1.F <> T2.F and T1.S <> T2.S --построить почти :) полное произведение
) A
--запрос

> Anatoly Podgoretsky ©   (10.12.12 16:14) [8]
>
>
> > Иксик ©   (09.12.12 01:00)  
>
> А зачем запросами, всего один запрос, а потом проход по
> датасет, очень быстро.


Будет быстро? Там до 10 миллионов записей за раз может быть... А насчет триггеров вариант не нравится?

>  O'ShinW ©   (10.12.12 16:21) [9]


Но там не дефинированное количество записей.

А зачем тогда триггер, сразу пишите то что надо.

Надо перепипежить для MS, но идея и так понятна:



Для 1e7 записей будет нескоро, даже при наличии соответствующих индексов.

> Кщд   (11.12.12 10:10) [13]
> with g as (


Это таблица, к которой потом если обращаться, она не будет каждый раз пересчитываться. Правильно?

> Но там не дефинированное количество записей.

ну и что?
там для исходных данных у меня несколько примеров просто было

Но больше понравился вариант  Кщд  
         join g g2
           on g.dt between g2.dt and g2.dt2
              or g.dt2 between g2.dt and g2.dt2
сразу пересекающиеся
и потом пересечения

>O'ShinW ©   (12.12.12 13:33) [14]
материализовать(создать временную таблицу) или использовать inline(аналогия с inline в Delphi - корректна) решает оптимизатор СУБД. в Oracle существует возможность явного управления этим поведением посредством "подсказок"(hints) оптимизатору. как в MS, увы, не знаю.

> Кщд   (13.12.12 08:05) [16]

спасибо

Прошу прощения у автора ветки,
но еще раз хочу спросить

допустим, тяжелый запрос SQ (с группировками), вернул аля так
NAME(varchar2) VAL(number)
NAME1  VAL1
NAME1  VAL2
NAME1  VAL3
NAME2  VAL1
NAME2  VAL3

а теперь надо еще посчитать count(name) и получить аля так
NAME(varchar2) VAL(number)  CNT(number)
NAME1  VAL1 3    --3, потому что NAME1 присутствует 3 раза
NAME1  VAL2 3
NAME1  VAL3 3
NAME2  VAL1 2     --2, потому что NAME2 присутствует 2 раза
NAME2  VAL3 2

т.е. этот запрос SQ придется еще раз группировать, но тогда потеряется VAL

если написать аля
with G as ( SQ )
select
  G.NAME, G.VAL, G2.CNT
from
   G
   join  ГРУППИРОВКА_ПО_NAME(G) G2 on G.NAME = G2.NAME
тогда не будет по идее два раза тяжелый запрос выполнятся?

зы
Или как-то лучше можно решить?

O'ShinW ©   (13.12.12 10:09) [17]
как-будто, с 2005-го появились аналитические ф-ции)

O'ShinW ©   (13.12.12 10:09) [17]

ROLLUP ?

да, спасибо

> ROLLUP ?

и CUBE рулят!
(и чтение мануалов.. тоже рулит :))

ps
В MSSQL практически также
...
GROUP BY F1,F2
WITH CUBE

> и чтение мануалов.. тоже рулит


Всегда

да да да !!!

> Возникла ситуация, когда могут перекрываться промежутки,
>  т.е. StartedAt больше, чем EndedAt в предыдущей строке.
>

И че?
Что посчитать то надо? datediff  по строке  сколько один процесс занял и игого по всем?  так при чем тут предыдущие строки?
Или у тебя в одной строке Started позже Ended?  Ну тогда в консерватории что-то менять надо..

> Не понял что значит 1600 и 1200. Там полный timestamp хранится.
>  Сумма будет равна количеству секунд между (01.12-12.12),
>  т.е. сумма двух промежутков, минус их пересечение.

То есть тебе сначала надо схлопнуть все интервалы,  а потом ?
1,  01.01.01 12:35:00,  01.01.01 13:35:00
2,  01.01.01 12:36:00,  01.01.01 13:36:00
3,  01.01.01 13:39:00,  01.01.01 13:40:00
4,  01.01.01 14:35:00,  01.01.01 15:35:00
5,  02.01.01 12:35:00,  03.01.01 13:35:00
6,  03.01.01 14:35:00,  01.01.01 13:35:00

Что должно быть на выходе?
Почему именно так?


> Единственное, что приходит в голову - это поставить триггер
> на добавление записи и если StartedAt меньше последнего
> EndedAt, то делать его равным последнему EndedAt. Тогда
> запрос будет тривиальным.

Самое гнусное что можно придумать - это "корябать" входные данные под удобство запроса...