使用SQL Server 2012生成一天中多个小时的日历表 [英] Generate a calendar table with hours of the day using SQL Server 2012
问题描述
问题陈述：
我在消防部门工作，并且正在对数据进行统计分析。一个问题是生成一个日历年的每天每一小时的服务呼叫数量。我需要一张可以与一年中的每一天以及每一天的每一小时发生的火灾联系在一起的桌子。我希望的是以下内容（使用军事时间）
Problem Statement: I work for a fire department and I am working on statistical analysis of my data. One issue is to generate the number of calls for service for every hour of every day for a calendar year. I need a table that can be joined to the fire incidents that has every day of the year and every hour of each day. What I am hoping for is the following (using military time)
2017年1月1日00:00:00
1 January 2017 00:00:00
2017年1月1日00:00:00
1 January 2017 00:00:00
2017年1月1日01:00:00
1 January 2017 01:00:00
2017年1月1日02:00:00
1 January 2017 02:00:00
2017年1月1日03:00:00
1 January 2017 03:00:00
2017年1月1日04:00： 00
1 January 2017 04:00:00
2017年1月1日05:00:00
1 January 2017 05:00:00
2017年1月1日06:00:00
1 January 2017 06:00:00
2017年1月1日07:00:00
1 January 2017 07:00:00
2017年1月1日08:00:00
1 January 2017 08:00:00
等到年底为止
2017年12月31日21:00:00
31 December 2017 21:00:00
2017年12月31日22:00:00
31 December 2017 22:00:00
2017年12月31日23:00:00
31 December 2017 23:00:00
结束
此表将使我能够加入火灾事件表，并且能够统计地计算一天中每个小时以及每个小时的事件数量一年中的一天。计算表是必需的，因为火灾表中有空隙。例如; 1月1日的0100小时，0200小时和0300小时没有紧急电话打进来。因此，我无法使用火灾事件表进行计算，因为没有关于何时没有电话进来的数据。带有间隙的火灾事件表如下所示：
this table will allow me to join to the fire incidents table and I will be able to statistically calculate the number of incidents for each hour of the day and for each day of the year. A calculated table is necessary because the fire incidents table has gaps in it. For example; On january 1st at 0100 hours and 0200 hours and 0300 hours no emergency calls came in. Therefore I cannot make a calculation using the fire incidents table because there is no data for when no calls come in. The fire incidents table with gaps looks like:
TimeInterval，IncidentAddress
TimeInterval, IncidentAddress
2017年1月1日，榆树街123号00:00:00
1 january 2017 00:00:00, 123 Elm Street
2017年1月1日04:00:00，橡树街
1 January 2017 04:00:00, 456 Oak Street
2017年1月1日05：00：00,789 Maple Street
1 January 2017 05:00:00, 789 Maple Street
（请注意，在0100、0200和0300的时间没有消防电话。那是差距。）
因为数据中的差距为零应该是缺少泊松分布所需的计算平均值。平均值不正确。
(Notice there are no fire calls for the hours of 0100, 0200 and 0300. Those are the gaps.) Because there are gaps in the data where zeros should be the calculated averages necessary for a Poisson distribution are missing. The averages are incorrect.
所需的输出：
我的目标是要有一个带有小时表的日历以供加入我发生火灾，因此我的结果集返回了。这是查询的草稿，如果有匹配的值，则返回日历表的每一行和火灾事件表的每一行。
Desired Output: My goal is to have a calendar with an hours of day table to join to my fire incidents so my results set returns. Here is a rough draft of a query that returns every row from the calendar table and rows from the fire incidents table if there is matching value.
SELECT
TimeInterval
, COUNT(Incidents) AS [CountOfIncidents] /*this should probably be a COALESCE statement*/
FROM CalendarTable /*all rows from the calendar with hours and rows with data from FireIncidents*/
LEFT OUTER JOIN FireIncidents ON CalendarTable.timeInterval = FireIncidents.TimeInterval
GROUP BY TimeInterval
查询将返回我希望实现的结果：
Query would return what I am hoping to achieve:
TimeInterval，CountOfIncidents
TimeInterval, CountOfIncidents
2017年1月1日00:00:00，5
1 january 2017 00:00:00, 5
2017年1月1日01:00:00，0
1 January 2017 01:00:00, 0
2017年1月1日02：00：00,0
1 January 2017 02:00:00, 0
2017年1月1日03：00：00,0
1 January 2017 03:00:00, 0
2017年1月1日04:00:00，2
1 January 2017 04:00:00, 2
2017年1月1日05:00:00，1
1 January 2017 05:00:00, 1
（请注意0100、0200和0300的小时数为零误码率。这就是我要的！现在，我可以创建一个直方图，显示有多少小时零通话。或者，我可以计算出考虑到一天中零次致电的平均值。）
(Notice the hours of 0100, 0200 and 0300 have zero number of calls. This is what I want! Now I can create a histogram showing how many hours had zero calls. Or I can calculate an average value that takes into account zero calls for parts of the day.)
我尝试过的操作：
我已经尝试了以下方法，但是我无法弄清楚如何从中创建表以及如何将其制成成品，如您在下面的问题段落中所见。
What I have tried: I have tried the following but I cannot figure out how to create a table out of this and how to make it a finished product as you can see below in the Question paragraph.
DECLARE @DayOfYearNumber INT
DECLARE @HourNumber INT
SET @DayOfYearNumber = 1
SET @HourNumber = 0
PRINT 'Year' + ', ' + 'CalendarDayOfYear' + ', ' + 'HourOfDay'
WHILE @DayOfYearNumber < 366
BEGIN
SET @HourNumber = 0
WHILE @HourNumber < 24
BEGIN PRINT '2017' + ', ' + CONVERT(VARCHAR, @DayOfYearNumber) + ' ' + CONVERT(VARCHAR, @HourNumber)
SET @HourNumber = @HourNumber + 1
END
SET @DayOfYearNumber = @DayOfYearNumber + 1
END
问题：
如何在SQL Server 2012中生成一个日历表，该表具有一年中的每一天和每一天的每一小时。再次是我的例子
How do I generate a calendar table in SQL Server 2012 that will have every day of the year and every hour of each day. My example again
2017年1月1日00:00:00
1 January 2017 00:00:00
2017年1月1日01:00:00
1 January 2017 01:00:00
2017年1月1日02:00:00
1 January 2017 02:00:00
2017年1月1日03:00:00
1 January 2017 03:00:00
2017年1月1日04:00:00
1 January 2017 04:00:00
2017年1月1日05:00:00
1 January 2017 05:00:00
2017年1月1日06:00:00
1 January 2017 06:00:00
2017年1月1日07:00:00
1 January 2017 07:00:00
2017年1月1日08:00:00
1 January 2017 08:00:00
等到年底为止
2017年12月31日21:00:00
31 December 2017 21:00:00
2017年12月31日22:00:00
31 December 2017 22:00:00
2017年12月31日23:00： 00
31 December 2017 23:00:00
年终
推荐答案
一种简单的方法使用递归：
A simple method uses recursion:
with d as (
select cast('2017-01-01' as datetime) as dte
union all
select dateadd(hour, 1, dte)
from d
where dateadd(hour, 1, dte) < '2018-01-01'
)
select d.*
from d
option (maxrecursion 0);
尽管递归速度非常快，但是如果您需要多次递归，则可能需要考虑在周围有一个数字表或将其存储在临时或永久表中。
Although recursion is surprisingly fast, if you are going to be needing this multiple times, you might want to consider have a numbers table around or storing this in a temporary or permanent table.
这篇关于使用SQL Server 2012生成一天中多个小时的日历表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
