sql - T-SQL - 跟踪随时间发生的事件

Question

我有一些数据与 ValidFrom 和 ValidTo 日期相关联。简单来说:

MembershipId | ValidFromDate | ValidToDate
==========================================
0001         | 1997-01-01    | 2006-05-09
0002         | 1997-01-01    | 2017-05-12
0003         | 2005-06-02    | 2009-02-07

此表上有一个非聚集索引，其中包括两个日期作为键值。

我还有一个日期维度表，涵盖从 1900 到 2999 的每个日期。

我想弄清楚如何从日期维度表中选择日期范围(比如 2016-01-01 到 2016-12-31)，然后确定每个日期有多少成员(member)资格在该日期有效。

下面的代码可以完成这项工作，但性能不是很好，我想知道是否有人有更好的方法来解决这个问题？

SELECT 
   d.DateKey
  ,(SELECT COUNT(*) FROM Memberships AS m
    WHERE d.DateKey between m.ValidFromDateKey and m.ValidToDateKey
    ) AS MembershipCount

FROM       
   DIM.[Date] AS d

WHERE
   d.CalendarYear = 2016

提前感谢您的任何建议!

Answer 1

您的 SQL 中的逻辑大部分是正确的，您只是针对 SQL 喜欢做事的方式实现得很糟糕。从您已经完成的Dates 表开始，而不是对每一行数据进行子选择，将您的逻辑更改为join，您就在那里:

select d.DateKey
      ,count(m.MembershipID) as MembershipCount
from DIM.[Date] as d
    left join Memberships as m
        on(d.DateKey between m.ValidFromDateKey and m.ValidToDateKey)
where d.CalendarYear = 2016
group by d.DateKey
order by d.DateKey;

---

您可能需要注意的是确定每天要计算哪些成员(member)资格。例如，如果您的日期是 2006-05-09，那么 MembershipID 0001 是否应该包含在该日期结束时？

问题本质上是，您是在计算在一整天内任何时间处于事件状态的成员(member)数量，还是只计算在特定时间(例如开始或结束)处于事件状态的成员(member)数量当天？

然后为您的 ValidFromDate 值重复这个思考过程。