登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

梦想之鹰的天空

天高任鸟飞......放飞....心情..........放飞.....梦想

 
 
 

日志

 
 

如何在日期维度中处理年份变化而重复的星期序号  

2008-02-16 22:11:40|  分类: 数据库 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
问题:

  我们的商业用户想要查询以星期为单位的聚集事实数据,他们把一星期定义为以星期天开始、星期六结束,不考虑年份变化。举个例子,企业部门可能会想要查询在过去的四个星期里每个星期分别招聘了多少名员工。虽然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. 查询结果如下:

  评论这张
 
阅读(385)| 评论(0)

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018