SQL Server非标准日期直方图

Question

我有用户登录数据和时间戳，想要做的是获得一个按年份分组的登录直方图，但是年份需要从一个任意日期开始计算。例如，我想要以下类型的信息：

1 May 2005 - 30 Apr 2006 | 525
1 May 2006 - 30 Apr 2007 | 673
1 May 2007 - 30 Apr 2008 | 892
1 May 2006 - 30 Apr 2009 | 1047

第一列的标签不重要，但日期范围是重要的。我知道可以通过以下方式将其按照年份分解：

SELECT YEAR([date]) AS [year], COUNT(*) AS cnt 
FROM logins
GROUP BY YEAR([date])
ORDER BY [year]

但这并不能给我想要的数据范围。怎样才能实现？

- cdeszaq

Answer 1

declare @baseDate datetime
set @baseDate = '1 May 2005'

SELECT
    datediff(year, @baseDate, [date]) AS YearBucket 
    ,COUNT(*) AS cnt 
FROM logins
GROUP BY datediff(year, @baseDate, [date])
ORDER BY datediff(year, @baseDate, [date])

抱歉，您是正确的。这里有一个修正版本（我应该一开始就使用测试表格...）

create table logins (date datetime, foo int)
insert logins values ('1 may 2005', 1)
insert logins values ('1 apr 2006', 2)
insert logins values ('1 may 2006', 3)

declare @baseDate datetime
set @baseDate = '1 May 2005'

SELECT
    datediff(day, @baseDate, [date]) / 365 AS YearBucket 
    ,COUNT(*) AS cnt 
FROM logins
GROUP BY datediff(day, @baseDate, [date]) / 365
ORDER BY datediff(day, @baseDate, [date]) / 365

如果您希望获得比天更细致的时间粒度，请更改datediff单位。

编辑#2 - 好的，这里有一个更加强大的解决方案，可以处理闰年：) 编辑#3 - 实际上，这并没有处理闰年，而是允许指定可变的时间间隔。采用dateadd(year, 1, @baseDate)来获取安全的闰年方法。

declare @baseDate datetime, @interval datetime
--@interval is expressed as time above 0 time (1/1/1900)
select @baseDate = '1 May 2005', @interval = '1901'

declare @timeRanges table (beginIntervalInclusive datetime, endIntervalExclusive datetime)
declare @i int
set @i = 1
while @i <= 10
begin
    insert @timeRanges values(@baseDate, @baseDate + @interval)
    set @baseDate = @baseDate + @interval
    set @i = @i + 1
end

SELECT
    tr.beginIntervalInclusive,
    tr.endIntervalExclusive,
    COUNT(*) AS cnt 
FROM logins join @timeRanges as tr
    on logins.date >= tr.beginIntervalInclusive
        and logins.date < tr.endIntervalExclusive
GROUP BY  tr.beginIntervalInclusive, tr.endIntervalExclusive
ORDER BY  tr.beginIntervalInclusive

- ahains

---

提供完全相同的结果，只是没有年份在前面。 - cdeszaq

@cdeszaq - 你是对的，抱歉。查询现在已经修复了，我应该一开始就使用一个测试表。 - ahains

我刚刚自己解决了这个问题...但是它会正确处理闰年吗？还是会引入偏移1的错误？ - cdeszaq

@cdeszaq - 好的，这里是一个更健壮的解决方案 :) - ahains

非常接近了...但对于2005年1月1日仍然有偏差...结束时间间隔被设置为12月31日，而不是2008年的下一年1月1日。 - cdeszaq

Answer 2

如果您能找到一种方法将日期范围定义在单独的表中，然后选择一个标签和两列日期，并从主查询中加入该表，类似于以下内容（根据您的表）：

Select Count(*) as NoLogons, DateRangeLabel
From logins a
inner join
(
Select
DateRangeLabel, StartDate, EndDate
From tblMyDates 
) b
on a.date between b.startdate and b.enddate
Group by DateRangeLabel

- u07ch