SQL在日期范围内按频率分组 [英] SQL group by frequency within a date range

问题描述

我需要编写一个存储过程，该存储过程接受开始日期，结束日期和频率（天，周，月，季，年），并根据这些参数输出结果集。显然，最简单的部分是按日期范围查询，但是如何按频率分组？

I have a requirement to write a stored procedure that accepts a start date, end date and a frequency (day, week, month, quarter, year) and outputs a result set based on those parameters. Obviously, the simple part is the query by date range, but how do you group by frequency?

因此，如果有一组原始数据像这样：

So if have a set of raw data like this:

Date            Count
---------------------
11/15/2011          6
12/16/2011          9
12/17/2011          2
12/18/2011          1
12/18/2011          4

然后我这样调用存储的过程：

And I call my stored proc like this:

sp_Report'1/1/2011'，'12 / 31/2011'，'week'

sp_Report '1/1/2011', '12/31/2011', 'week'

我期望这样的结果：

WeekOf          Count
---------------------
11/19/2011          6
12/17/2011         11
12/24/2011          5

这里有几个问题：

1）我如何确定一周结束的日期（周日结束于周日）？

1) How do I determine the date for the end of the week (week ending on Sunday)?

2）如何按WeekOf日期范围分组？

2) How do I group by that WeekOf date range?

推荐答案

以下脚本重新以统一的方式显示输出：它显示了期间的开始日期和结束日期以及该期间的总数。

The following script represents the output in a unified way: it shows period's start and end dates as well as the total count for the period.

这也确定了查找值的方式分组。基本上，您可以看到三种不同的模式：一种用于'day'频率，另一种用于和

That has also determined the ways of finding the values to group by. Basically, you can see three distinct patterns: one for the 'day' frequency , another one for 'week' and still another for all the other frequency types.

第一个最简单：PeriodStart和PeriodEnd都只是 Date

The first one is simplest: both PeriodStart and PeriodEnd are just Date.

对于几周来说，我使用的是一个众所周知的技巧，即每周的第一天是从给定日期减去一个等于1的值而得出的少于其工作日数。一周的结束时间也类似：我们只是在同一表达式中添加 6 。

For weeks, I'm using a quite well known trick, whereby the first day of week is derived from the given date by subtracting from it a value that is one less than its weekday number. The end of the week is found similarly: we are merely adding 6 to the same expression.

月，季度和年份按以下方式分组。零日期和给定日期之间对应单位的整数将加回零日期。这给了我们这个时期的开始。最终的发现非常相似，只是我们要添加一个大于差异的数字。这样就产生了下一个周期的开始，因此我们减去了一天，这给了我们正确的结束日期。

Months, quarters and years are grouped in the following manner. The integer number of corresponding units between the zero date and the given date is added back to the zero date. That gives us the beginning of the period. The end is found very similarly, only we are adding the number that is one greater than the difference. That produces the beginning of the next period, so we are then subtracting one day, which gives us the correct ending date.

SELECT
  PeriodStart,
  PeriodEnd,
  Count = SUM(Count)
FROM (
  SELECT
    PeriodStart = CASE @Frequency
      WHEN 'day'     THEN Date
      WHEN 'week'    THEN DATEADD(DAY, 1 - DATEPART(WEEKDAY, Date), Date)
      WHEN 'month'   THEN DATEADD(MONTH,   DATEDIFF(MONTH,   0, Date), 0)
      WHEN 'quarter' THEN DATEADD(QUARTER, DATEDIFF(QUARTER, 0, Date), 0)
      WHEN 'year'    THEN DATEADD(YEAR,    DATEDIFF(YEAR,    0, Date), 0)
    END,
    PeriodEnd   = CASE @Frequency
      WHEN 'day'     THEN Date
      WHEN 'week'    THEN DATEADD(DAY, 7 - DATEPART(WEEKDAY, Date), Date)
      WHEN 'month'   THEN DATEADD(DAY, -1, DATEADD(MONTH,   DATEDIFF(MONTH,   0, Date) + 1, 0))
      WHEN 'quarter' THEN DATEADD(DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, Date) + 1, 0))
      WHEN 'year'    THEN DATEADD(DAY, -1, DATEADD(YEAR,    DATEDIFF(YEAR,    0, Date) + 1, 0))
    END,
    Count
  FROM atable
  WHERE Date BETWEEN @DateStart AND @DateEnd
) s
GROUP BY
  PeriodStart,
  PeriodEnd

• EXEC spReport'1/1/2011' ， 2011年12月31日，天 ：

  PeriodStart PeriodEnd  Count
  ----------- ---------- -----
  2011-11-15  2011-11-15 6
  2011-12-16  2011-12-16 9
  2011-12-17  2011-12-17 2
  2011-12-18  2011-12-18 5
  




• EXEC spReport'1/1/2011'，'12 / 31/2011'，'week'：

  PeriodStart PeriodEnd  Count
  ----------- ---------- -----
  2011-11-13  2011-11-19 6
  2011-12-11  2011-12-17 11
  2011-12-18  2011-12-24 5
  




• EXEC spReport'1/1 / 2011， 12/31/2011，月 ：

  PeriodStart PeriodEnd  Count
  ----------- ---------- -----
  2011-11-01  2011-11-30 6
  2011-12-01  2011-12-31 16
  




• EXEC spReport'1/1/2011'，'12 / 31/2011'，'季度'：

  PeriodStart PeriodEnd  Count
  ----------- ---------- -----
  2011-10-01  2011-12-31 22
  




• EXEC spReport'1/1/2011'， '12 / 31/2011'，'year'：

  PeriodStart PeriodEnd  Count
  ----------- ---------- -----
  2011-01-01  2011-12-31 22
  




注意：来自 MSDN ：




在命名过程时避免使用 sp _ 前缀。 SQL Server使用此前缀来指定系统过程。如果存在具有相同名称的系统过程，则使用前缀可能导致应用程序代码中断。有关更多信息，请参见设计存储过程（数据库引擎）。。 p>


Avoid the use of the sp_ prefix when naming procedures. This prefix is used by SQL Server to designate system procedures. Using the prefix can cause application code to break if there is a system procedure with the same name. For more information, see Designing Stored Procedures (Database Engine).

这篇关于SQL在日期范围内按频率分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！