MySQL按日期累计总和顺序 [英] MySQL cumulative sum order by date

问题描述

我正在按日期执行累积总和订单，但没有得到预期的结果.与用于求和的顺序相比，某些记录的显示顺序不同.

I am executing a cumulative sum order by date and I do not get the expected result. Some records are presented in a different order compared to the order used for the sum.

请看 SQL Fiddle

我希望得到以下结果:

2015-05-05T00:00:00Z 50 30 20 90 120

2015-05-05T00:00:00Z 60 30 30 120 100

2015-05-05T00:00:00Z 60 30 30 120 100

2015-05-04T00:00:00Z 70 50 20 30 70

2015-05-04T00:00:00Z 70 50 20 30 70

2015-05-04T00:00:00Z 80 40 40 70 50

2015-05-04T00:00:00Z 80 40 40 70 50

2015-05-03T00:00:00Z 30 20 10 10

2015-05-03T00:00:00Z 30 20 10 10

或以下顺序:

2015-05-05T00:00:00Z 60 30 30 120

2015-05-05T00:00:00Z 50 30 20 90

2015-05-05T00:00:00Z 50 30 20 90

2015-05-05T00:00:00Z 60 30 30 120

2015-05-04T00:00:00Z 80 40 40 70

2015-05-04T00:00:00Z 70 50 20 30

2015-05-04T00:00:00Z 70 50 20 30

2015-05-04T00:00:00Z 80 40 40 70

2015-05-03T00:00:00Z 30 20 10 10

2015-05-03T00:00:00Z 30 20 10 10

(已添加)请注意，负值也是可能的.这就是为什么我在下面的答案中提到累积总和的顺序无法解决问题的原因.作为示例，我将稍微修改结果:

(Added) please note that negative values are also possible. This is the reason why I have mentioned on answers below that an order on the cumulative sum would not solve the problem. As an example I will modify slightly the result:

2015-05-05T00:00:00Z 30 60 -30 60

2015-05-05T00:00:00Z 50 30 20 90

2015-05-05T00:00:00Z 50 30 20 90

2015-05-04T00:00:00Z 80 40 40 70

2015-05-04T00:00:00Z 80 40 40 70

2015-05-04T00:00:00Z 70 50 20 30

2015-05-04T00:00:00Z 70 50 20 30

2015-05-03T00:00:00Z 30 20 10 10

2015-05-03T00:00:00Z 30 20 10 10

感谢您的帮助.

推荐答案

按cum_liability_pts"描述的顺序添加额外字段:

SQL 小提琴

SELECT *
  FROM (
    SELECT date,
           tot_earn_pts,
           tot_redeem_pts,
           tot_earn_pts - tot_redeem_pts AS tot_liability_pts,
           @cum := @cum + tot_earn_pts - tot_redeem_pts AS cum_liability_pts
      FROM (   
        SELECT date, 
               earn_points AS tot_earn_pts, 
               redeem_points AS tot_redeem_pts
          FROM i_report_total_order
       /* WHERE website_id = 36 */

           ) tots
      JOIN (SELECT @cum := 0) init
  ORDER BY date asc 
       ) res_asc
ORDER BY date desc, cum_liability_pts desc;

这篇关于MySQL按日期累计总和顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！