Блог

Прошу прощения за ужасный заголовок, не совсем уверен, как объяснить это без примера:

Я пытаюсь вернуть столбец в запросе, который изменяется между 0 и 1 по мере изменения часа столбца datetime в результирующем наборе. Есть ли у кого-нибудь предложения о хорошем способе сделать это?? Или как это можно было бы сделать с помощью CTE

Я начал изучать использование CTE, но не продвинулся далеко и надеюсь, что есть более простой способ. Смотрите ниже очень простой пример того, чего я надеюсь достичь, с текущим выводом и тем выводом, который я хочу. ОБРАТИТЕ внимание, что я создал столбец [band], который выполняет то, что я хочу, при условии, что все значения времени указаны в один и тот же день. Конечно, это прерывается, когда даты охватывают более одного дня.

 declare @test as table(id int, dt datetime, comment varchar(50))

insert into @test values(1, '2011-01-01 07:00', 'one')
insert into @test values(2, '2011-01-01 07:30', 'two')
insert into @test values(3, '2011-01-02 07:50', 'three')
insert into @test values(4, '2011-01-03 08:00', 'four')
insert into @test values(5, '2011-01-03 08:50', 'five')
insert into @test values(6, '2011-01-03 09:00', 'six')
insert into @test values(7, '2011-01-03 10:00', 'seven');

select *, DATEPART(HOUR, dt) % 2 as [band]
from @test
  

ТЕКУЩИЙ ВЫВОД

 1   2011-01-01 07:00:00.000 one     1
2   2011-01-01 07:30:00.000 two     1
3   2011-01-02 07:50:00.000 three   1
4   2011-01-03 08:00:00.000 four    0
5   2011-01-03 08:50:00.000 five    0
6   2011-01-03 09:00:00.000 six     1
7   2011-01-03 10:00:00.000 seven   0
  

ТРЕБУЕМЫЙ ВЫВОД

 1   2011-01-01 07:00:00.000 one     1
2   2011-01-01 07:30:00.000 two     1
3   2011-01-02 07:50:00.000 three   0
4   2011-01-03 08:00:00.000 four    1
5   2011-01-03 08:50:00.000 five    1
6   2011-01-03 09:00:00.000 six     0
7   2011-01-03 10:00:00.000 seven   1
  

Обратите внимание здесь, что я хочу, чтобы столбец [band] чередовался по мере изменения datetime в строках на новые часы. Являются ли они следующим часом в тот же день или другим часом на следующий день.

Как насчет?

 SELECT *, 
    (dense_rank() over (order by Dateadd(hh,Datediff(hh,0,dt),0))) % 2 as [band]
FROM @test
  

У меня сейчас нет sql под рукой. Но

1. Dateadd / datediff удаляет минуты из часов
2. Затем dense_rank упорядочивает их как rownumber, но не нумерует дубликаты
3. затем %2 выполняет то, что вы делали в своем исходном запросе.

Насколько я понимаю вопрос:

Вы хотите сравнить значение datetime каждой записи с предыдущей записью, и если изменился час или любое значение, превышающее час, верните 1, в противном случае верните 0.

Для этого нет отличного способа — вы должны выполнить итерацию. В SQL итерация означает курсоры:

 DECLARE timeLoop CURSOR FOR
SELECT ID, DT 
FROM @test
ORDER BY ID

DECLARE @Id Int
DECLARE @Current DATETIME
DECLARE @Last DATETIME
DECLARE @Change bit
OPEN timeLoop

FETCH NEXT FROM timeLoop into @ID, @Current
SET @LAST = DATEADD(-1, DY, @Current)

WHILE @FETCH_STATUS = 0
BEGIN
    IF ABS(DATEDIFF(h, @Last, @Current) >= 1) SET @Change = 1
    ELSE SET @Change = 0

    SELECT @Id, @Dt, @Change
    SET @Last = @Current

    FETCH NEXT FROM timeLoop into @ID, @Current
END

CLOSE timeLoop
DEALLOCATE timeLoop
  

Приносим извинения за любые синтаксические ошибки выше — у меня нет sql server в этом поле и я не проверял точный синтаксис, но это поможет вам начать.

На самом деле я не думаю, что вам вообще нужен CTE

 SELECT a.ID, a.DT, ISNULL(b.DT, '1/1/1970')
    CASE     WHEN ABS(DATEDIFF(h, a.dt, ISNULL(b.DT, '1/1/1970')) < 1 THEN 1 
            DEFAULT 1 
    END AS Change
FROM @Test a
LEFT JOIN @Test b
    ON a.ID = b.ID-1
  

Присоедините таблицу к самой себе по ID к ID-1 (и послушайте крик администратора базы данных) при объединении по левой стороне, и первая строка присоединится к null, каждая последующая строка присоединится к предыдущей строке.