日期范围内的SQL分组频率

Question

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

如果有以下一组原始数据：

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

我这样调用存储过程：

sp_Report '2011年1月1日'，'2011年12月31日'，'周'

我期望得到以下结果：

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

这里有几个问题：

1）如何确定一周的结束日期（以星期日为结束日期）？

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

- Scott

Answer 1

以下脚本以统一的方式表示输出：它显示了周期的开始和结束日期以及该周期的总计数。这也确定了查找要分组的值的方法。基本上，您可以看到三种不同的模式：一种是“day”频率，另一种是“week”，还有其他所有频率类型的模式。第一个最简单：PeriodStart和PeriodEnd都是Date。对于周，我使用了一个非常著名的技巧，即从给定日期中减去比其工作日少一个的值来导出一周的第一天。同样地，我们只需将6添加到相同的表达式即可找到该周的结束。月份、季度和年份按以下方式分组。在零日期和给定日期之间的相应单位的整数数量被添加回零日期。这给我们带来了该时期的开始。结束也类似地找到，只是我们添加的数字比差异大1。这产生了下一个周期的开始，因此我们随后减去一天，这给我们正确的结束日期。

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', '12/31/2011', 'day':

  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', 'month':

  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', 'quarter':

  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用于指定系统存储过程。如果有一个同名的系统存储过程，使用该前缀可能会导致应用程序代码出现错误。更多信息请参见设计存储过程（数据库引擎）。

- Andriy M

Answer 2

 Create procedure MyProc
 @startDate DateTime,
 @endDate DateTime,
 @freq varChar(5)
 As

    If @freq = "day" 
        Select DateAdd(day, 0, datediff(day, 0, date)), Frequency,
        Sum(Count) 
        From Table
        Group By DateAdd(day, 0, datediff(day, 0, date))
    Else If @freq = "week" 
        Select DateAdd(week, 0, datediff(week, 0, date)), Frequency,
        Sum(Count) 
        From Table
        Group By DateAdd(week, 0, datediff(week, 0, date))
    Else If @freq = "Month" 
        Select DateAdd(Month, 0, datediff(Month, 0, date)), Frequency,
        Sum(Count) 
        From Table
        Group By DateAdd(Month, 0, datediff(Month, 0, date))
    Else If @freq = "Quarter" 
        Select DateAdd(Quarter, 0, datediff(Quarter, 0, date)), Frequency,
        Sum(Count) 
        From Table
        Group By DateAdd(Quarter, 0, datediff(Quarter, 0, date))
    Else If @freq = "Year" 
        Select DateAdd(Year, 0, datediff(Year, 0, date)), Frequency,
        Sum(Count) 
        From Table
        Group By DateAdd(Year, 0, datediff(Year, 0, date))

- Charles Bretana

Answer 3

类似这样的代码应该可以运行。

select date_column,
     sum(count)
from @table
where date_column between @start_date and @end_date
group by case @frequency
                    when 'week' then datepart(week,date_column )
                    when 'year' then datepart(year,date_column)
                    when 'quarter' then datepart(quarter,date_column)
                    when ...
         end;

- Gabe

---

只有当时间跨度不超过一年时，此方法才有效。否则，例如将会把2013年2月和2014年2月算作一起。 - Daniel P

Answer 4

select `date` as weekOf, sum(amt)
from myTable
where `date` between '2011-10-01' and '2012-01-01'
group by week(`date` )

- Darren Cato

---

糟糕，刚刚意识到这不是MySQL。 - Darren Cato