sql server—在sql中计算滚动聚合最有效的方法是什么？

bq3bfh9z 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(440)

我有一个数据集，其中包括一堆客户和他们“停留”的日期范围。例如：

| ClientID | DateStart | DateEnd |
+----------+-----------+---------+
| 1        | Jan 1     | Jan 31  | (datediff = 30)
| 1        | Apr 4     | May 4   | (datediff = 30)
| 2        | Jan 3     | Feb 27  | (datediff = 55)
| 3        | Jan 1     | Jan 7   | (datediff = 6)
| 3        | Jan 10    | Jan 17  | (datediff = 6)
| 3        | Jan 20    | Jan 27  | (datediff = 6)
| 3        | Feb 1     | Feb 7   | (datediff = 6)
| 3        | Feb 10    | Feb 17  | (datediff = 6)
| 3        | Feb 20    | Feb 27  | (datediff = 6)

我的最终目标是能够识别客户通过阈值的日期 N 过去的夜晚 X 时间。比如说 30 最近几天 90 天。我还需要知道他们什么时候通过门槛。用例：酒店住宿和vip状态。
在上面的例子中，客户1在1月31日通过了阈值（在过去90天中有30个晚上），直到4月2日仍然保持达到阈值（现在在过去90天中只有29个晚上），但是在5月4日再次通过了阈值。
客户端2在2月3日通过了阈值，并一直达到阈值，直到4月28日，在这一点上最早的日子是90多天前，他们到期。
客户3在2月17日左右通过了门槛
所以我想生成一个这样的表：

| ClientID | VIPStart  | VIPEnd  |
+----------+-----------+---------+
| 1        | Jan 31    | Apr 2   |
| 1        | May 4     | Jul 5   |
| 2        | Feb 3     | Apr 28  |
| 3        | Feb 17    | Apr 11  |
(Forgive me if the dates are slightly off, I'm doing this in my head)

理想情况下，我想生成一个视图，因为我需要经常引用它。
我想知道最有效的方法是什么？假设我有成千上万的客户和成百上千的客人。
到目前为止，我处理这个问题的方法是使用一个包含参数的sql语句：as of {?Date} ，谁有vip身份谁没有。我是通过计算 DATEADD(day,-90,{?Date}) ，然后排除超出范围的记录，然后截断 DateStart 越早越好 DateEnd 然后，计算 DATEDIFF(day,DateStart,DateEnd) 使用调整后的 DateStart 以及 DateEnd ，然后得到一个 SUM() 结果的 DATEDIFF() 对于每个客户 {?Date} . 很管用，但不好看。它给了我一个时间点快照；我想要历史。
生成一个日期表，然后对每个日期使用上面的方法似乎有点低效。
我考虑的另一个选择是将原始数据转换成一个分解表，每个记录对应一个晚上，这样我可以更容易地计算。这样地：

| ClientID | StayDate  |
+----------+-----------+
| 1        | Jan 1     |
| 1        | Jan 2     |
| 1        | Jan 3     |
| 1        | Jan 4     |
etc.

然后我可以添加一个列来计算过去90天中的天数，这样我就可以大致了解情况了。
但我不知道如何做到这一点。我有一个代码片段，可以这样做：

WITH DaysTally AS (
    SELECT MAX(DATEDIFF(day, DateStart, DateEnd)) - 1 AS Tally
    FROM Stays
UNION ALL
    SELECT Tally - 1 AS Expr1
    FROM DaysTally AS DaysTally_1
    WHERE  (Tally - 1 >= 0))

SELECT t.ClientID,
        DATEADD(day, c.Tally, t.DateStart) AS "StayDate"
    FROM Stays AS t 
    INNER JOIN DaysTally AS c ON 
        DATEDIFF(day, t.DateStart, t.DateEnd) - 1 >= c.Tally

OPTION (MAXRECURSION 0)

但我不能让它工作没有 MAXRECURSION 我认为你不能用 MAXRECURSION 现在我在闲逛。所以我要寻求的帮助是：追求目标最有效的方法是什么？如果你有一个代码示例，那也会很有帮助！谢谢。

sql window-functions sql-server Date gaps-and-islands

来源：https://stackoverflow.com/questions/62232976/whats-the-most-efficient-way-to-calculate-a-rolling-aggregate-in-sql

2条答案

按热度按时间

xjreopfe1#

可以通过在视图中创建计数表来消除递归。方法如下：
对于每个时段，生成从时段前90天到时段后90天的日期。这些都是该时期可能影响的“候选人日”。
对于每一行，添加一个标志，表明它是否在期间内（与之前和之后的90天相反）。
按客户id和日期聚合。
使用一个运行总和来获得前90天中30+的天数。
然后筛选30天以上的时间，并将其视为一个缺口和孤岛问题。
假设1000天就足够了（包括之前和之后的90天），那么查询如下所示：

with n as (
      select v.n
      from (values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(n)
     ),
     nums as (
      select (n1.n * 100 + n2.n * 10 + n3.n) as n
      from n n1 cross join n n2 cross join n n3
     ),
     running90 as (
      select clientid, dte, sum(in_period) over (partition by clientid order by dte rows between 89 preceding and current row) as running_90
      from (select t.clientid, dateadd(day, n.n - 90, datestart) as dte,
                   max(case when dateadd(day, n.n - 90, datestart) >= datestart and dateadd(day, n.n - 90, datestart) <= t.dateend then 1 else 0 end) as in_period
            from t join
                 nums n
                 on dateadd(day, n.n - 90, datestart) <= dateadd(day, 90, dateend)
            group by t.clientid, dateadd(day, n.n - 90, datestart)
           ) t
     )
select clientid, min(dte), max(dte)
from (select r.*,
             row_number() over (partition by clientid order by dte) as seqnum
      from running90 r
      where running_90 >= 30
     ) r
group by clientid, dateadd(day, - seqnum, dte);

没有递归的cte（尽管有一个可以用于 n )，这不受 maxrecursion 问题。
这是一把小提琴。
结果与你的结果略有不同。这可能是由于定义上的一些细微差别。以上包括作为“占用”日的结束日。在上述查询中，90天是前89天加上当天。倒数第二个查询显示了90天的运行天数，这在我看来是正确的。

赞(0）回复(0）举报 2021-07-26

xmq68pz92#

这是一个有趣的问题。首先，我将列举从每个客户第一次入住开始到最后一次入住结束后90天的天数。然后，您可以将stay表与left联接起来，并使用窗口函数来标记“vip”天数（请注意，这假设给定客户机没有重叠的停留，这与示例数据一致）。
接下来是间隙和孤岛：您可以使用窗口总和将“相邻的”vip天数分组，然后进行聚合。

with cte as (
    select clientID, min(dateStart) dt, dateadd(day, 90, max(dateEnd)) dateMax
    from stays 
    group by clientID
    union all
    select clientID, dateadd(day, 1, dt), dateMax 
    from cte 
    where dt < dateMax
)
select clientID, min(dt) VIPStart, max(dt) VIPEnd
from (
    select t.*, sum(isNotVip) over(partition by clientID order by dt) grp
    from (
        select 
            c.clientID, 
            c.dt,
            case when count(s.clientID) over(
                partition by c.clientID 
                order by c.dt 
                rows between 90 preceding and current row
            ) >= 30
                then 0
                else 1
            end isNotVip
        from cte c
        left join stays s 
            on c.clientID = s.clientID and c.dt between s.dateStart and s.dateEnd
    ) t
) t
where isNotVip = 0
group by clientID, grp
order by clientID, VIPStart
option (maxrecursion 0)

db上的这个演示会处理您的示例数据，产生：

clientID | VIPStart   | VIPEnd    
-------: | :--------- | :---------
       1 | 2020-01-30 | 2020-04-01
       1 | 2020-05-03 | 2020-07-04
       2 | 2020-02-01 | 2020-04-28
       3 | 2020-02-07 | 2020-04-20

您可以将其置于如下视图中：
这个 order by 以及 option(maxrecursion) 创建视图时必须省略子句
每一个在其 from 子句必须以结尾 option(max recursion 0) 演示

赞(0）回复(0）举报 2021-07-26

我来回答

sql server—在sql中计算滚动聚合最有效的方法是什么？

2条答案

相关问题

热门标签

最新问答