代码之家 › 专栏 › 技术社区 › cdeszaq Sudhir N

SQL Server非标准基于日期的柱状图

data-mining reporting sql-server sql

cdeszaq Sudhir N · 技术社区 · 16 年前

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

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]

但这并不能提供我想要的数据范围。怎么能做到?

2 回复 | 直到 14 年前

ahains 16 年前

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

Tom H zenazn 14 年前

如果您可以在单独的表中定义日期范围,那么请从主查询中选择一个标签和两列日期,然后根据表的不同,在此基础上进行联接。

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