我们的商业用户想要查询以星期为单位的聚集事实数据，他们把一星期定义为以星期天开始、星期六结束，不考虑年份变化。举个例子，企业部门可能会想要查询在过去的四个星期里每个星期分别招聘了多少名员工。虽然SQL Server的DATEPART函数可以赋予一年中任何一天其所在的星期序号，但是当年份变化之后，星期序号又再次从1开始。有没有什么简便的方法，可以做到确定某一个星期的序号，然后通过加减星期数目来获得所需的星期?
专家解答：
这个问题可能有许多可行的解决方案，不过脑子里第一个想到的方法是向日期维度里添加一列累计星期序号。把该列设置为连续号码来代表每个星期;即在星期天到星期六范围内的每一天都被赋予同一个号码，随后的每一个星期天则累加一个号码。我们还要创建一个函数来返回每一个查询日期的星期序号。在确定了某一个日期的星期序号之后，我们可以在WHERE从句里使用一个简单的BETWEEN来筛选出某一个或多个星期内的数据。
现在让我们来看看如何添加累计星期序号列的各个步骤：
1. 创建一个简单的日期维度：
| CREATE TABLE dbo.dim_Date ( wk_Date INT IDENTITY NOT NULL, FullDate DATETIME NOT NULL, WeekDayName VARCHAR(9) NOT NULL ) GO |
2. 赋予该日期维度某一范围内的日期：
| DECLARE @StartDate DATETIME DECLARE @EndDate DATETIME DECLARE @CurDate DATETIME SET @StartDate = '2007-01-01' SET @EndDate = '2008-12-31' SET @CurDate = @StartDate WHILE @CurDate <= @EndDate BEGIN INSERT INTO dbo.dim_Date (FullDate, WeekDayName) VALUES (@CurDate, DATENAME(weekday, @CurDate)) SET @CurDate = DATEADD(DAY, 1, @CurDate) END GO |
现在我们可以向日期维度里添加CumulativeWeek列并对其赋值。我们要在日期维度里的第一个星期赋予值1，然后从这个星期开始向后递增。记住每一个星期是以星期天开始的。我们找到日期维度里的第一个星期天的日期，然后使用一个常用的表表达来计算每一个日期与第一个星期天之间的日数，最后把该日数除以7再加1获得该日的星期序号。
3. 把CumulativeWeek列添加到日期维度中：
| ALTER TABLE dbo.dim_Date ADD CumulativeWeek INT NOT NULL CONSTRAINT DF_dim_Date_CumulativeWeek DEFAULT 0 WITH VALUES; GO |
4. 在日期维度里给CumulativeWeek列赋值：
| DECLARE @Week1Date DATETIME SELECT @Week1Date = MIN(FullDate) FROM dim_Date WHERE WeekDayName = 'Sunday' ; WITH cte_DayCounter AS ( SELECT wk_Date, DATEDIFF(dd, @Week1Date, FullDate) Days FROM dbo.dim_Date ) UPDATE dim SET CumulativeWeek = CASE WHEN c.Days >= 0 THEN (c.Days / 7) + 1 ELSE 0 END FROM dbo.dim_Date dim JOIN cte_DayCounter c ON c.wk_Date = dim.wk_Date GO |
我们的下一个任务就是要创建一个函数可以返回任意一天的CumulativeWeek值。这个函数在接受DATETIME参数后，截去时间部分，加入日期维度来获得CumulativeWeek值，并返回该星期。
5.创建一个返回任意日期的CumulativeWeek值的函数：
| CREATE FUNCTION dbo.udf_GetCumulativeWeekForDate( @Date DATETIME) RETURNS INTEGER AS BEGIN DECLARE @Week INTEGER SELECT @Week = ISNULL(CumulativeWeek, 0) FROM dbo.dim_Date WHERE FullDate = CONVERT(DATETIME,CONVERT(VARCHAR(10),@Date,120)) RETURN @Week END |
6. 输入几个日期参数来测试函数的运作
| SELECT
'2007-12-29' AS Date ,DATENAME(weekday, '2007-12-29') AS WeekDay ,dbo.udf_GetCumulativeWeekForDate('2007-12-29') AS CumulativeWeek UNION ALL SELECT '2007-12-30' ,DATENAME(weekday, '2007-12-30') ,dbo.udf_GetCumulativeWeekForDate('2007-12-30') UNION ALL SELECT '2008-01-01' ,DATENAME(weekday, '2008-01-01') ,dbo.udf_GetCumulativeWeekForDate('2008-01-01') UNION ALL SELECT '2008-01-06' ,DATENAME(weekday, '2008-01-06') ,dbo.udf_GetCumulativeWeekForDate('2008-01-06') |
回到问题中的例子，假设我们想要知道在过去的四个星期里每一个星期分别招聘了多少名员工。我们可以创建一个简单的包含聘用日期的事实表，聘用日期为连接到日期维度的整数。我们先向事实表里填充简单的样例信息，然后再创建查询。先获得当日(假设为2008年1月22日)的CumulativeWeek值，然后把CumulativeWeek的范围设置为CumulativeWeek – 4和CumulativeWeek – 1之间。
7.创建员工事实表：
| CREATE TABLE dbo.fact_EmployeeActivity (
wk_EmployeeActivity INT IDENTITY NOT NULL, fk_HireDate INT NOT NULL ) GO |
8. 向员工事实表里填充信息：
| INSERT INTO dbo.fact_EmployeeActivity(fk_HireDate)
SELECT 358 UNION ALL SELECT 359 UNION ALL SELECT 360 UNION ALL SELECT 361 UNION ALL SELECT 365 UNION ALL SELECT 366 UNION ALL SELECT 367 UNION ALL SELECT 372 UNION ALL SELECT 373 UNION ALL SELECT 383 GO |
9. 查询在过去四个星期里聘用的员工：
| DECLARE @BeginWeek INTEGER
DECLARE @EndWeek INTEGER DECLARE @CurWeek INTEGER SELECT @CurWeek = dbo.udf_GetCumulativeWeekForDate('2008-01-22') SET @BeginWeek = @CurWeek - 4 SET @EndWeek = @CurWeek - 1 SELECT CumulativeWeek ,COUNT(*) AS EmployeesHired FROM dbo.fact_EmployeeActivity f JOIN dbo.dim_Date d ON d.wk_Date = f.fk_HireDate WHERE d.CumulativeWeek BETWEEN @BeginWeek AND @EndWeek GROUP BY CumulativeWeek ORDER BY CumulativeWeek GO |
10. 查询结果如下：
评论