提取与最接近另一个日期的日期关联的值

kiki1113

我有2张桌子。第一个是个人（ID）列表和交易日期。第二个是客户历史记录表，其中包含一段时间内客户数据的快照。我有兴趣获取最接近但在交易日期之前的运行日期的成员身份标志。

*编辑：我尝试使用TOP 1，并且排名/分区...但是它们花费的时间太长了。我无权在表上创建索引。这是让我得到我所需要的查询：

SELECT  t1.*, t2.RunDate, t2.CST_ISMEMBER 
FROM table1 t1
INNER JOIN table2 t2 
   ON t1.ID=t2.ID
   AND t2.RunDate = 
            (SELECT MAX(t2b.RunDate) 
            FROM table2 t2b  
            WHERE t2b.ID=t2.ID AND t1.TransDate >= t2b.RunDate)

更多编辑*我发现对于表1中的大约9000个人，表2中的运行日期发生在交易日期之后。因此，现在我需要找到交易的关闭日期（之前或之后）。努力找到不涉及超过排名/分区或前1名的datediff解决方案，这给我带来了索引问题的困扰。我必须在5分钟后终止查询，以免被IT大吼大叫；-)这些是表2上可用的最大索引。

    TABLE 1 
ID   TransDate 
1    8/20/13
2    9/5/14

TABLE 2
ID   RunDate      IsMember
1    6/30/2010    Y
1    7/11/2012    N
1    7/30/2013    N
1    8/15/2013    Y
2    9/1/2014     Y
2    9/10/2014    N

所需结果：

ID  TransDate   IsMember
1   8/20/13     N
2   9/5/14      Y

解决此问题的最佳方法是什么？

提前致谢。

戈登·利诺夫

相关子查询是一种简单的方法。代码如下所示：

select t1.*,
       (select top (1) t2.ismember
        from table2 t2
        where t2.id = t1.id and t2.rundate < t1.transdate
        order by t2.rundate desc
       ) as ismember
from table1 t1;

为了提高性能，您需要一个索引table2(id, rundate, ismember)。

编辑：

您对此有糟糕的索引。这个历史记录表如何无法建立索引id非常奇怪。这是使用窗口函数的完全不同的方法。

with t as (
      select id, transdate as dte, null as ismember, 1 as which
      from table1
      union all
      select id, rundate, ismember, 2
      from table2
     )
select t.*
from (select t.*, lag(ismember) over (partition by id order by dte, which desc) as ismember
      from t
     ) t
where which = 1;

我怀疑所有lag()联合数据都将比尝试解决第一种方法快。但是，使用适当的索引，第一个会更快。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。