sql - 检查更改历史记录中连续 y 个月的值是否大于 x

Question

我有成员(member)级别变化的表格。它具有所有成员的级别和发生变化的日期的历史变化。例如，我可以列出成员编号 5 的更改:

select * from memberlevelhistory where member = 5

结果:

member  changedate  level
5       2012-04-01  2
5       2012-03-01  3
5       2012-02-01  2
5       2011-02-01  6
5       2011-02-01  6
5       2010-03-15  6
5       2010-02-01  5
5       2010-01-01  5
5       2009-10-01  4
5       2009-08-27  2
5       2009-08-01  1

历史表中的最后一个条目是当前级别。

题:
如何列出所有在 3 个月或更长时间内级别高于或等于 3 的成员？

这是问题的简化版本。为了让它更有趣，我只需要在这 3 个月期间没有低于起始级别的成员。因此，如果成员以 4 级开始了 3 个月期间，并且在上个月仅是 3 级，则该成员将从列表中排除。

任何帮助，即使是简化的问题也非常感谢。

扩大的视野:

我还需要这段时间 >=3 个月的水平 >=3 发生在过去 6 个月的窗口内。

Answer 1

这可能是 not exists 的情况.如果在接下来的三个月内没有另一个级别低于当前所选记录的条目，则该条目有效。这解决了这两个要求。在级别合适但跨度未知的情况下，每个成员的最后一个条目可能存在问题。我已决定删除这些记录，但您可能有其他想法。

Sql Fiddle with example is here .

select distinct mlh.member
  from memberlevelhistory mlh
 where mlh.level >= 3
   and not exists
       (
         select null
           from memberlevelhistory mlh2
          where mlh2.member = mlh.member
            and mlh2.changedate >= mlh.changedate
            and mlh2.changedate < dateadd(month, 3, mlh.changedate)
            and mlh2.level < mlh.level
       )
    -- The last entry might have appropriate level
    -- But we cannot tell how long it lasted,
    -- So we are going to remove it.
   and exists
       (
         select null
           from memberlevelhistory mlh3
          where mlh3.member = mlh.member
            and mlh3.changedate > mlh.changedate
       )

编辑:

我已经将 not exists() 重写为 left join 并添加了“最后一个条目持续到今天”标准。

Sql Fiddle with example is here.

select distinct mlh.member
  from memberlevelhistory mlh
  left join memberlevelhistory mlh2
    on mlh2.member = mlh.member
   and mlh2.changedate >= mlh.changedate
   and mlh2.changedate < dateadd(month, 3, mlh.changedate)
   and mlh2.level < mlh.level
 where mlh.level >= 3
   and mlh2.member is null
   and datediff(month, mlh.changedate, getdate()) >= 3

查询重写:

; with ranges as 
(
  select mlh.member, mlh.changedate StartRange, min(isnull(mlh2.changedate, getdate())) EndDate
    from memberlevelhistory mlh
    left join memberlevelhistory mlh2
      on mlh2.member = mlh.member
     and mlh2.changedate >= mlh.changedate
     and mlh2.level < mlh.level
   where mlh.level >= 3
  group by mlh.member, mlh.changedate
 having datediff (month, min(isnull(mlh2.changedate, getdate())), getdate()) <= 6
    and datediff (month, mlh.changedate, min(isnull(mlh2.changedate, getdate()))) >= 3
)
select distinct member
  from ranges

And Sql Fiddle is here .

我认为 100 和 101 应该包括在内，因为两者都运行了 3 个月，这是在 3 月，也就是在这一刻之前的 6 个月。

我所做的是在某人运行良好时生成范围，然后测试该范围的持续时间为 3 个月或更长时间，并在过去 6 个月内结束日期。

更新 :如果我最后做对了，您需要在过去六个月中持续三个月。计算可能会截断更改为当前日期 - 六个月。使用它作为起点，并找到一个范围的终点作为第一个 mlh将较低级别和较高日期作为终点有足够的信息来计算持续时间。

; with ranges as 
(
  select mlh.member, 
  -- If good range starts more than six months before today
  -- truncate it to today - 6 months
         case when datediff (month, mlh.changedate, getdate()) > 6
              then dateadd(month, -6, getdate())
              else mlh.changedate 
              end StartRange,
  -- First bad mlh after current changedate
         min(isnull(mlh2.changedate, getdate())) EndRange
    from memberlevelhistory mlh
    left join memberlevelhistory mlh2
      on mlh2.member = mlh.member
     and mlh2.changedate >= mlh.changedate
     and mlh2.level < mlh.level
   where mlh.level >= 3
  group by mlh.member, mlh.changedate
  -- As above, limit good range to max six months before today
  -- And only get those lasting at least three months
 having datediff (month, case when datediff(month, mlh.changedate, getdate()) > 6
                              then dateadd(month, -6, getdate())
                              else mlh.changedate 
                          end, 
                         min(isnull(mlh2.changedate, getdate()))) >= 3
)
select distinct member
  from ranges

Sql Fiddle with example is here .