SQL Datediff - поиск datediff между строками

#sql #sql-server #tsql #datediff

#sql #sql-сервер #tsql #datediff

Вопрос:

Я хотел бы запросить базу данных с использованием sql, чтобы показать разницу во времени между идентификаторами 1,2,3 и так далее. в основном, он будет сравнивать строку под ним для всех записей. буду признателен за любую помощь.

 IDCODE  DATE TIME        DIFFERENCE (MINS)
1      02/03/2011 08:00        0
2      02/03/2011 08:10        10
3      02/03/2011 08:23        13
4       02/03/2011 08:25        2
5       02/03/2011 09:25        60
6       02/03/2011 10:20        55
7       02/03/2011 10:34        14

Спасибо!

Ответ №1:

При использовании SQL Server одним из способов является выполнение:

 DECLARE @Data TABLE (IDCode INTEGER PRIMARY KEY, DateVal DATETIME)
INSERT @Data VALUES (1, '2011-03-02 08:00')
INSERT @Data VALUES (2, '2011-03-02 08:10')
INSERT @Data VALUES (3, '2011-03-02 08:23')
INSERT @Data VALUES (4, '2011-03-02 08:25')
INSERT @Data VALUES (5, '2011-03-02 09:25')
INSERT @Data VALUES (6, '2011-03-02 10:20')
INSERT @Data VALUES (7, '2011-03-02 10:34')

SELECT t1.IDCode, t1.DateVal, ISNULL(DATEDIFF(mi, x.DateVal, t1.DateVal), 0) AS Mins
FROM @Data t1
    OUTER APPLY (
        SELECT TOP 1 DateVal FROM @Data t2 
        WHERE t2.IDCode < t1.IDCode ORDER BY t2.IDCode DESC) x

Другой способ — использовать CTE и ROW_NUMBER(), например:

 ;WITH CTE AS (SELECT ROW_NUMBER() OVER (ORDER BY IDCode) AS RowNo, IDCode, DateVal FROM @Data)

SELECT t1.IDCode, t1.DateVal, ISNULL(DATEDIFF(mi, t2.DateVal, t1.DateVal), 0) AS Mins
FROM CTE t1
    LEFT JOIN CTE t2 ON t1.RowNo = t2.RowNo   1
ORDER BY t1.IDCode

1. Мне нравится первый, он избавил меня от многих хлопот, но мой опыт работы с CTE не очень хорош. Большие таблицы требуют слишком больших ресурсов для базы данных.

2. Есть ли способ добавить количество, если IDCODE одинаков для 2 дат, и получить разницу между этими двумя строками с одинаковым IDCODE, а не инкрементным способом? Например: Давайте предположим, что первые две даты имеют IDCODE равный 1, поэтому я хочу получить datediff только между этими двумя строками.

Ответ №2:

Стандартное решение ANSI SQL. Должен работать в PostgreSQL, Oracle, DB2 и Teradata:

 SELECT idcode, 
       date_time, 
       date_time - lag(date_time) over (order by date_time) as difference
FROM your_table

Ответ №3:

Как упоминал @a_horse_with_no_name, используя функцию lag()

Я хотел за миллисекунду определить разницу.

 Select idcode,
       datetime,
       Difference = datediff(millisecond, lag(convert(datetime2,datetime)) over (order by convert(datetime2,datetime)),convert(datetime2,datetime))
From your_table

в моем случае мне нужно было преобразовать строку в datetime2