从表的不同记录中选择最小值

Question

假设我有一个查询，在这个查询中，我每天计数事件的数量：

**Date**        **NumberOfEvents**
2017-11-1       7
2017-11-2       11
2017-11-3       3
...
2017-11-8       24
2017-11-9       6
2017-11-10      10
2017-11-11      9
...
2017-11-22      22
2017-11-23      11
2017-11-24      14
2017-11-25      17
...      
2017-11-28      16
2017-11-29      21
2017-11-30      6
...

那么假设我定义了一个变量@StartingDay ='2017-11-3'

我想得到一个查询，其中包含@StartingDay之后4周内同一周+-1天的最小值，p.ex：

**Period**                   **DateWithMin**   **MinNumberOfEvents**
2017-11-09 To 2017-11-11     2017-11-9         6
2017-11-16 To 2017-11-18     2017-11-17        8
2017-11-23 To 2017-11-25     2017-11-23        11
2017-11-30 To 2017-12-02     2017-11-30        6

我相信我必须通过不同的周期来寻找最小值，但是我无法找到一个循环的方法。

Answer 1

另一种方法是使用递归的CTE生成From和To日期，将Row_Number()应用于结果以找到每个分组的Min，并只选择这些结果：

Declare @StartingDay    Date = '2017-11-03',
        @NumWeeks       Int = 4

;With Dates As
(
    Select  DateFrom    = DateAdd(Day, -1, DateAdd(Week, 1, @StartingDay)),
            DateTo      = DateAdd(Day, 1, DateAdd(Week, 1, @StartingDay))
    Union All
    Select  DateFrom    = DateAdd(Week, 1, DateFrom),
            DateTo      = DateAdd(Week, 1, DateTo)  
    From    Dates
    Where   DateTo < DateAdd(Day, 1, DateAdd(Week, @NumWeeks, @StartingDay))
), Results As
(
    Select  PeriodFrom      = D.DateFrom,
            PeriodTo        = D.DateTo,
            NumberOfEvents  = Y.NumberOfEvents,
            RN              = Row_Number() Over (Partition By D.DateFrom, D.DateTo 
                                                 Order By Y.NumberOfEvents),
            Date            = Y.Date
    From    YourTable   Y
    Join    Dates       D   On  Y.Date Between D.DateFrom And D.DateTo
)
Select  PeriodFrom, 
        PeriodTo, 
        DateWithMin         = Date, 
        MinNumberOfEvents   = NumberOfEvents
From    Results
Where   RN = 1

Answer 2

您可以使用模块化和日期算法来获取时间段和组：

select min(date), max(date), min(NumberOfEvents)
from t
where (datediff(day, @startingday, date) % 7) in (0, 1, 6) and
      date > dateadd(day, 1, @startingday) and
      date <= dateadd(day, 4 * 7 + 1, @startingday)
group by (datediff(day, @startingday, date) + 1) / 7;

获取最小事件的日期更麻烦。以下是一种方法：

select min(date), max(date), min(NumberOfEvents),
       max(case when seqnum = 1 then date end) as date_at_min
from (select t.*, v.grp,
             row_number() over (partition by grp order by numberofevents) as seqnum
      from t cross apply
           (values ((datediff(day, @startingday, date) + 1) / 7)) v(grp)
     ) t
where (datediff(day, @startingday, date) % 7) in (0, 1, 6) and
      date > dateadd(day, 1, @startingday) and
      date <= dateadd(day, 4 * 7 + 1, @startingday)
group by grp;