SQL 如何按日期范围分组

Question

有人可以帮我弄清楚按日期范围进行分组吗？

现在我有类似这样的查询

    Select date, count(x)
    from data
    group by date

这返回的结果看起来像这样

    2011/1/1   10
    2011/1/2   5
    2011/1/3   8
    2011/1/4   3

等等...

但我想每2天计数一次，以便数据看起来像这样

    2011/1/1   15
    2011/1/3   11

有什么想法吗？

谢谢

Answer 1

您可以通过将日期转换为整数值并减少为偶数，将日期标准化为 2 组。一个简单的方法是 val / 2 * 2，因为第一个 / 2 将被截断任何小数位（只要 val 的类型 是一个整数！），* 2 会将其返回到原始值，但标准化为偶数除外。以下是使用 CTE 数据源对结果进行标准化和分组的示例：

;with Data as ( 
    select '1/1/2011' as [date], 1 as x union
    select '1/1/2011' as [date], 2 as x union
    select '1/1/2011' as [date], 3 as x union
    select '1/1/2011' as [date], 4 as x union
    select '1/1/2011' as [date], 5 as x union
    select '1/1/2011' as [date], 6 as x union
    select '1/1/2011' as [date], 7 as x union
    select '1/1/2011' as [date], 8 as x union
    select '1/1/2011' as [date], 9 as x union
    select '1/1/2011' as [date], 10 as x union
    select '1/2/2011' as [date], 11 as x union
    select '1/2/2011' as [date], 12 as x union
    select '1/2/2011' as [date], 13 as x union
    select '1/2/2011' as [date], 14 as x union
    select '1/2/2011' as [date], 15 as x union
    select '1/3/2011' as [date], 16 as x union
    select '1/3/2011' as [date], 17 as x union
    select '1/3/2011' as [date], 18 as x union
    select '1/3/2011' as [date], 19 as x union
    select '1/3/2011' as [date], 20 as x union
    select '1/3/2011' as [date], 21 as x union
    select '1/3/2011' as [date], 22 as x union
    select '1/3/2011' as [date], 23 as x union
    select '1/4/2011' as [date], 24 as x union
    select '1/4/2011' as [date], 25 as x union
    select '1/4/2011' as [date], 26 as x
)
Select
    cast(cast(cast(Date as datetime) as integer) / 2 * 2 as datetime) as date,
    count(x)
from data
group by cast(cast(Date as datetime) as integer) / 2 * 2

输出：

date                    (No column name)
2011-01-01 00:00:00.000 15
2011-01-03 00:00:00.000 11

Answer 2

Select floor((date - trunc(date,'MM')) / 2), count(x)
  from data
 group by floor((date - trunc(date,'MM')) / 2)