Блог

У меня есть список элементов, состояния которых находятся в пределах диапазонов дат, и некоторые подсчеты неверны (технически корректны, но неверны по замыслу).

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

Таблица выглядит примерно так:

 table_above
itemid | start                   | stop                    | state
1234   | 2000-01-01 00:00:00.000 | 2014-02-01 10:04:00.000 | 1
1234   | 2014-02-01 10:04:00.003 | NULL                    | 2
1111   | 2000-01-01 00:00:00.000 | NULL                    | 2
 

itemid 1234 был в state 1 течение примерно 14 лет, затем на Feb 1 него переключились state 2 , и stop = null средства, которые на данный момент, item 1234 есть state 2 .

item 1111 state 2 с тех пор 2000-01-01 00:00:00.000 и в state 2 настоящее время.

Был небольшой промежуток времени, когда БД вообще не регистрировала состояние itemid 1234 для следующих двух дат: '2014-02-01 10:04:00.001' и '2014-02-01 10:04:00.002'

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

Выполняемый мной запрос подсчитывает количество элементов в определенном состоянии в определенный момент времени:

 select
  count(1)
where
  start < '2014-02-01 12:00:00.000'
  and (stop >= '2014-02-01 12:00:00.000' or stop is null)
  and state = 2
from
  table_above
 

Это вернет 2 для table_above . Однако следующий запрос вернет 1 .

 select
  count(1)
where
  start < '2014-02-01 10:04:00.001'
  and (stop >= '2014-02-01 10:04:00.001' or stop is null)
  and state = 2
from
  table_above
 

Проблема в том, что на самом деле в течение этого крошечного периода времени элементы, связанные с этими itemid s, существуют, но они нигде не учитываются.

Запрос технически корректен, но мне нужно, чтобы он представлял намерение.

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

Кроме того, есть ли какой-либо способ выполнить запрос для печати всех пробелов для конкретного itemid ?

Наконец, я подозреваю, есть ли какое-либо перекрытие в диапазонах дат, где их не должно быть, и я хотел бы, чтобы запрос обнаружил, есть ли какое-либо перекрытие. В любой момент времени элемент может находиться только в одном состоянии одновременно, но я хотел бы проверить, есть ли некоторые точки, в которых элементы имеют несколько состояний, например, в примере ниже.

 table_above
itemid | start                   | stop                    | state
1234   | 2000-01-01 00:00:00.000 | 2014-02-01 10:04:00.000 | 1
1234   | 2014-02-01 10:03:59.999 | NULL                    | 2
 

Если бы я выполнил запрос выше дважды, с state = 2 или state = 1 для момента '2014-02-01 10:04:00.000' времени, оба возвращали бы количество 1 , но предполагается, что каждый элемент имеет только одно состояние в каждый момент времени, поэтому они не должны оба возвращаться 1

В другом ответе есть некоторая полезная информация, но вот еще кое-что, что также следует учитывать:

• Если вы используете datetime тип, то значения всегда будут округляться с шагом .000, .003 или .007, потому что это точность. Это в документации.
• Сравнение a datetime со строкой приведет к преобразованию этой строки в a datetime , поэтому на самом деле нет «крошечного периода» непредставления. Рассмотрим следующее:

   declare @dt0 datetime, @dt1 datetime, @dt2 datetime, @dt3 datetime
  select @dt0 = '2014-02-01 10:04:00.000',
         @dt1 = '2014-02-01 10:04:00.001',
         @dt2 = '2014-02-01 10:04:00.002',
         @dt3 = '2014-02-01 10:04:00.003'
  
  if @dt0 = @dt1 print 'True' else print 'False'
  if @dt2 = @dt3 print 'True' else print 'False'
   

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

• Ваша логика немного не подходит для сравнения диапазона. Обычно начало диапазона является включающим, а конец диапазона — исключительным. У вас все наоборот.

  Вместо:

   start < '2014-02-01 12:00:00.000'
  and (stop >= '2014-02-01 12:00:00.000' or stop is null)
   

  Это должно быть:

   start <= '2014-02-01 12:00:00.000'
  and (stop > '2014-02-01 12:00:00.000' or stop is null)
   

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

Вы могли бы попробовать что-то вроде следующего, чтобы зафиксировать, когда дата начала / окончания состояния находится между датой начала и датой окончания другого состояния:

 Select A.itemid
From table_above A
    Join table_above B
    On A.itemid = B.itemid
Where B.start Between A.start And A.stop
       Or B.stop Between A.start And A.stop
 

Кроме того, есть ли какой-либо способ выполнить запрос для печати всех пробелов для определенного itemid?

В SQL Server 2012 вы можете сделать это довольно легко с помощью аналитической функции lag(). (В более ранних версиях используйте подзапросы.)

 select *
from (select itemid, start, stop, state, 
             lag(stop) over (partition by itemid order by start) prev_stop
      from table_above) x
where start <> prev_stop;
 

Этот запрос выдаст вам все строки, в которых значение для «start» отличается от значения для предыдущего «stop». Вы можете добавить «itemid» в предложение WHERE, если вам нужно проверить наличие одного itemid, и вы можете вычесть «prev_stop» из «start», если хотите считать значения в пределах, скажем, 500 мсек равными.

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

Я думаю, это почти то же самое, что сказать: «Когда есть пробел, измените время остановки на равное следующему времени начала». Если я прав в этом, то я думаю, что этот запрос дает вам хорошую рабочую таблицу.

 select itemid,
       start,
       case when datediff(ms, next_start, stop) <= 500 then next_start
            else stop
       end as stop,
       state
from
  (select *, lead(start) over (partition by itemid order by start) next_start
  from table_above) x;
 

Я бы, вероятно, заключил это в инструкцию CREATE VIEW и основывал свои подсчеты на нем, а не на базовых таблицах.