SQL Server中的任何30天范围内的数据（不是日期范围内的）

Question

我收到了一张包含事务列表的表。 的例子，可以说有4个领域： ID，用户名，DateAddedd，金额

在

我想在运行30天来检查，如果有一个时间的查询，即，用户进行的交易总数为100或更多

我看到很多月份或一天的分组样本，但问题是如果例如 用户在20/4和5/5上做了50美元的交易另一个50美元的交易，查询应该显示它。 （在30天内100美元以上）

Answer 1

我认为这应该工作（我假设的交易有个约会组成部分，用户可以在一天内有多个交易）：

;with DailyTransactions as (
    select UserID,DATEADD(day,DATEDIFF(day,0,DateAdded),0) as DateOnly,SUM(Amount) as Amount 
    from Transactions group by UserID,DATEADD(day,DATEDIFF(day,0,DateAdded),0) 
), Numbers as (
    select ROW_NUMBER() OVER (ORDER BY object_id) as n from sys.objects 
), DayRange as (
    select n from Numbers where n between 1 and 29 
) 
select 
    dt.UserID,dt.DateOnly as StartDate,MAX(ot.DateOnly) as EndDate, dt.Amount + COALESCE(SUM(ot.Amount),0) as TotalSpend 
from 
    DailyTransactions dt 
     cross join 
    DayRange dr 
     left join 
    DailyTransactions ot 
     on 
      dt.UserID = ot.UserID and 
      DATEADD(day,dr.n,dt.DateOnly) = ot.DateOnly 
group by dt.UserID,dt.DateOnly,dt.Amount 
having dt.Amount + COALESCE(SUM(ot.Amount),0) >= 100.00 

好的，我m使用3个公用表表达式。第一种（DailyTransactions）将交易表减少为每天每个用户一笔交易（如果DateAdded仅为日期，并且每个用户每天只有一笔交易，则不需要）。第二和第三（Numbers和DayRange）有点欺骗 - 我想让我的数字1-29（用于DATEADD）。有多种方法可以创建一个永久的或（在这种情况下）临时Numbers表。我只是选择了一个，然后在DayRange中，我将其过滤为我需要的数字。

既然我们有可用的东西，我们编写主查询。我们正在查询DailyTransactions表中的行，但我们希望在同一个表中查找30天内的后续行。这就是DailyTransactions的左边加入的内容。它找到后面的那些行，其中可能有0,1个或更多。如果它不止一个，我们希望将所有这些值加在一起，这就是为什么我们需要在这个阶段进行更多的分组。最后，我们可以编写我们的having条款，以仅过滤那些来自特定日期（dt.Amount）的金额+来自晚些日期的金额总和（SUM(ot.Amount)）符合您设定的标准的结果。

我此基础上这样定义的表：

create table Transactions (
    UserID int not null, 
    DateAdded datetime not null, 
    Amount decimal (38,2) 
) 

Answer 2

如果我正确理解你，你需要一个日历表，然后检查日期和日期+ 30之间的总和。所以如果你想检查一年的时间，你需要检查一些像365周期的东西。

这是一种做法。递归CTE创建日历，交叉应用计算CalDate和CalDate + 30之间每个CalDate的总和。

declare @T table(ID int, UserID int, DateAdded datetime, Amount money) 

insert into @T values(1, 1, getdate(), 50) 
insert into @T values(2, 1, getdate()-29, 60) 
insert into @T values(4, 2, getdate(), 40) 
insert into @T values(5, 2, getdate()-29, 50) 
insert into @T values(7, 3, getdate(), 70) 
insert into @T values(8, 3, getdate()-30, 80) 
insert into @T values(9, 4, getdate()+50, 50) 
insert into @T values(10,4, getdate()+51, 50) 

declare @FromDate datetime 
declare @ToDate datetime 

select 
    @FromDate = min(dateadd(d, datediff(d, 0, DateAdded), 0)), 
    @ToDate = max(dateadd(d, datediff(d, 0, DateAdded), 0)) 
from @T 

;with cal as 
(
    select @FromDate as CalDate 
    union all 
    select CalDate + 1 
    from cal 
    where CalDate < @ToDate 
) 
select S.UserID 
from cal as C 
    cross apply 
    (select 
     T.UserID, 
     sum(Amount) as Amount 
    from @T as T 
    where T.DateAdded between CalDate and CalDate + 30 
    group by T.UserID) as S 
where S.Amount >= 100 
group by S.UserID  
option (maxrecursion 0)