一种方法使用 apply :

select t.*,
       t2.r as imputed_r
from t outer apply
     (select top (1) t2.*
      from t t2
      where t2.m = t.m and
            t2.d >= t.d and t2.r <= t.p
      order by t2.d desc
     ) t2;

这是一把小提琴。

你也许可以把它整理一下，但希望详细的内容能帮助你理解正在发生的事情。

WITH r1 AS (
-- Find what the previous day's p value was so we can compare it with today's
SELECT
    *
    ,LAG(p) OVER (PARTITION BY m ORDER BY d) AS previous_p
FROM
    @t
)
,r2 AS (
-- Compute the change between p valuesin percentage
SELECT
    *
    ,CASE WHEN r1.previous_p > 0 THEN ((r1.p*1.0-r1.previous_p)/r1.previous_p) ELSE 0 END AS p_delta
FROM
    r1
)
,r3 AS (
-- Use the percentage change to check if a machine was "paused"
SELECT
    *
    ,CASE WHEN r2.p_delta > 0.8 OR (r2.previous_p = 0 AND r2.p > 0) THEN 1 ELSE 0 END AS is_new_session -- Adjust 0.8 to whatever percent change makes sense for a new session
FROM
    r2
)
, r4 AS (
-- Put each row for a machine into its corresponding group
SELECT
    *
    ,SUM(r3.is_new_session) OVER (PARTITION BY r3.m ORDER BY r3.d) AS session_group_id
FROM
    r3
)
, r5 AS (
-- Now we can calculate for each group how total p and r leftover are for each day
SELECT
    *
    ,CASE WHEN r4.p-r4.r > 0 AND r4.r > 0 THEN r4.p-r4.r ELSE 0 END AS remaining_p
    ,CASE WHEN r4.r-r4.p > 0 THEN r4.r-r4.p ELSE 0 END AS remaining_r
FROM
    r4
)
, r6 AS (
-- We need to get the accumulating remaining amounts
SELECT
    *
    ,SUM(r5.remaining_p) OVER (PARTITION BY r5.m, r5.session_group_id ORDER BY r5.d ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS total_remaining_p
    ,SUM(r5.remaining_r) OVER (PARTITION BY r5.m, r5.session_group_id ORDER BY r5.d ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS total_remaining_r
FROM
    r5
)
,r7 AS (
-- We then go backwards and subtract accumulating p out of the accumulated r
SELECT
    *
    ,r6.total_remaining_r - r6.total_remaining_p AS adjusted_total_remaining_r
FROM
    r6
)
,r8 AS (
-- Shift everything back one day so the adjusted remaining r can be added to the correct date
SELECT
    *
    ,LEAD(r7.adjusted_total_remaining_r) OVER (PARTITION BY r7.m, r7.session_group_id ORDER BY r7.d) AS next_adjusted_total_remaining_r
FROM
    r7
)
-- Finally, calculate what the corected r amount is for each day
SELECT
    r8.d
   ,r8.m
   ,r8.p
   ,r8.r
    ,CASE WHEN r8.remaining_p > 0 THEN -- If there is any remaing p left where we can put r amounts
        CASE WHEN r8.total_remaining_p > r8.total_remaining_r THEN -- Also check that we have more accumlated left than r
            CASE WHEN r8.next_adjusted_total_remaining_r > 0 THEN r8.r + r8.next_adjusted_total_remaining_r -- If we still have space, add the adjusted amount of what is left
            ELSE r8.r -- If we have run out of extra r, leave it as r
            END
        ELSE r8.p -- For those days that we had to adjust by the max amount possible, r + adjusted_r will just be p
        END
     ELSE r8.r - r8.remaining_r END AS corrected_r -- Remove the extra r where r was greater than p
FROM
    r8
ORDER BY r8.m,r8.d

输出：

+------------+---+------+-----+-------------+
|     d      | m |  p   |  r  | corrected_r |
+------------+---+------+-----+-------------+
| 2020-05-27 | 1 |  100 |  10 |          10 |
| 2020-05-28 | 1 |   90 |  10 |          10 |
| 2020-05-29 | 1 |   70 |  10 |          10 |
| 2020-05-30 | 1 |   70 |  10 |          10 |
| 2020-05-31 | 1 |   80 |  10 |          10 |
| 2020-06-01 | 1 |   50 |  10 |          40 |
| 2020-06-02 | 1 |   10 |  10 |          10 |
| 2020-06-03 | 1 |    0 |  10 |           0 |
| 2020-06-04 | 1 |    0 |  10 |           0 |
| 2020-06-05 | 1 |    0 |  10 |           0 |
| 2020-06-06 | 1 |    0 |   0 |           0 |
| 2020-06-07 | 1 |    0 |   0 |           0 |
| 2020-06-08 | 1 |   40 |   0 |           0 |
| 2020-06-09 | 1 |   50 |   0 |           0 |
| 2020-06-10 | 1 |   80 |  10 |          10 |
| 2020-05-27 | 2 | 1100 | 100 |         100 |
| 2020-05-28 | 2 |  190 | 100 |         100 |
| 2020-05-29 | 2 |  170 | 100 |         160 |
| 2020-05-30 | 2 |  170 | 100 |         170 |
| 2020-05-31 | 2 |  180 | 100 |         180 |
| 2020-06-01 | 2 |  150 | 100 |         150 |
| 2020-06-02 | 2 |  110 | 100 |         110 |
| 2020-06-03 | 2 |   10 | 100 |          10 |
| 2020-06-04 | 2 |   10 | 100 |          10 |
| 2020-06-05 | 2 |   10 | 100 |          10 |
| 2020-06-06 | 2 |   10 |   0 |           0 |
| 2020-06-07 | 2 |   10 |   0 |           0 |
| 2020-06-08 | 2 |  140 |   0 |           0 |
| 2020-06-09 | 2 |  150 |   0 |           0 |
| 2020-06-10 | 2 |  180 | 100 |         100 |
+------------+---+------+-----+-------------+

通过使用下面的脚本，我们成功地使它工作在一个可以接受的非常小的误差范围内。
它的工作原理是检查前面的行，看是否有多余的 r 如果是，则将其添加到当前行。然后对这个的输出运行相同的过程，有效地将任何多余的内容向后移动两行。这涵盖了现实世界数据中的大多数情况。
最后，它会检查是否有多余的内容，并找到最近一行的时间戳 p 超过某个阈值的值，将多余的值移到。如果没有带的行 p > r 事先（例如，接收到的第一个数据周期会将此问题排除在外），它会在将来获取最近的一个数据周期。

with t as(
select *
      ,case when lead(p - r) over (partition by m order by d) < 0
            then case when p - r < 0
                        then p
                        else r
                        end
                 + abs(lead(p - r,1,0) over (partition by m order by d))
            else case when p - r >= 0
                        then r
                        else r  + (p - r)
                        end
            end
as r1
from @t
)
,b2 as(
select *
      ,case when lead(p - r1) over (partition by m order by d) < 0
            then case when p - r1 < 0
                        then p
                        else r1
                        end
                 + abs(lead(p - r1,1,0) over (partition by m order by d))
            else case when p - r1 >= 0
                        then r1
                        else r1 + (p - r1)
                        end
            end
        as r2
from t
)
,d as(
select *
      ,case when p - r2 < 0
            then case when max(case when p - r2 > 30 then d else '19000101' end) over (partition by m order by d rows between unbounded preceding and 1 preceding) = '19000101'
                        then min(case when p - r2 > 30 then d else '29990101' end) over (partition by m order by d rows between 1 following and unbounded following)
                        else max(case when p - r2 > 30 then d else '19000101' end) over (partition by m order by d rows between unbounded preceding and 1 preceding)
                        end
            else null
            end as drep
from b2
)
,dover as
(
select drep
      ,m
      ,sum(p) as p
      ,sum(r2 - p) as r3
from d
where drep is not null
group by drep
        ,m
)
,f as(
select d.*
    ,dover.r3
    ,case when d.drep is null
        then d.r2 + isnull(dover.r3 /*- dover.p*/,0) -- abs(isnull(dover.p - (dover.r3 - dover.p),0))
        else d.r2 - (d.r2 - d.p)
        end as ra
from d
    left join dover
        on d.m = dover.m
            and d.d = dover.drep
)
select d,m,p,r,ra
from f
order by m,d