如何使用 sql 服务器计算订单的重叠订阅天数

Question

我有一个带有订单的订单表。我想计算每个用户在特定日期的订阅天数(以基于设置的方式优先(。

create table #orders (orderid int, userid int, subscriptiondays int, orderdate date)
insert into #orders
    select 1, 2, 10, '2011-01-01'
    union 
    select 2, 1, 10, '2011-01-10'
    union 
    select 3, 1, 10, '2011-01-15'
    union 
    select 4, 2, 10, '2011-01-15'
declare @currentdate date = '2011-01-20'
--userid 1 is expected to have 10 subscriptiondays left
(since there is 5 left when the seconrd order is placed)
--userid 2 is expected to have 5 subscriptionsdays left 

我

敢肯定以前已经这样做过，我只是不知道要搜索什么。很像一个运行总计？

因此，当我@currentdate设置为"2011-01-20"时，我想要这个结果：

userid      subscriptiondays
1           10
2           5

当我将@currentdate设置为"2011-01-25"时

userid      subscriptiondays
1           5
2           0

当我@currentdate设置为"2011-01-11"时

userid      subscriptiondays
1           9
2           0

谢谢！

Answer 1

我认为您需要使用递归公用表表达式。

编辑：我还在下面添加了一个过程实现，而不是使用递归公用表表达式。 我建议使用该过程方法，因为我认为我包含的递归 CTE 查询可能无法处理许多数据方案。

下面的查询为你提供的方案提供了正确的答案，但你可能想要想出一些其他复杂的方案，看看是否有任何错误。

例如，我有一种感觉，如果您有多个先前的订单与后来的订单重叠，则此查询可能会崩溃。

with CurrentOrders (UserId, SubscriptionDays, StartDate, EndDate) as
(
    select
        userid,
        sum(subscriptiondays),
        min(orderdate),
        dateadd(day, sum(subscriptiondays), min(orderdate))
    from #orders
    where
        #orders.orderdate <= @currentdate
        -- start with the latest order(s)
        and not exists (
            select 1
            from #orders o2
        where
            o2.userid = #orders.userid
            and o2.orderdate <= @currentdate
            and o2.orderdate > #orders.orderdate
        )
    group by
        userid
    union all
    select
        #orders.userid,
        #orders.subscriptiondays,
        #orders.orderdate,
        dateadd(day, #orders.subscriptiondays, #orders.orderdate)
    from #orders
    -- join any overlapping orders
    inner join CurrentOrders on
        #orders.userid = CurrentOrders.UserId
        and #orders.orderdate < CurrentOrders.StartDate
        and dateadd(day, #orders.subscriptiondays, #orders.orderdate) > CurrentOrders.StartDate
)
select
    UserId,
    sum(SubscriptionDays) as TotalSubscriptionDays,
    min(StartDate),
    sum(SubscriptionDays) - datediff(day, min(StartDate), @currentdate) as RemainingSubscriptionDays
from CurrentOrders
group by
    UserId
;

Philip 提到了对公共表表达式的递归限制的担忧。 下面是使用表变量和 while 循环的过程替代方案，我相信它完成了同样的事情。

虽然我已经验证了这个替代代码确实有效，至少对于提供的示例数据，我很高兴听到任何人对这种方法的评论。 好主意？ 坏主意？ 有什么需要注意的吗？

declare @ModifiedRows int
declare @CurrentOrders table
(
    UserId int not null,
    SubscriptionDays int not null,
    StartDate date not null,
    EndDate date not null
)
insert into @CurrentOrders
select
    userid,
    sum(subscriptiondays),
    min(orderdate),
    min(dateadd(day, subscriptiondays, orderdate))
from #orders
where
    #orders.orderdate <= @currentdate
    -- start with the latest order(s)
    and not exists (
        select 1
        from #orders o2
        where
            o2.userid = #orders.userid
            and o2.orderdate <= @currentdate
            -- there does not exist any other order that surpasses it
            and dateadd(day, o2.subscriptiondays, o2.orderdate) > dateadd(day, #orders.subscriptiondays, #orders.orderdate)
    )
group by
    userid
set @ModifiedRows = @@ROWCOUNT

-- perform an extra update here in case there are any additional orders that were made after the start date but before the specified @currentdate
update co set
    co.SubscriptionDays = co.SubscriptionDays + #orders.subscriptiondays
from @CurrentOrders co
inner join #orders on
    #orders.userid = co.UserId
    and #orders.orderdate <= @currentdate
    and #orders.orderdate >= co.StartDate
    and dateadd(day, #orders.subscriptiondays, #orders.orderdate) < co.EndDate

-- Keep attempting to update rows as long as rows were updated on the previous attempt
while(@ModifiedRows > 0)
begin
    update co set
        SubscriptionDays = co.SubscriptionDays + overlap.subscriptiondays,
        StartDate = overlap.orderdate
    from @CurrentOrders co
    -- join any overlapping orders
    inner join (
        select
            #orders.userid,
            sum(#orders.subscriptiondays) as subscriptiondays,
            min(orderdate) as orderdate
        from #orders
        inner join @CurrentOrders co2 on
            #orders.userid = co2.UserId
            and #orders.orderdate < co2.StartDate
            and dateadd(day, #orders.subscriptiondays, #orders.orderdate) > co2.StartDate
        group by
            #orders.userid
    ) overlap on
        overlap.userid = co.UserId
    set @ModifiedRows = @@ROWCOUNT
end
select
    UserId,
    sum(SubscriptionDays) as TotalSubscriptionDays,
    min(StartDate),
    sum(SubscriptionDays) - datediff(day, min(StartDate), @currentdate) as RemainingSubscriptionDays
from @CurrentOrders
group by
    UserId

EDIT2：我对上面的代码进行了一些调整，以解决各种特殊情况，例如，如果一个用户碰巧有两个订单在同一天结束。

例如，将设置数据更改为以下内容会导致原始代码出现问题，我现在已经更正了这些问题：

insert into #orders
    select 1, 2, 10, '2011-01-01'
    union 
    select 2, 1, 10, '2011-01-10'
    union 
    select 3, 1, 10, '2011-01-15'
    union 
    select 4, 2, 6, '2011-01-15'
    union 
    select 5, 2, 4, '2011-01-17'

编辑3：我做了一些额外的调整来解决其他特殊情况。 特别是，前面的代码遇到了以下设置数据的问题，我现在已经更正了这些问题：

insert into #orders
    select 1, 2, 10, '2011-01-01'
    union 
    select 2, 1, 6, '2011-01-10'
    union 
    select 3, 1, 10, '2011-01-15'
    union 
    select 4, 2, 10, '2011-01-15'
    union 
    select 5, 1, 4, '2011-01-12'

Answer 2

如果我的澄清评论/问题是正确的，那么你想使用 DATEDIFF：

DATEDIFF(dd, orderdate,  @currentdate)

Answer 3

我对这个问题的解释：

• 在第 X 天，客户购买了"跨度"的订阅天数(即 N 天有效(
• 跨度从购买当天开始，适用于 X 到第 X 天 + (N - 1(... 但见下文
• 如果客户在第一个跨度过期后购买第二个跨度(或所有现有跨度过期后的任何新跨度(，请重复该过程。(30 天前的一次 10 天购买对今天购买的第二个 purbox 没有影响。
• 如果客户在现有跨度仍然有效时购买跨度，则新跨度适用于day immediately after end of current span(s)到that date + (N – 1)

• 这是迭代的。如果客户在 1 月 1 日、1 月 2 日和 1 月 3 日购买 10 天跨度，则如下所示：

  截至1日：1月1日至

  1月10

  日截至2日：1月1日至1月10日，1月11日至1月20日

  (有效时间为1月1日至1月20日(

  截至 3 日：1

月 1 日至 1 月 10 日、1 月 11 日至 1 月 20 日、1 月 21 日至 1 月 30 日(有效时间为 1 月 1 日至 1 月 30 日(

如果这确实是问题所在，那么在 T-SQL 中解决这是一个可怕的问题。要确定给定购买的"有效跨度"，您必须按照购买顺序计算所有先前购买的有效跨度，因为该整体累积效应。对于 1 个用户和 3 行来说，这是一个微不足道的问题，但对于数千个用户和数十次购买来说，这不是一个微不足道的问题(大概，这就是您想要的(。

我会这样解决它：

• 将数据类型为 date 的列EffectiveDate添加到表中
• 构建一个一次性流程，逐个用户遍历每一行，逐个订单日期遍历每一行，并如上所述计算生效日期
• 修改用于插入数据以在创建新条目时计算生效日期的过程。这样，您只需引用该用户最近进行的购买。
• 解决有关删除(取消？(或更新(错误设置？(订单的后续问题

我可能是错的，但我看不到使用基于集合的策略来解决这个问题的任何方法。(递归 CTE 等可以工作，但它们只能递归到这么多级别，我们不知道这个问题的限制 - 更不用说你需要多久运行一次，或者它必须有多好。我会观察并投票给任何在没有递归的情况下解决这个问题的人！

当然，这只有在我对问题的理解是正确的情况下才适用。如果没有，请忽略。

Answer 4

事实上，我们需要计算订阅天数减去天数的总和，第一个订阅日期和@currentdate如下：

select userid, 
       sum(subsribtiondays)-
       DATEDIFF('dd', 
                (select min(orderdate) 
                 from #orders as a 
                 where a.userid=userid),  @currentdate)
from #orders
where orderdate <= @currentdata
group by userid