设计日历表

介绍 (Introduction)

There is a common need in reporting to aggregate or return data that is crunched based on date attributes. These may include weekdays, holidays, quarters, or time of year. While any of this information can be calculated on the fly, a calendar table can save time, improve performance, and increase the consistency of data returned by our important reporting processes.

在报告中普遍需要聚合或返回基于日期属性处理的数据。 这些可能包括工作日，节假日，季度或一年中的时间。 尽管可以即时计算任何此类信息，但日历表可以节省时间，提高性能并提高我们重要报告流程返回的数据的一致性。

什么是日历表，为什么有用？ (What is a Calendar Table and Why is it Useful?)

A calendar table is a permanent table containing a list of dates and various components of those dates. These may be the result of DATEPART operations, time of year, holiday analysis, or any other creative operations we can think of.

日历表是一个永久性表，其中包含日期列表以及这些日期的各个组成部分。 这些可能是DATEPART操作，一年中的时间，假期分析或我们可以想到的任何其他创意操作的结果。

The primary key of this table will always be the date, or some easy-to-use representation of that date. Each subsequent column will be an attribute of that date, where the types and size of those columns can vary greatly. At first glance, it may seem that a table such as this would be superfluous, and that this data is easy to generate, but oftentimes as our reporting needs become complex, so does the creation, maintenance, and usage of this data.

该表的主键将始终是日期或该日期的某些易于使用的表示形式。 随后的每一列都是该日期的属性，这些列的类型和大小可以有很大的不同。 乍一看，这样的表似乎是多余的，并且易于生成此数据，但是通常由于我们的报告需求变得复杂，因此创建，维护和使用此数据也是如此。

As a result, storing calendar data in a permanent location can be an easy solution. Here are some reasons why this data is useful and why storing it in a dedicated table can be a great decision:

因此，将日历数据存储在永久位置可能是一个简单的解决方案。 以下是一些有用的理由，以及为什么将这些数据存储在专用表中是一个很好的决定的原因：

• Data is relatively easy to generate and requires little maintenance once created.
  数据相对容易生成，创建后几乎不需要维护。
• Calendar data can be used to service any reports that require it, removing the need to recreate it in each report.
  日历数据可用于为需要它的任何报告提供服务，而无需在每个报告中重新创建它。
• We can implement a large number of calendar metrics, and can easily add more as needed.
  我们可以实现大量的日历指标，并且可以根据需要轻松添加更多指标。
• Calendar data is tiny in terms of space used. Performance against this data is generally quite fast.
  日历数据占用的空间很小。 通常，针对此数据的性能非常快。
• Complex reports can be simplified by removing commonly used DATEPART and DATEADD computations.
  通过删除常用的DATEPART和DATEADD计算，可以简化复杂的报告。
• Important business logic, such as holidays, can be centralized and maintained in a single location.
  重要的业务逻辑（例如假期）可以集中并维护在一个位置。
• Maintaining calendar data in a single table ensures we do not encounter inconsistencies between different reports, reporting systems, or applications that need it.
  在单个表中维护日历数据可确保我们不会在不同的报表，报表系统或需要它的应用程序之间遇到不一致的情况。

There are many different reasons why a calendar table can be useful—this article is our opportunity to create one from scratch, populate it with data, analyze it, and put it to good use!

日历表之所以有用，有很多不同的原因-本文是我们从零开始创建一个日历表，使用数据填充，对其进行分析并加以利用的机会！

实施日历表 (Implementing a Calendar Table)

Our first step is to identify and define metrics that we want to collect. This is where we should look at our reporting needs and determine what sorts of date-related calculations we perform on a regular basis. For our examples here, we will include 33 different metrics (plus the calendar date itself), though you are free to add more as needed. Once introduced, we’ll walk through how to populate this data, and then how to use it.

我们的第一步是识别并定义我们要收集的指标。 在这里，我们应该查看报告需求，并确定我们定期执行哪种与日期相关的计算。 对于此处的示例，我们将包括33种不同的指标（加上日历日期本身），不过您可以根据需要随意添加更多指标。 引入后，我们将逐步介绍如何填充此数据，以及如何使用它。

日期零件 (Date Parts)

The simplest metrics are basic date components, including:

最简单的指标是基本日期成分，包括：

• Calendar Month: The numeric representation of the month, a number from 1-12.
• 日历月份 ： 月份的数字表示形式，从1到12的数字。
• Calendar Day: The numeric representation of the calendar day, a number from 1-31. The maximum value depends on the month and on whether it is a leap year.
• 日历日 ： 日历日的数字表示形式，是1到31之间的数字。 最大值取决于月份以及是否为leap年。
• Calendar Year: The numeric representation of the year, such as 1979 or 2017.
• 日历年份 ： 年份的数字表示形式，例如1979或2017。
• Calendar Quarter: The numeric representation of the quarter, a number from 1-4.
• 日历季度 ： 季度的数字表示形式，为1-4的数字。
• Day Name: The common name for the day of the week, such as Tuesday or Saturday. 日期名称 ：星期几的通用名称，例如星期二或星期六。
• Day of Week: The numeric representation of the day of the week, a number from 1(Sunday) through 7 (Saturday). In some countries the number 1 is used to represent Monday, though here we will use Sunday for this calculation. 星期几 ：一周中每天的数字表示，一些从1（星期日）至7日（星期六）。 在某些国家/地区，数字1用于表示星期一，尽管此处我们将使用星期日进行计算。
• Month Name: The common name for the month, such as February or October.
• 月份名称 ： 月份的通用名称，例如2月或10月。

These are all bits and pieces of the date itself and are useful whenever we are looking to find out metrics on specific days of the week, fiscal quarters, or other date parts.

这些都是日期本身的所有细节，每当我们要查找一周中特定日期，财政季度或其他日期部分的指标时，这些功能就很有用。

相对时间点 (Relative Points in Time)

Knowing when a date is, with respect to other calendar metrics, can be very handy in understanding how business changes over time. The following metrics allow you to determine in what part of the week, month, or year a date occurs:

相对于其他日历指标而言，了解日期的时间对于理解业务随时间的变化非常方便。 以下度量标准使您可以确定日期在一周，一月或一年中的哪个部分发生：

• Day of Week in Month: The occurrence of a day of week within the current month. Ie: The third Thursday of the current month. 每月的星期几：在当前月份中星期几的发生。 即：本月的第三个星期四。
• Day of Week in Year: The occurrence of a day of week within the current year. Ie: The seventeenth Monday of the current year. 一年中的星期几：当年中星期几的发生。 即：本年的第十七个星期一。
• Day of Week in Quarter: The occurrence of a day of week within the current quarter. Ie: The seventh Saturday of the current quarter. 季度中的星期几：当前季度中星期几的发生。 即：当前季度的第七个星期六。
• Day of Quarter: The day number within the current quarter. 季度的天：当前季度内的天数。
• Day of Year: The day number out of the current year. 每年的日子：当年的天数。
• Week of Month: The week number within the current month. With this calculation, the weeks count starting on the first of the month, regardless of the day of week.
• 每月的星期： 当月的星期数。 通过此计算，周数从每月的第一天开始