sql—仅当前一行中的一个值小于另一个值时才聚合前一行

qzwqbdag  于 2021-07-26  发布在  Java
关注(0)|答案(3)|浏览(372)

我的处境是每台机器都有两次计数, p 以及 r . p 应始终大于或等于 r 但由于技术滞后和聚合周期短,情况并非总是如此,因为 r 经常计数-但不总是-显示前一时期的数据。因为滞后的长度不是恒定的,所以无法确切地知道是哪个周期 r 值属于。因此,我不能简单地移动所有的 r 在时间上统一向后计数,因为这可能会在以前没有的地方产生其他差异。
这种情况无法改变,我必须按原样处理数据。
在下面的示例中,您可以看到 p 在机器上计算短暂的“暂停” 1 在机器上速度明显减慢 2 ,但是 r count继续返回大于的值 p 在“暂停”之前的短暂时间:

-- Dummy data
declare @t table(d date,m int,p int,r int);
insert into @t values(getdate()-9,1,100,10),(getdate()-8,1,90 ,10),(getdate()-7,1,70 ,10),(getdate()-6,1,70 ,10),(getdate()-5,1,80 ,10),(getdate()-4,1,50 ,10),(getdate()-3,1,10 ,10),(getdate()-2,1,0  ,10),(getdate()-1,1,0  ,10),(getdate()+0,1,0  ,10),(getdate()+1,1,0  ,0),(getdate()+2,1,0  ,0),(getdate()+3,1,40 ,0),(getdate()+4,1,50 ,0),(getdate()+5,1,80 ,10),(getdate()-9,2,1100,100),(getdate()-8,2,190 ,100),(getdate()-7,2,170 ,100),(getdate()-6,2,170 ,100),(getdate()-5,2,180 ,100),(getdate()-4,2,150 ,100),(getdate()-3,2,110 ,100),(getdate()-2,2,10  ,100),(getdate()-1,2,10  ,100),(getdate()+0,2,10  ,100),(getdate()+1,2,10  ,0),(getdate()+2,2,10  ,0),(getdate()+3,2,140 ,0),(getdate()+4,2,150 ,0),(getdate()+5,2,180 ,100);
select * from @t order by m,d;

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

我需要能够明智地调整这些 r 在某种程度上倒计时,这样就可以将它们添加到前面的行中,使每一行保持一致 p 大于或等于相应 r 价值观。
在上面的例子中 m = 1 ,输出可能类似于以下任何一种 r 计数;我不在乎调整的蔓延,只在乎这个 p >= r 对于每一行和每一次调整,只能在时间上向后移动:

+------------+---+------+------+------+------+
|     d      | m |  p   |  r1  |  r2  |  r3  |
+------------+---+------+------+------+------+
| 2020-05-27 | 1 |  100 |   10 |   10 |   10 |
| 2020-05-28 | 1 |   90 |   10 |   10 |   10 |
| 2020-05-29 | 1 |   70 |   10 |   15 |   10 |
| 2020-05-30 | 1 |   70 |   20 |   20 |   10 |) Note how the original 30 r counts
| 2020-05-31 | 1 |   80 |   20 |   20 |   10 |} that didn't follow the rule
| 2020-06-01 | 1 |   50 |   20 |   15 |   40 |) have been moved back in time
| 2020-06-02 | 1 |   10 |   10 |   10 |   10 |
| 2020-06-03 | 1 |    0 |    0 |    0 |    0 |
| 2020-06-04 | 1 |    0 |    0 |    0 |    0 |
| 2020-06-05 | 1 |    0 |    0 |    0 |    0 |
| 2020-06-06 | 1 |    0 |    0 |    0 |    0 |
| 2020-06-07 | 1 |    0 |    0 |    0 |    0 |
| 2020-06-08 | 1 |   40 |    0 |    0 |    0 |
| 2020-06-09 | 1 |   50 |    0 |    0 |    0 |
| 2020-06-10 | 1 |   80 |   10 |   10 |   10 |
+------------+---+------+------+------+------+

我试过用窗口函数和 rows between 但我不知道如何识别 r 需要重新分配给以前期间的值,以及确定 p 分配给的值。如果我有任何进展,我会加在下面,但所有的帮助是非常感谢。

尝试1

我所管理的最接近的是下面的代码,它对上面的代码有效,但是当您更改 p = 50 价值低于 40 当我只想向后时,也会在时间上前后调整:

with t as(
select row_number() over (partition by m order by d) as rn
      ,(row_number() over (partition by m order by d)-1) / 5 as gn
      ,*
from @t
where m = 1
)
select *
      ,case when p > r
            then r + (sum(case when p < r then r else 0 end) over (partition by gn) / sum(case when p > r then 1 else 0 end) over (partition by gn))
            else case when p = r
                      then r
                      else 0
                      end
            end as r_adj
from t;

尝试2

这更近了,但仍在时间上向前和向后调整:

with t as(
select row_number() over (partition by m order by d) as rn
      ,(row_number() over (partition by m order by d)-1) / 10 as gn
      ,(row_number() over (partition by m order by d)+4) / 10 as gn2
      ,*
from @t
where m = 1
)
,r1 as(
select *
      ,case when p > r
            then r + (sum(case when p < r then r - p else 0 end) over (partition by gn) / sum(case when p > r then 1. else 0. end) over (partition by gn))
            else case when p = r
                      then r
                      else 0
                      end
            end as r_adj
from t
)
select d
      ,m
      ,p
      ,r
      ,case when p > r_adj
            then r_adj + (sum(case when p < r_adj then r_adj - p else 0 end) over (partition by gn2) / sum(case when p > r_adj then 1. else 0. end) over (partition by gn2))
            else case when p = r_adj
                      then r_adj
                      else r_adj - (r_adj - p)
                      end
            end as r_new
from r1
order by rn
;
zzoitvuj

zzoitvuj1#

一种方法使用 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;

这是一把小提琴。

db2dz4w8

db2dz4w82#

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

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 |
+------------+---+------+-----+-------------+
uqcuzwp8

uqcuzwp83#

通过使用下面的脚本,我们成功地使它工作在一个可以接受的非常小的误差范围内。
它的工作原理是检查前面的行,看是否有多余的 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

相关问题