SQL Server: сравнение дат между строками

ProgramBox

SQL Server: сравнение дат между строками

Post author:admin
Запись опубликована:28 февраля, 2023
Post category:Вопросы по программированию

#sql #sql-server #datetime

#sql #sql-сервер #дата-время

Вопрос:

Я работаю с SQL server 2016 и не могу понять, как построить этот запрос.

Допустим, у меня есть такая таблица:

 ID  EntryTime               ResultTime
1   2016-05-02 13:30:00     2016-05-02 21:50:00
2   2016-05-02 14:45:00     2016-05-02 22:00:00
3   2016-05-02 16:30:00     2016-05-02 22:21:00
4   2016-05-03 01:00:00     2016-05-03 03:33:00
5   2016-05-03 10:30:00     2016-05-04 07:47:00
6   2016-05-03 12:30:00     2016-05-03 22:45:00
7   2016-05-04 11:30:00     2016-05-05 21:30:00
8   2016-05-04 12:30:00     2016-05-04 22:58:00
9   2016-05-04 13:30:00     2016-05-04 23:04:00
10  2016-05-04 13:45:00     2016-05-04 22:59:00
11  2016-05-04 14:00:00     2016-05-04 22:59:00
12  2016-05-04 14:15:00     2016-05-04 23:04:00
13  2016-05-04 17:45:00     2016-05-04 21:47:00
14  2016-05-05 23:30:00     2016-05-06 03:25:00
15  2016-05-05 23:45:00     2016-05-06 03:30:00
16  2016-05-06 00:00:00     2016-05-06 03:32:00
17  2016-05-06 00:15:00     2016-05-06 03:31:00
18  2016-05-06 00:30:00     2016-05-06 03:25:00
19  2016-05-06 00:45:00     2016-05-06 02:50:00
20  2016-05-06 01:00:00     2016-05-06 03:25:00

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

Например: время результата для строки 1 равно «2016-05-02 21:50:00», поэтому следующей строкой будет строка 4, поскольку это первая строка, в которой время ввода после времени результата, которое было выбрано в последний раз, следующая строка должна быть после времени результата строки 4 (После «2016-05-03 03:33:00»), поэтому следующей строкой будет строка 5.

Запрошенный результат:

 ID  EntryTime               ResultTime
1   2016-05-02 13:30:00     2016-05-02 21:50:00
4   2016-05-03 01:00:00     2016-05-03 03:33:00
5   2016-05-03 10:30:00     2016-05-04 07:47:00
7   2016-05-04 11:30:00     2016-05-05 21:30:00
14  2016-05-05 23:30:00     2016-05-06 03:25:00

1. ваш желаемый результат неясен .. можете ли вы быть более подробным…

2. Я думаю, что вы единственный, кто знает результат

3. Интересно… Определенно, это можно сделать в цикле WHILE, но как single SELECT, не уверен…

4. Какова версия вашего SQL Server? Пожалуйста, добавьте соответствующий тег.

Ответ №1:

Один из способов сделать это — использовать рекурсивный CTE для получения следующей строки. Например,

 with cte as (
    select *
    from myTable
    where id = 1
    union all
    select t.*
    from myTable t
    cross join cte
    where t.id = (
        select id 
        from (
            select id, row_number() over (order by id) rn 
            from myTable 
            where entrytime > cte.resulttime) z 
        where rn = 1)
    )
select * from cte;

РЕДАКТИРОВАТЬ: для нескольких «символов» вот метод, который будет работать (с примерами данных).

 DECLARE @myTable TABLE (Symbol CHAR(3), EntryTime DATETIME, ResultTime DATETIME)
INSERT @myTable VALUES ('AAA','2016-05-02 13:30:00','2016-05-02 21:50:00')
,('AAA','2016-05-02 14:45:00','2016-05-02 22:00:00')
,('AAA','2016-05-02 16:30:00','2016-05-02 22:21:00')
,('AAA','2016-05-03 01:00:00','2016-05-03 03:33:00')
,('AAA','2016-05-03 10:30:00','2016-05-04 07:47:00')
,('AAA','2016-05-03 12:30:00','2016-05-03 22:45:00')
,('AAA','2016-05-04 11:30:00','2016-05-05 21:30:00')
,('AAA','2016-05-04 12:30:00','2016-05-04 22:58:00')
,('AAA','2016-05-04 13:30:00','2016-05-04 23:04:00')
,('AAA','2016-05-04 13:45:00','2016-05-04 22:59:00')
,('AAA','2016-05-04 14:00:00','2016-05-04 22:59:00')
,('AAA','2016-05-04 14:15:00','2016-05-04 23:04:00')
,('AAA','2016-05-04 17:45:00','2016-05-04 21:47:00')
,('AAA','2016-05-05 23:30:00','2016-05-06 03:25:00')
,('AAA','2016-05-05 23:45:00','2016-05-06 03:30:00')
,('AAA','2016-05-06 00:00:00','2016-05-06 03:32:00')
,('AAA','2016-05-06 00:15:00','2016-05-06 03:31:00')
,('AAA','2016-05-06 00:30:00','2016-05-06 03:25:00')
,('AAA','2016-05-06 00:45:00','2016-05-06 02:50:00')
,('AAA','2016-05-06 01:00:00','2016-05-06 03:25:00')
,('BBB','2016-05-02 01:00:00','2016-05-02 03:01:00')
,('BBB','2016-05-02 02:00:00','2016-05-02 03:05:00')
,('BBB','2016-05-02 03:00:00','2016-05-02 03:40:00')
,('BBB','2016-05-02 04:00:00','2016-05-02 04:01:00')
,('BBB','2016-05-02 05:00:00','2016-05-03 07:00:00')
,('BBB','2016-05-02 06:00:00','2016-05-02 07:00:00')
,('BBB','2016-05-03 06:00:00','2016-05-03 07:05:00')
,('BBB','2016-05-04 06:01:00','2016-05-04 07:08:00')
,('BBB','2016-05-04 06:07:00','2016-05-04 07:52:00')
,('BBB','2016-05-05 06:00:00','2016-05-05 07:49:00')
,('CCC','2016-05-05 06:00:00','2016-05-05 07:04:00')
,('CCC','2016-05-05 06:05:00','2016-05-05 06:55:00')
,('CCC','2016-05-05 07:00:00','2016-05-05 07:10:00')
,('CCC','2016-05-05 07:06:00','2016-05-05 08:05:00')
,('CCC','2016-05-05 08:00:00','2016-05-05 08:15:00')
,('CCC','2016-05-05 08:09:00','2016-05-05 09:00:00');

WITH myTable AS (
    SELECT Symbol, EntryTime, ResultTime, ROW_NUMBER() OVER (PARTITION BY Symbol ORDER BY EntryTime) RN
    FROM @myTable)
, CTE AS (
    SELECT *
    FROM myTable
    WHERE RN = 1
    UNION ALL
    SELECT T.*
    FROM CTE
    CROSS APPLY (
        SELECT Symbol, EntryTime, ResultTime, RN
        FROM (
            SELECT *, ROW_NUMBER() OVER (ORDER BY EntryTime) RN2
            FROM myTable
            WHERE Symbol = CTE.Symbol
            AND EntryTime > CTE.ResultTime) Z
        WHERE RN2 = 1) T
    )
SELECT Symbol, EntryTime, ResultTime--, RN [ID?]
FROM CTE
ORDER BY Symbol;

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

2. MAXRECURSION с нулевым значением должно быть указано, верно? Значение по умолчанию равно 100.

3. Идеальный ответ, быстрый и эффективный, я просто изменил «порядок по идентификатору» в строке 12 на «порядок по времени ввода», потому что идентификатор не всегда находится в порядке времени ввода. Но у меня есть еще одна проблема . В этой таблице у меня 50 разных символов, поэтому я заставил ее работать с первым символом, как я могу запустить все 50 символов (каждый символ должен начинаться с первого раза ввода)

4. Что вы подразумеваете под «символами»? Не могли бы вы отредактировать OP с примером?

5. что такое OP? Я имею в виду, что у меня есть еще один столбец с именем symbol, все данные, которые я привел здесь, — это один символ, поэтому предположим, что все строки содержат значение «AAA» для столбца Symol, и у меня есть другой набор дат с символом «BBB» для каждой строки и другой набор дат с символом»CCC» и так далее… итак, вопрос в том, как я получаю все результаты, которые дал мне ваш ответ, но теперь с символами для каждого набора

Ответ №2:

Вы можете использовать этот запрос:

 select *
from @t t1
where not exists (
    select 1
    from @t t2
    where t2.id < t1.id and t2.resultDate > t1.entryDate
)

1. Спасибо за ответ, но это не совсем хорошо. Это учитывает время последнего результата в таблице, даже если оно не выбрано.

2. Например: если время результата в одной строке равно 10:00:00, а в следующей строке время ввода равно 9:00:00, а время результата равно 11:00, предполагается пропустить эту строку и не использовать ее время результата, потому что время ввода предшествует последнему времени результата. теперь ваш ответ будет искать время следующего ввода после 11:00:00, а не после 10:00:00, как предполагается

Ответ №3:

Я думаю, что иногда курсор является ответом

 CREATE FUNCTION getSelected
(    
)
RETURNS @res TABLE 
(
    id int, EntryTime DATETIME, ResultTime DATETIME
)
AS
BEGIN
    declare @idC int;
    declare @ResultTimeC DATETIME;
    declare @EntryTimeC DATETIME;
    declare @lastNextDate DATETIME;

    DECLARE Iterator CURSOR LOCAL FAST_FORWARD 
    FOR SELECT id, EntryTime, ResultTime FROM dbo.tt1 order by EntryTime, id
    OPEN Iterator
    WHILE 1=1 BEGIN
        FETCH NEXT FROM Iterator INTO @idC, @EntryTimeC, @ResultTimeC
        IF @@FETCH_STATUS < 0 BREAK 

        if(@lastNextDate is null or @lastNextDate < @EntryTimeC) begin
            set @lastNextDate = @ResultTimeC;
            insert into @res (id, EntryTime, ResultTime) values (@idC, @EntryTimeC, @ResultTimeC);
        end;            
    END
    CLOSE Iterator
    DEALLOCATE Iterator;

    RETURN 
END

Редактировать

И версия с несколькими символами

 CREATE FUNCTION getSelected2
(    
)
RETURNS @res TABLE 
(
    id int, EntryTime DATETIME, ResultTime DATETIME, Symbol char(3)
)
AS
BEGIN
    declare @idC int;
    declare @ResultTimeC DATETIME;
    declare @EntryTimeC DATETIME;
    declare @SymbolC char(3);
    declare @lastNextDate DATETIME; 
    declare @lastSymbol char(3);

    DECLARE Iterator CURSOR FAST_FORWARD 
    FOR SELECT id, EntryTime, ResultTime, Symbol FROM dbo.tt2 order by Symbol, EntryTime, id
    OPEN Iterator
    WHILE 1=1 BEGIN
        FETCH NEXT FROM Iterator INTO @idC, @EntryTimeC, @ResultTimeC, @SymbolC
        IF @@FETCH_STATUS < 0 BREAK 

        if(@lastSymbol is null or @lastSymbol <> @SymbolC) begin
            set @lastSymbol = @SymbolC;
            set @lastNextDate = null;
        end;
        if(@lastNextDate is null or @lastNextDate < @EntryTimeC) begin
            set @lastNextDate = @ResultTimeC;
            insert into @res (id, EntryTime, ResultTime, Symbol) values (@idC, @EntryTimeC, @ResultTimeC, @SymbolC);
        end;            
    END
    CLOSE Iterator
    DEALLOCATE Iterator;

    RETURN 
END

1. Как я могу запустить эту функцию?

2. ВЫБЕРИТЕ * ИЗ getSelected()

3. Очень хорошо, идеально подходит для одного символа, но я добавляю еще одну проблему, которая у меня есть для нескольких символов, посмотрите на комментарий, где ответил ZLK. Как я могу сделать это для нескольких символов, каждый из которых начинается заново?

4. @TVC я внес правку. Рекурсивный CTE имеет некоторые преимущества по сравнению с этим, но это может быть быстрее. Если вы можете измерить это на реальных данных, это может быть интересно.

5. Я только что проверил это. Очень быстро … удивительно, 5 секунд для всех символов. Я получил те же результаты, что и рекурсивный способ. Если да, то каковы преимущества рекурсивного по сравнению с этим, как вы сказали?