如何在mysql中计算反向运行和

lxkprmvk  于 2021-08-13  发布在  Java
关注(0)|答案(1)|浏览(499)

我在这个查询中计算了累计和。现在,我需要计算相同变量的反向累积和

SELECT t1.date, t1.ant, t1.hab,  
(@csum:= @csum + t1.hab) as cumulative_hab
from(
SELECT date,
        ant, 
        sum(num_habit) as hab
        from xxxxxxxxxx
        WHERE date BETWEEN CURDATE() - INTERVAL 5 DAY AND CURDATE()
        group by ant) AS t1
,(select @csum := 0) vars
order by t1.ant

我的table看起来像这样

date       ant   hab    cumulative_hab
24-05-2020  0   382,000   382,000
24-05-2020  1   28,000    410,000
24-05-2020  2   26,000    436,000
24-05-2020  3   11,000    447,000
24-05-2020  4   29,000    476,000
24-05-2020  6   6,000     482,000
24-05-2020  7   12,000    494,000
28-05-2020  8   50,000    544,000
24-05-2020  12  5,000     549,000
24-05-2020  13  6,000     555,000

我想另一列与反向运行总和(反向累计总和),第一个值是计算555-382

date      ant   hab    cumulative_hab   reverse_cum_hab
24-05-2020  0   382,000   382,000             555,000
24-05-2020  1   28,000    410,000             173,000, 
24-05-2020  2   26,000    436,000             145,000
24-05-2020  3   11,000    447,000             119,000
24-05-2020  4   29,000    476,000             108,000
24-05-2020  6   6,000     482,000              79,000
24-05-2020  7   12,000    494,000              73,000
28-05-2020  8   50,000    544,000              61,000
24-05-2020  12  5,000     549,000              11,000
24-05-2020  13  6,000     555,000               6,000
wgx48brx

wgx48brx1#

作为初学者:如果您运行的是mysql 8.0,那么您可以通过窗口函数轻松地完成这项工作:

select 
    date,
    ant,
    sum(num_habit) as hab,
    sum(sum(num_habit)) over(order by date) cumulative_hab,
    sum(sum(num_habit)) over(order by date desc) reverse_cumulative_hab
from mytable
where date between current_date - interval 5 day and current_date
group by date, ant
order by date

在早期版本中,它更为复杂。我建议加入两个问题:

select t.*, r.reverse_cumulative_hab
from (
    select t.*, @csum := @csum + hab cumulative_hab
    from (
        select date, ant, sum(num_habit) as hab
        from mytable
        where date between current_date - interval 5 day and current_date
        group by date, ant
        order by date
    ) t
    cross join (select @csum := 0) x
) t
inner join (
    select t.*, @rcsum := @rcsum + hab reverse_cumulative_hab
    from (
        select date, ant, sum(num_habit) as hab
        from mytable
        where date between current_date - interval 5 day and current_date
        group by date, ant
        order by date desc
    ) t
    cross join (select @rcsum := 0) x
) r on r.date = t.date
order by t.date

这假设没有重复 antdate .
也可以简化逻辑,通过计算累积和与总和之间的差值来计算逆和:

select t.*, z.total_hab - t.cumulative_hab reverse_cumulative_hab
from (
    select t.*, @csum := @csum + hab cumulative_hab
    from (
        select date, ant, sum(num_habit) as hab
        from mytable
        where date between current_date - interval 5 day and current_date
        group by date, ant
        order by date
    ) t
    cross join (select @csum := 0) x
) t
cross join (
    select sum(num_habit) as total_hab
    from mytable
    where date between current_date - interval 5 day and current_date
) z
order by date

注意,在行的排序方面,这些查询比原始代码更安全:在计算累计和之前,记录在子查询中排序。

相关问题