本文介绍了T-SQL-跟踪一段时间内的事件的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一些与 ValidFrom ValidTo 日期相关的数据。简单来说就是:

I have some data which has ValidFrom and ValidTo dates associated with it. In simple terms:

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

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

There is a non-clustered index on this table which includes the two dates as key values.

我还有一个日期维度表,该表涵盖了从 1900 到 2999 。

I also have a Date dimension table which covers every date from 1900 to 2999.

我试图弄清楚如何从日期维度表中选择日期范围(比如说 2016-01-01 2016-12-31 ),然后确定每个日期有多少个有效会员那个日期。

I'm trying to figure out how I can select a range of dates from the Date dimension table (let's say 2016-01-01 to 2016-12-31) and then identify, for each date, how many memberships were valid on that date.

下面的代码可以完成工作,但是性能并不理想,我想知道是否有人对如何解决这个问题有任何建议? / p>

The code below does the job but the performance isn't great and I was wondering whether anyone has any recommendations for a better way to go about this?

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

谢谢您的任何建议!

推荐答案

SQL中的逻辑大部分是正确的,对于SQL喜欢做的事情,它实施得很差。从已经完成的 Date 表开始,而不是对每行数据进行子选择,将逻辑更改为 join ,您在这里:

The logic in your SQL is mostly correct, you have just implemented it poorly for how SQL likes to do things. Starting with your Dates table as you have done already, rather than doing a sub-select for each row of data, change your logic to a join and you are there:

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;






您可能要注意的是识别每天要计算哪些会员资格。例如,如果您的日期为 2006-05-09 ,那么该日期结束时是否应包括MembershipID 0001


What you may want to be careful of is identifying which memberships are to be counted on each day. For example, if your date is 2006-05-09 should MembershipID 0001 be included as it ends that day?

从本质上讲,问题是,您要计算整天在任何时间点上处于活动状态的会员资格的数量,还是仅对那些处于活动状态的会员资格进行计数在某个特定时间,例如说一天的开始或结束?

The question is essentially, are you counting the number of Memberships that were active at any point during the entire day, or just those that were active at a particular time, say the start or the end of the day?

然后对您的 ValidFromDate 值。

这篇关于T-SQL-跟踪一段时间内的事件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-05 11:16
查看更多