logo标签列表

时间点最近邻居算法

sqlsql-server-2008datetimenearest-neighborcensus
3
假设我有两个表格 (SQL Fiddle)。其中一个记录了各种时间戳的值，另一个指示要采样最近值的ID和日期时间。使用类似于Kevin Meade的NEAREST NEIGHBOR PREFERENCE LOW（但在SQL Server 2008中），我想找到最接近目标（人口普查）日期但不是在（人口普查）日期之后的指定ID的（非空值）。如果有匹配人口普查日期的行，则使用该行（除非它具有空值）。如果没有在人口普查日期之前的行，则找到最接近人口普查日期但不在其之前的行，并使用该行。

第一个表：

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');

这里是Meade先生的Oracle代码，用于在给定ID的一个表中实现类似功能：
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的解决方案here。

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
1个回答
0

你可以使用相关子查询来完成这个任务——在Oracle和SQL Server中都可以，因为这几乎是标准的SQL语句，除了top 1之外。

以下是查询语句：

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语句，这样会变得更加复杂。
不幸的是，这个解决方案没有给我期望的结果集。鉴于上述表格，我期望有8行记录（每个PAT_ENC_CSN_ID和CENSUS_TIME组合各1行），并且没有NULL的MEAS_VALUE。 - AvgJoe
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接