Datediff между непересекающимися строками в таблице

Я хотел бы взять среднее значение разницы во времени из таблицы 1 ниже. Значения не являются последовательными и иногда значение времени повторяется, поэтому мне нужно 1) Сортировать по времени, 2) отбрасывать не уникальные значения, 3) выполнять разницу во времени (в миллисекундах), а затем 4) усреднять полученные значения разницы во времени. Далее я хотел бы 5) ограничить операцию datediff выбранным временным диапазоном, таким как Где _TimeStamp >= '20091220 11:59:56.1' и _TimeStamp = ' 20091220 11: 59: 56.8'. Я просто в тупике, как все это сложить!

Таблица 1:
_TimeStamp
2009-12-20 11: 59: 56.0
2009-12-20 11: 59: 56.5
2009-12-20 11: 59: 56.3
2009-12-20 11: 59: 56.4
2009-12-20 11: 59: 56.4
2009-12-20 11: 59: 56.9

3 2
sql sql-server unique datediff

3 ответа:

Вот тот, который работает и не является уродливым: 

;WITH Time_CTE AS
(
    SELECT
        MIN(_Timestamp) AS dt,
        ROW_NUMBER() OVER (ORDER BY MIN(_Timestamp)) AS RowNum
    FROM Table1
    GROUP BY _Timestamp
)
SELECT
    t1.dt AS StartDate,
    t2.dt AS EndDate,
    DATEDIFF(MS, t1.dt, t2.dt) AS Elapsed
FROM Time_CTE t1
INNER JOIN Time_CTE t2
ON t2.RowNum = t1.RowNum + 1

Даст вам следующие выходные данные из вашего примера: 

StartDate               | EndDate                 | Elapsed
------------------------+-------------------------+--------
2009-12-20 11:59:56.000 | 2009-12-20 11:59:56.300 | 300
2009-12-20 11:59:56.300 | 2009-12-20 11:59:56.400 | 100
2009-12-20 11:59:56.400 | 2009-12-20 11:59:56.500 | 100
2009-12-20 11:59:56.500 | 2009-12-20 11:59:56.900 | 400

Edit: если вы хотите ограничить временные диапазоны, просто добавьте WHERE _Timestamp BETWEEN @StartDate AND @EndDate перед строкой GROUP BY.

Edit2: и если вы хотите среднее, то измените это последнее утверждение SELECT t1.dt, ... на: 

SELECT AVG(DATEDIFF(MS, t1.dt, t2.dt))
FROM Time_CTE t1 ... (same as above)
1

Шаг 1 состоит в том, чтобы выбрать только уникальные времена: 

SELECT DISTINCT _TimeStamp FROM table 
    WHERE _TimeStamp >= '20091220 11:59:56.1' AND _TimeStamp <= '20091220 11:59:56.8';

Затем, если вы хотите, скажем, сравнить все времена друг с другом (не уверен, как вы хотите выбрать время), вы можете сделать что-то сумасшедшее, например: 

SELECT t1._TimeStamp, t2._TimeStamp, DATEDIFF(ms,t1._TimeStamp,t2._TimeStamp) FROM 
    (SELECT DISTINCT _TimeStamp FROM table 
        WHERE _TimeStamp >= '20091220 11:59:56.1' AND _TimeStamp <= '20091220 11:59:56.8') AS t1 
    INNER JOIN
    (SELECT DISTINCT _TimeStamp FROM table 
        WHERE _TimeStamp >= '20091220 11:59:56.1' AND _TimeStamp <= '20091220 11:59:56.8') AS t2
WHERE t1._TimeStamp != t2._TimeStamp;

Мой синтаксис может быть отключен, потому что я исхожу из MySQL, но что-то подобное должно работать.

Если вам нужно среднее значение, вы можете попробовать взять среднее из приведенных выше результатов: 

SELECT AVG(DATEDIFF(ms,t1._TimeStamp,t2._TimeStamp)) FROM 
    (SELECT DISTINCT _TimeStamp FROM table 
        WHERE _TimeStamp >= '20091220 11:59:56.1' AND _TimeStamp <= '20091220 11:59:56.8') AS t1 
    INNER JOIN
    (SELECT DISTINCT _TimeStamp FROM table 
        WHERE _TimeStamp >= '20091220 11:59:56.1' AND _TimeStamp <= '20091220 11:59:56.8') AS t2
WHERE t1._TimeStamp != t2._TimeStamp;
Все еще непроверено, но теоретически, я думаю, это должно сработать.
1

Если мои предположения о том, чего вы хотите, верны, то я вижу два способа сделать это.

Прямой путь: 

SELECT
    AVG(DATEDIFF(ms, T1.my_time, T2.my_time))
FROM
    My_Table T1
INNER JOIN My_Table T2 ON
    T2.my_time > T1.my_time
WHERE
    NOT EXISTS
    (
        SELECT
            *
        FROM
            My_Table T3
        WHERE
            (T3.my_time > T1.my_time AND T3.my_time < T2.my_time) OR
            (T3.my_time = T1.my_time AND T3.my_pk < T1.my_pk) OR
            (T3.my_time = T2.my_time AND T3.my_pk < T2.my_pk)
    )

Хитрый способ: 

SELECT
    DATEDIFF(ms, MIN(my_time), MAX(my_time))/(COUNT(DISTINCT my_time) - 1)
FROM
    My_Table
В конце концов, средние различия-это всего лишь общая разница, деленная на количество делений, на которые вы ее разбиваете.

Вам нужно будет добавить предложения WHERE для диапазона дат, Если вы хотите ограничить этим, и вам нужно будет учитывать возможность деления на ноль во втором запросе.

1