sql - 在 SQL Server 中按 30 天的任何范围(而不是按日期范围)对数据进行分组

Question

我得到了一张包含交易 list 的表格。
例如，假设它有 4 个字段:
ID、用户 ID、添加日期、金额

我想运行一个查询，检查是否有一段时间，在 30 天内，用户进行了 100 次或更多的交易

我看到了很多按月或按天分组的样本，但问题是，如果例如
用户在 20/4 日进行了 50 美元的交易，在 5/5 日他又进行了 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

好的，我使用了 3 个常用表表达式。第一个 (DailyTransactions) 将事务表减少为每个用户每天的单个事务(如果 DateAdded 只是一个日期，并且每个用户每天都有一个事务，则这不是必需的)。第二个和第三个(数字和 DayRange)有点像作弊 - 我想让数字 1-29 可供我使用(用于 DATEADD)。有多种方法可以创建永久或(在本例中)临时 Numbers 表。我只是选择了一个，然后在 DayRange 中，我将其筛选为我需要的数字。

现在我们有了这些可用的，我们编写主查询。我们正在查询 DailyTransactions 表中的行，但我们希望在同一表中查找 30 天内的后续行。这就是 DailyTransactions 的左连接正在做的事情。它正在查找后面的行，其中可能有 0、1 或更多。如果它不止一个，我们希望将所有这些值加在一起，这就是为什么我们需要在这个阶段做进一步的分组。最后，我们可以编写我们的 have 子句，以仅过滤到特定日期的金额 ( dt.Amount ) + 之后几天的金额总和 ( SUM(ot.Amount) ) 满足您设定的条件的结果。

我基于这样定义的表:

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