Ближайший сосед на момент времени

Скажем, у меня есть две таблицы (SQL Fiddle). Один из них содержит записанные значения с различными отметками времени, другой указывает идентификаторы и даты для выборки ближайших значений. Используя что-то похожее на NEAREST NEIGHBOR PREFERENCE LOW Кевина Мида (но в SQL Server 2008), я хочу чтобы найти (ненулевое значение) указанного идентификатора, ближайшего к целевой дате (переписи), но не позже даты (переписи). Если есть строка, соответствующая дате переписи, используйте ее (если она не имеет нулевого значения). Если до даты переписи нет строки, найдите строку, ближайшую к дате переписи, но не до нее, и используйте ее.

Первая таблица:

CREATE TABLE Recorded_Vent_Types
    ([PAT_ENC_CSN_ID] int, [RECORDED_TIME] datetime, [MEAS_VALUE] varchar(9));

INSERT INTO Recorded_Vent_Types
    ([PAT_ENC_CSN_ID], [RECORDED_TIME], [MEAS_VALUE])
VALUES
    (11117777,  '2013-06-08 19:36:00.000',  'SIMV/PRVC'),
    (11117777,  '2013-06-08 22:21:00.000',  'PRVC/AC'),
    (11117777,  '2013-06-09 00:10:00.000',  NULL),
    (11117777,  '2013-06-09 03:00:00.000',  'SIMV/PRVC'),
    (11117777,  '2013-06-09 23:59:00.000',  'SIMV/PRVC'),
    (11117777,  '2013-06-10 00:00:00.000',  'NAVA'),
    (11117777,  '2013-06-10 00:20:00.000',  'PS'),
    (11117777,  '2013-06-10 00:25:00.000',  NULL),
    (555999,    '2013-06-08 00:36:00.000',  NULL),
    (555999,    '2013-06-08 22:21:00.000',  'PRVC/AC'),
    (555999,    '2013-06-09 00:10:00.000',  'SIMV/PRVC'),
    (555999,    '2013-06-11 23:15:00.000',  'BIVENT'),
    (555999,    '2013-06-12 00:00:00.000',  NULL),
    (555999,    '2013-06-12 00:20:00.000',  'PS');

Вторая таблица:

CREATE TABLE Census
    ([PAT_ENC_CSN_ID] int, [CENSUS_TIME] datetime);

INSERT INTO Census
    ([PAT_ENC_CSN_ID], [CENSUS_TIME])
VALUES
    (11117777, '2013-06-08 00:00:00'),
    (11117777, '2013-06-09 00:00:00'),
    (11117777, '2013-06-10 00:00:00'),
    (11117777, '2013-06-11 00:00:00'),
    (555999, '2013-06-08 00:00:00'),
    (555999, '2013-06-09 00:00:00'),
    (555999, '2013-06-11 00:00:00'),
    (555999, '2013-06-12 00:00:00');

Вот код Oracle мистера Мида для чего-то похожего в одной таблице для данного идентификатора:

select *
from claim_history
where claim_id = 1
and status_date =
   (
    select min(status_date)
    from (
          select max(status_date) status_date
          from claim_history
          where claim_id = 1
          and status_date <= sysdate-3
          union all
          select min(status_date)
          from claim_history
          where claim_id = 1
          and status_date > sysdate-3
         )
   )
/

Мой желаемый набор результатов:

PAT_ENC_CSN_ID    CENSUS_TIME           RECORDED_TIME               MEAS_VALUE
555999      June, 08 2013 00:00:00+0000 June, 08 2013 22:21:00+0000 PRVC/AC
555999      June, 09 2013 00:00:00+0000 June, 08 2013 22:21:00+0000 PRVC/AC
555999      June, 11 2013 00:00:00+0000 June, 09 2013 00:10:00+0000 SIMV/PRVC
555999      June, 12 2013 00:00:00+0000 June, 11 2013 23:15:00+0000 BIVENT
11117777    June, 08 2013 00:00:00+0000 June, 08 2013 19:36:00+0000 SIMV/PRVC
11117777    June, 09 2013 00:00:00+0000 June, 08 2013 22:21:00+0000 PRVC/AC
11117777    June, 10 2013 00:00:00+0000 June, 10 2013 00:00:00+0000 NAVA
11117777    June, 11 2013 00:00:00+0000 June, 10 2013 00:20:00+0000 PS

@Gordon Linoff подал мне идею использовать абсолютные значения разницы дат между временем переписи и временем регистрации. Это заставило меня изменить решение @bobs здесь.

SELECT * FROM
    (
    SELECT rvt.PAT_ENC_CSN_ID, CENSUS_TIME, RECORDED_TIME, MEAS_VALUE, ABS(DATEDIFF(s, c.CENSUS_TIME, RECORDED_TIME)) diff,
        ROW_NUMBER() OVER (PARTITION BY rvt.PAT_ENC_CSN_ID, c.CENSUS_TIME ORDER BY ABS(DATEDIFF(s, c.CENSUS_TIME, RECORDED_TIME))) AS SEQUENCE
    FROM Recorded_Vent_Types rvt join Census c on rvt.PAT_ENC_CSN_ID=c.PAT_ENC_CSN_ID
    WHERE MEAS_VALUE IS NOT NULL
    ) as m
WHERE SEQUENCE = 1
ORDER BY PAT_ENC_CSN_ID,CENSUS_TIME
;

Но это возвращает (абсолютное) ближайшее зарегистрированное время без предпочтения зарегистрированного времени до времени переписи. Результат:

PAT_ENC_CSN_ID    CENSUS_TIME           RECORDED_TIME               MEAS_VALUE
555999      June, 08 2013 00:00:00+0000 June, 08 2013 22:21:00+0000 PRVC/AC
555999      June, 09 2013 00:00:00+0000 June, 09 2013 00:10:00+0000 SIMV/PRVC
555999      June, 11 2013 00:00:00+0000 June, 11 2013 23:15:00+0000 BIVENT
555999      June, 12 2013 00:00:00+0000 June, 12 2013 00:20:00+0000 PS
11117777    June, 08 2013 00:00:00+0000 June, 08 2013 19:36:00+0000 SIMV/PRVC
11117777    June, 09 2013 00:00:00+0000 June, 08 2013 22:21:00+0000 PRVC/AC
11117777    June, 10 2013 00:00:00+0000 June, 10 2013 00:00:00+0000 NAVA
11117777    June, 11 2013 00:00:00+0000 June, 10 2013 00:20:00+0000 PS

sql sql-server-2008 datetime nearest-neighbor census
person AvgJoe    schedule 10.06.2013    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Вы можете сделать это как коррелированный подзапрос - как в Oracle, так и в SQL Server, потому что это почти стандартный SQL, за исключением первого.

Вот запрос:

select *,
       (select top 1 PAT_ENC_CSN_ID
        from census c
        where c.census_time <= rvt.recorded_time
        order by (case when c.census_time <= rvt.recorded_time then 1 else 0
                  end) desc,
                 (case when c.census_time <= rvt.recorded_time then c.census_time
                  end) desc,
                 c.census_time asc
       ) as nearestVal
from Recorded_Vent_Types rvt

Подзапрос возвращает одну строку на основе order by, которая является ключевой для запроса. Он состоит из трех частей.

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

abs(c.census_time - rvt.recorded_time)

Потому что это логично. Увы, это не работает, потому что abs() не работает на datetime. И тогда мне пришлось бы использовать функцию datediff() или оператор case, и это начало бы выглядеть более сложным.

person Gordon Linoff    schedule 10.06.2013
comment
К сожалению, это решение не дает ожидаемого результата. Учитывая приведенные выше таблицы, я ожидаю 8 строк (по 1 для каждой комбинации PAT_ENC_CSN_ID и CENSUS_TIME) без NULL MEAS_VALUE. - person AvgJoe; 11.06.2013