复杂查询涉及搜索连续日期(按月)

Question

我有一个表，其中包含按月按帐户列表以及指示活动的字段。我想根据以下标准进行搜索以查找帐户何时"死亡"：

1. 该帐户在几个月的连续时间内都有一致的活动
2. 该帐户在最后一个月的活动高峰（Spike =所有以前的活动时间的平均值的200％或更多）
3. 活动之后的一个月和接下来的12个月都有0个活动

所以表看起来像这样：

ID | Date      | Activity
1  | 1/1/2010  | 2
2  | 1/1/2010  | 3.2
1  | 2/3/2010  | 3
2  | 2/3/2010  | 2.7
1  | 3/2/2010  | 8
2  | 3/2/2010  | 9
1  | 4/6/2010  | 0
2  | 4/6/2010  | 0
1  | 5/2/2010  | 0
2  | 5/2/2010  | 2

因此，在这种情况下，两个帐户1和2在1月至3月的几个月中都有活动，两个帐户在三月的活动中都表现出激增。两个帐户在四月有0个活动。帐户2在5月再次进行活动，但帐户1没有。因此，我的查询应返回帐户1，而不是帐户2。我想将其视为我的查询结果：

ID | Last Date
1  | 3/2/2010 

我意识到这是一个复杂的问题，我没想到有人会为我编写整个查询。我能想到的当前最佳方法是创建一系列子征服并加入它们，但我什至不知道子征服会是什么样。例如：如何寻找一个连续的行，以使一个ID的一个ID串联，其中活动为0（或所有非零？）。

如果SQL太简单地参与了我的秋季后卫，那就是使用Java使用蛮力搜索，我首先找到所有唯一的ID，然后在整个几个月内迭代每个唯一的ID，以确定是否以及何时ID"死了"。

再次：非常感谢朝着正确方向移动的任何帮助。

Answer 1

在Java中或在SQL中进行部分处理，并在Java中完成处理是一种很好的方法。

我不会解决如何定义尖峰。

我建议您从条件3开始。很容易找到最后一个非零值。那就是您要测试的峰值和峰值之前的数据。

。

SELECT out.*
FROM monthly_activity out
  LEFT OUTER JOIN monthly_activity comp
    ON out.ID = comp.ID AND out.Date < comp.Date AND comp.Activity <> 0
WHERE comp.Date IS NULL

还不错，但是如果这是因为记录是本月的最后一个，所以您不希望结果，所以，

SELECT out.*
FROM monthly_activity out
  INNER JOIN monthly_activity comp
    ON out.ID = comp.ID AND out.Date < comp.Date AND comp.Activity == 0
GROUP BY out.ID

Answer 2

可能不是世界上最有效的代码，但我认为这可以做到您的追求：

declare @t table (AccountId int, ActivityDate date, Activity float)
insert @t 
      select 1,   '2010-01-01', 2
union select 2,   '2010-01-01', 3.2
union select 1,   '2010-02-03', 3
union select 2,   '2010-02-03', 2.7
union select 1,   '2010-03-02', 8
union select 2,   '2010-03-02', 9
union select 1,   '2010-04-06', 0
union select 2,   '2010-04-06', 0
union select 1,   '2010-05-02', 0
union select 2,   '2010-05-02', 2

select AccountId, ActivityDate LastActivityDate --, Activity
from @t a
where 
--Part 2 --select only where the activity is a peak
Activity >= isnull
(
    (
        select 2 * avg(c.Activity)
        from @t c
        where c.AccountId = 1
        and c.ActivityDate >= isnull
        (
            (
                select max(d.ActivityDate)
                from @t d
                where d.AccountId = c.AccountId
                and d.ActivityDate < c.ActivityDate
                and d.Activity = 0  
            )
            ,
            (
                select min(e.ActivityDate)
                from @t e
                where e.AccountId = c.AccountId
            )
        )
        and c.ActivityDate < a.ActivityDate
    )
    , Activity + 1 --Part 1 (i.e. if no activity before today don't include the result)
)
--Part 3
and not exists --select only dates which have had no activity for the following 12 months on the same account (assumption: count no record as no activity / also ignore current date in this assumption)
(
    select 1
    from @t b
    where a.AccountId = b.AccountId
    and b.Activity > 0
    and b.ActivityDate between dateadd(DAY, 1, a.ActivityDate) and dateadd(YEAR, 1, a.ActivityDate)
)