SQL Server非标准日期基于直方图

I have user login data with timestamps and what I would like to do is get a histogram of logins by year, but with the year starting at an arbitrary date. For example, I want the following sort of information:

我有带时间戳的用户登录数据,我想要做的是获得按年份登录的直方图,但年份从任意日期开始。例如,我想要以下类型的信息:

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

The labels in the first column are not important, but the date ranges are. I know I can break it down by strait years with:

第一列中的标签不重要,但日期范围是。我知道我可以通过以下几年分解:

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

But that doesn't give me the data ranges I want. How can this be done?

但这并没有给我我想要的数据范围。如何才能做到这一点?

2 个解决方案

#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])

EDIT - apologies, you are correct. Here is a fixed version (I should have used a test table to start with...)

编辑 - 道歉,你是对的。这是一个固定版本(我应该使用一个测试表开始......)

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

Change the datediff units if you want more granularity than days.

如果您想要更多粒度而不是几天,请更改datediff单位。

EDIT #2 - ok, here is a more robust solution that handles leap years :) EDIT #3 - Actually this doesn't handle leap years, instead it allows for variable intervals of time to be specified. Go with dateadd(year, 1, @baseDate) for the leap year safe approach.

编辑#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

#2

If you can find a way to define your date ranges in a separate table then select out a label and two columns of dates and join on that from your main query something like this depending on your tables.

如果您可以在单独的表中找到一种定义日期范围的方法,那么选择一个标签和两列日期,并根据您的表格从主查询中加入这样的内容。

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

#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])